Lock Monitoring

Hi,
We're trying to investigate a performance issue with our application
which uses SQL Server 2000 as the backend. The performance has become
an issue in a live customer environment and we are trying to determine
what is acquiring and holding locks for a "long" time.
We've been monitoring with SQL Profiler, looking for command completion
that takes more than 2 seconds. This has indicated some scripts, but,
some of the scripts that are reaching the 30 second execution limit are
very simple and execute in (very) sub second time under normal
circumstances.
So, something must be holding a lock that they are waiting for. Nothing
we're seeing in our trace is making this obvious.
What I'm looking to do is find a way to identify stored procedures that
in their execution time are holding locks for "long" periods of time.
Can anyone suggest a way to do this? Perfmon gives me average lock time
in ms and things like that, but not what's causing it and SQL Profiler
doesn't seem to be able to tell me what has the lock and how long it's
held the lock for (and what type of lock it is).
Any help gratefully accepted!
Cheers,
Michael
If you are experiencing deadlocks - Read up on Trace flags 1204, 1205
Use sp_who and sp_lock as well.
"Michael Jervis" <mjervis@.gmail.com> wrote in message
news:1159452500.950233.257270@.k70g2000cwa.googlegr oups.com...
> Hi,
> We're trying to investigate a performance issue with our application
> which uses SQL Server 2000 as the backend. The performance has become
> an issue in a live customer environment and we are trying to determine
> what is acquiring and holding locks for a "long" time.
> We've been monitoring with SQL Profiler, looking for command completion
> that takes more than 2 seconds. This has indicated some scripts, but,
> some of the scripts that are reaching the 30 second execution limit are
> very simple and execute in (very) sub second time under normal
> circumstances.
> So, something must be holding a lock that they are waiting for. Nothing
> we're seeing in our trace is making this obvious.
> What I'm looking to do is find a way to identify stored procedures that
> in their execution time are holding locks for "long" periods of time.
> Can anyone suggest a way to do this? Perfmon gives me average lock time
> in ms and things like that, but not what's causing it and SQL Profiler
> doesn't seem to be able to tell me what has the lock and how long it's
> held the lock for (and what type of lock it is).
> Any help gratefully accepted!
> Cheers,
> Michael
>
|||http://support.microsoft.com/kb/271509/EN-US/ has a lot of good information
on how to monitor blocking and links to other KB articles on locks and
blocking.
Tom
"Michael Jervis" <mjervis@.gmail.com> wrote in message
news:1159452500.950233.257270@.k70g2000cwa.googlegr oups.com...
> Hi,
> We're trying to investigate a performance issue with our application
> which uses SQL Server 2000 as the backend. The performance has become
> an issue in a live customer environment and we are trying to determine
> what is acquiring and holding locks for a "long" time.
> We've been monitoring with SQL Profiler, looking for command completion
> that takes more than 2 seconds. This has indicated some scripts, but,
> some of the scripts that are reaching the 30 second execution limit are
> very simple and execute in (very) sub second time under normal
> circumstances.
> So, something must be holding a lock that they are waiting for. Nothing
> we're seeing in our trace is making this obvious.
> What I'm looking to do is find a way to identify stored procedures that
> in their execution time are holding locks for "long" periods of time.
> Can anyone suggest a way to do this? Perfmon gives me average lock time
> in ms and things like that, but not what's causing it and SQL Profiler
> doesn't seem to be able to tell me what has the lock and how long it's
> held the lock for (and what type of lock it is).
> Any help gratefully accepted!
> Cheers,
> Michael
>

Lock Monitoring

Hi,
We're trying to investigate a performance issue with our application
which uses SQL Server 2000 as the backend. The performance has become
an issue in a live customer environment and we are trying to determine
what is acquiring and holding locks for a "long" time.
We've been monitoring with SQL Profiler, looking for command completion
that takes more than 2 seconds. This has indicated some scripts, but,
some of the scripts that are reaching the 30 second execution limit are
very simple and execute in (very) sub second time under normal
circumstances.
So, something must be holding a lock that they are waiting for. Nothing
we're seeing in our trace is making this obvious.
What I'm looking to do is find a way to identify stored procedures that
in their execution time are holding locks for "long" periods of time.
Can anyone suggest a way to do this? Perfmon gives me average lock time
in ms and things like that, but not what's causing it and SQL Profiler
doesn't seem to be able to tell me what has the lock and how long it's
held the lock for (and what type of lock it is).
Any help gratefully accepted!
Cheers,
MichaelIf you are experiencing deadlocks - Read up on Trace flags 1204, 1205
Use sp_who and sp_lock as well.
"Michael Jervis" <mjervis@.gmail.com> wrote in message
news:1159452500.950233.257270@.k70g2000cwa.googlegroups.com...
> Hi,
> We're trying to investigate a performance issue with our application
> which uses SQL Server 2000 as the backend. The performance has become
> an issue in a live customer environment and we are trying to determine
> what is acquiring and holding locks for a "long" time.
> We've been monitoring with SQL Profiler, looking for command completion
> that takes more than 2 seconds. This has indicated some scripts, but,
> some of the scripts that are reaching the 30 second execution limit are
> very simple and execute in (very) sub second time under normal
> circumstances.
> So, something must be holding a lock that they are waiting for. Nothing
> we're seeing in our trace is making this obvious.
> What I'm looking to do is find a way to identify stored procedures that
> in their execution time are holding locks for "long" periods of time.
> Can anyone suggest a way to do this? Perfmon gives me average lock time
> in ms and things like that, but not what's causing it and SQL Profiler
> doesn't seem to be able to tell me what has the lock and how long it's
> held the lock for (and what type of lock it is).
> Any help gratefully accepted!
> Cheers,
> Michael
>|||http://support.microsoft.com/kb/271509/EN-US/ has a lot of good information
on how to monitor blocking and links to other KB articles on locks and
blocking.
Tom
"Michael Jervis" <mjervis@.gmail.com> wrote in message
news:1159452500.950233.257270@.k70g2000cwa.googlegroups.com...
> Hi,
> We're trying to investigate a performance issue with our application
> which uses SQL Server 2000 as the backend. The performance has become
> an issue in a live customer environment and we are trying to determine
> what is acquiring and holding locks for a "long" time.
> We've been monitoring with SQL Profiler, looking for command completion
> that takes more than 2 seconds. This has indicated some scripts, but,
> some of the scripts that are reaching the 30 second execution limit are
> very simple and execute in (very) sub second time under normal
> circumstances.
> So, something must be holding a lock that they are waiting for. Nothing
> we're seeing in our trace is making this obvious.
> What I'm looking to do is find a way to identify stored procedures that
> in their execution time are holding locks for "long" periods of time.
> Can anyone suggest a way to do this? Perfmon gives me average lock time
> in ms and things like that, but not what's causing it and SQL Profiler
> doesn't seem to be able to tell me what has the lock and how long it's
> held the lock for (and what type of lock it is).
> Any help gratefully accepted!
> Cheers,
> Michael
>

Lock Monitoring

Hi,
We're trying to investigate a performance issue with our application
which uses SQL Server 2000 as the backend. The performance has become
an issue in a live customer environment and we are trying to determine
what is acquiring and holding locks for a "long" time.
We've been monitoring with SQL Profiler, looking for command completion
that takes more than 2 seconds. This has indicated some scripts, but,
some of the scripts that are reaching the 30 second execution limit are
very simple and execute in (very) sub second time under normal
circumstances.
So, something must be holding a lock that they are waiting for. Nothing
we're seeing in our trace is making this obvious.
What I'm looking to do is find a way to identify stored procedures that
in their execution time are holding locks for "long" periods of time.
Can anyone suggest a way to do this? Perfmon gives me average lock time
in ms and things like that, but not what's causing it and SQL Profiler
doesn't seem to be able to tell me what has the lock and how long it's
held the lock for (and what type of lock it is).
Any help gratefully accepted!
Cheers,
MichaelIf you are experiencing deadlocks - Read up on Trace flags 1204, 1205
Use sp_who and sp_lock as well.
"Michael Jervis" <mjervis@.gmail.com> wrote in message
news:1159452500.950233.257270@.k70g2000cwa.googlegroups.com...
> Hi,
> We're trying to investigate a performance issue with our application
> which uses SQL Server 2000 as the backend. The performance has become
> an issue in a live customer environment and we are trying to determine
> what is acquiring and holding locks for a "long" time.
> We've been monitoring with SQL Profiler, looking for command completion
> that takes more than 2 seconds. This has indicated some scripts, but,
> some of the scripts that are reaching the 30 second execution limit are
> very simple and execute in (very) sub second time under normal
> circumstances.
> So, something must be holding a lock that they are waiting for. Nothing
> we're seeing in our trace is making this obvious.
> What I'm looking to do is find a way to identify stored procedures that
> in their execution time are holding locks for "long" periods of time.
> Can anyone suggest a way to do this? Perfmon gives me average lock time
> in ms and things like that, but not what's causing it and SQL Profiler
> doesn't seem to be able to tell me what has the lock and how long it's
> held the lock for (and what type of lock it is).
> Any help gratefully accepted!
> Cheers,
> Michael
>|||http://support.microsoft.com/kb/271509/EN-US/ has a lot of good information
on how to monitor blocking and links to other KB articles on locks and
blocking.
Tom
"Michael Jervis" <mjervis@.gmail.com> wrote in message
news:1159452500.950233.257270@.k70g2000cwa.googlegroups.com...
> Hi,
> We're trying to investigate a performance issue with our application
> which uses SQL Server 2000 as the backend. The performance has become
> an issue in a live customer environment and we are trying to determine
> what is acquiring and holding locks for a "long" time.
> We've been monitoring with SQL Profiler, looking for command completion
> that takes more than 2 seconds. This has indicated some scripts, but,
> some of the scripts that are reaching the 30 second execution limit are
> very simple and execute in (very) sub second time under normal
> circumstances.
> So, something must be holding a lock that they are waiting for. Nothing
> we're seeing in our trace is making this obvious.
> What I'm looking to do is find a way to identify stored procedures that
> in their execution time are holding locks for "long" periods of time.
> Can anyone suggest a way to do this? Perfmon gives me average lock time
> in ms and things like that, but not what's causing it and SQL Profiler
> doesn't seem to be able to tell me what has the lock and how long it's
> held the lock for (and what type of lock it is).
> Any help gratefully accepted!
> Cheers,
> Michael
>

Localization issue

Hi All,

I already have a web site running with SQL Server as a backend (in
english)For future growth, I would like to make it localized. Regarding
the database, I have come up with several approaches.

1) just simply add the column in those table which needs different
language.

2) add additional tables to do it.

3) create a new database to store different language's information

As mentioned, my database have already been implemented, so the minimum
modification is preferred. Could you guys suggest me the best way to do
it?

Another thing, if I alter my existing database into UTF-8 now, will it
affect the original data (ie. English).

Thanks.

Iceiceriver (hfung@.hotmail.com) writes:
> I already have a web site running with SQL Server as a backend (in
> english)For future growth, I would like to make it localized. Regarding
> the database, I have come up with several approaches.
> 1) just simply add the column in those table which needs different
> language.
> 2) add additional tables to do it.
> 3) create a new database to store different language's information
> As mentioned, my database have already been implemented, so the minimum
> modification is preferred. Could you guys suggest me the best way to do
> it?

Adding multi-language support is a task with an impact. Without knowing
the nature of your database it is hard to give recommendations.

In the system I work with, we once faced this problem. At that time,
many our tables had two name columns, for instance countryname and
countrynamefor, holding the Swedish and English name respectively. As
we entered the Finnish market, we needed support for a third language,
since in Finland, both Swedish and Finnish are official languages.

After some discussion, we decided to take the big step: the names were
moved out specific name tables. For instance the countries table
would get a subtable countrynames with the key (countrycode, languageid).
For simplicity we did keep a name column in the main table, so we
can use that as a fall back if there is no name in the current language
in the name table.

Adding an extra column may be easy for the first language you support,
but if you add specific tables, you have the infrastructre built for
your third, fourth language etc.

> Another thing, if I alter my existing database into UTF-8 now, will it
> affect the original data (ie. English).

There is no support for storing data in SQL Server as UTF-8. To store
Unicode data, use UCS-2. (The same as UTF-16, but SQL 2000 does not
support surrogates.) That is the nchar/nvarchar datatypes. Note that
depending on which languages you plan to support, you can still make it with
char/varchar.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Thanks for your reply. I am wondering if there is any sample DB schema
available on the web. I would like to see an example that how several
related-tables also need to be localized. Any suggestion to re-arrange
those existing relationship? I am kinda new to localization problem,
please give me some hints.

"After some discussion, we decided to take the big step: the names were
moved out specific name tables. For instance the countries table
would get a subtable countrynames with the key (countrycode,
languageid).
For simplicity we did keep a name column in the main table, so we
can use that as a fall back if there is no name in the current language
in the name table."

Erland Sommarskog wrote:
> iceriver (hfung@.hotmail.com) writes:
> > I already have a web site running with SQL Server as a backend (in
> > english)For future growth, I would like to make it localized.
Regarding
> > the database, I have come up with several approaches.
> > 1) just simply add the column in those table which needs different
> > language.
> > 2) add additional tables to do it.
> > 3) create a new database to store different language's information
> > As mentioned, my database have already been implemented, so the
minimum
> > modification is preferred. Could you guys suggest me the best way
to do
> > it?
> Adding multi-language support is a task with an impact. Without
knowing
> the nature of your database it is hard to give recommendations.
> In the system I work with, we once faced this problem. At that time,
> many our tables had two name columns, for instance countryname and
> countrynamefor, holding the Swedish and English name respectively. As
> we entered the Finnish market, we needed support for a third
language,
> since in Finland, both Swedish and Finnish are official languages.
> After some discussion, we decided to take the big step: the names
were
> moved out specific name tables. For instance the countries table
> would get a subtable countrynames with the key (countrycode,
languageid).
> For simplicity we did keep a name column in the main table, so we
> can use that as a fall back if there is no name in the current
language
> in the name table.
> Adding an extra column may be easy for the first language you
support,
> but if you add specific tables, you have the infrastructre built for
> your third, fourth language etc.
> > Another thing, if I alter my existing database into UTF-8 now, will
it
> > affect the original data (ie. English).
> There is no support for storing data in SQL Server as UTF-8. To store
> Unicode data, use UCS-2. (The same as UTF-16, but SQL 2000 does not
> support surrogates.) That is the nchar/nvarchar datatypes. Note that
> depending on which languages you plan to support, you can still make
it with
> char/varchar.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||iceriver wrote:
> Erland,
> Thanks for your reply. I am wondering if there is any sample DB
schema
> available on the web. I would like to see an example that how several
> related-tables also need to be localized. Any suggestion to
re-arrange
> those existing relationship? I am kinda new to localization problem,
> please give me some hints.

Why change the database, other than just for the different language?
I'd suggest that keeping table and field names in the original language
would be simplest.
It's the user interface really needs to have local name of fields and
(perhaps) localised error messages.
How you'd do this best depends on what the user interface is
specifically written in.

One method...
Stick these in an xml file distributed with the app or a table in the
database with a structure something like
Language,
Message_No,
Message
Use something in the app to specify the language your user has and look
up the entry in the message field for each text box/error.
Potentially a fair bit of work. The last app I wrote is multi-national
but the company's standard language for computer systems is english for
this reason.

You also have to remember to handle different formatting of dates,
numbers ( comma or full stop as decimal place ).|||iceriver (hfung@.hotmail.com) writes:
> Thanks for your reply. I am wondering if there is any sample DB schema
> available on the web. I would like to see an example that how several
> related-tables also need to be localized. Any suggestion to re-arrange
> those existing relationship? I am kinda new to localization problem,
> please give me some hints.

I have no idea what is out there. But I have a strong feeling that the
answer depends on your business, and you are asking me question about a
database I don't know anything about. What was the right answer for us
may not be for you.

But there is one thing I forgot to mention in my prevoius reply: the
database is the easy part. The hard and expensive work is all the
translation. With a good architecture, you can easily add an other
language from a technical point of view. But the translation work will
be the same each time. And the maintenance of all languages will increase
for each new language you add.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Andy: Thanks, I also heard about the XML stuff may help, but I don't
know too much details in it.

Erland: Thanks, how a architecture and language addition will increase
the maintenance load?

If you guys encounter any good book/web reference regarding the
localization issue, please feel free to let me know. Thanks so much.|||Andy: Thanks, I also heard about the XML stuff may help, but I don't
know too much details in it.

Erland: Thanks, how a architecture and language addition will increase
the maintenance load?

If you guys encounter any good book/web reference regarding the
localization issue, please feel free to let me know. Thanks so much.|||iceriver (hfung@.hotmail.com) writes:
> Erland: Thanks, how a architecture and language addition will increase
> the maintenance load?

You don't localize once. As you change your site, you will have localize
all those changes as well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp