Lock: Timeout

I enabled SQL Server Profiler Trace to findout performance issue for one of the application. And I am seeing Lock: Timeout eventclass with duration of 0 on tempdb database with objectID 0. Does this mean anything or need to be worried about?

Because I am seeing this many times in 20 mins trace.

Please check whether you or application is generating static cursor. Static cursors create table in tempdb

Lock requests/sec

I have just played a little with the performance monitor on my SQL-server and tried to look at the lock request per second. I really don't know what an acceptable range for this is but the server is running a pretty advanced website with quite alot of transactions. With only a modest 20 users online I have an average of about 5500 locks per second, is this acceptable and what are limits I should look for? The server is a Win2k dual AMD 1,6Mhz 512MB SP4 with RAID1...I don't know if you can look at the number of locks and determine if hardware is adequate. One should look at the database design, queries and stored procedures that access and modify data. Look at the type of locks, are they shared or exclusive? Maybe 95% of the locks are shared and that is due to scanning or large datasets coming back. Maybe one mass update is aquiry exclusive locks and will escalate to a table lock.

You sholud look at the type of locks, locks/session and the queries aquiring the locks.|||I'm not really concerned with the hardware as it seemingly performs well, but I was wondering if this was a really high number of locks and if I should be concerned about it. And how do I issue exclusive locks for my inserts and updates?|||achorozy is absoolutely right, the number of locks is arbitrary, are you having deadlocks, blocking, table locks, etc are more important questions, not to repeat achorozy but these could be shared locks which could be just queries which is perfectly fine.

HTH

Lock Pages in Memory on x64 Standard

All,

I have a new database server running W2K3/MSSQL2K5 x64. I read in BOL that you can use the Lock Pages in Memory option to improve performance on your database server. I did some research on the internet and some sources are stating that you can only use that option with MSSQL2K5 Enterprise but in BOL they state that you can use this option in Standard/Enterprise.

Can anyone confirm that "Lock Pages in Memory on x64 Standard" works in MSSQL2K5 Standard?

Thanks in advance for the help.As far as I understand this, it is available on 32-bit systems with AWE and on 64-bit systems. The three versions supporting this feature is thus Standard, Enterprise and Developer.

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
>

lock blocks under SQLServer.Memory Manager

I'm trying to use the performance monitor on Win2K Adv Server to
troubleshoot performance issues I have on a MSSQL box.
I was trying to find out if there was a lot of blocking on my databases
creating the performance problems.
I starting tracing the counter called "lock Blocks" under "SQLServer.Memory
Manager" and found out that the average value was 4000 with picks at 8000.
I'm not too sure if I understand the exact meaning of this counter. Can
someone explain it to me and tell me if the values I'm getting are bad or
normal?
Thank youHow many rows do you get when you execute this:
select * from master..sysprocesses
where blocked > 0
I am a bit puzzled as to what the figure in lock blocks
actually means. I created a lock block and in perfmon I
got a figure of 1013. I will investigate further what this
figure means.
If you are concerned about blocking locks, then use the
above query and sp_lock to find out what is blocking. You
can also use DBCC INPUTBUFFER (SPID) to find out what was
submitted that caused the block.
Mark Allison
SQL Server MVP
>--Original Message--
>I'm trying to use the performance monitor on Win2K Adv
Server to
>troubleshoot performance issues I have on a MSSQL box.
>I was trying to find out if there was a lot of blocking
on my databases
>creating the performance problems.
>I starting tracing the counter called "lock Blocks"
under "SQLServer.Memory
>Manager" and found out that the average value was 4000
with picks at 8000.
>I'm not too sure if I understand the exact meaning of
this counter. Can
>someone explain it to me and tell me if the values I'm
getting are bad or
>normal?
>Thank you
>
>.
>|||i ran your query and I didn't get any rows back.. I guess blocking is not my
problem then...
thank you Mark
"Mark Allison" <mark@.allisonmitchell.c0m> wrote in message
news:080c01c3b427$576ab100$a001280a@.phx.gbl...
> How many rows do you get when you execute this:
> select * from master..sysprocesses
> where blocked > 0
> I am a bit puzzled as to what the figure in lock blocks
> actually means. I created a lock block and in perfmon I
> got a figure of 1013. I will investigate further what this
> figure means.
> If you are concerned about blocking locks, then use the
> above query and sp_lock to find out what is blocking. You
> can also use DBCC INPUTBUFFER (SPID) to find out what was
> submitted that caused the block.
> Mark Allison
> SQL Server MVP
> >--Original Message--
> >I'm trying to use the performance monitor on Win2K Adv
> Server to
> >troubleshoot performance issues I have on a MSSQL box.
> >I was trying to find out if there was a lot of blocking
> on my databases
> >creating the performance problems.
> >I starting tracing the counter called "lock Blocks"
> under "SQLServer.Memory
> >Manager" and found out that the average value was 4000
> with picks at 8000.
> >I'm not too sure if I understand the exact meaning of
> this counter. Can
> >someone explain it to me and tell me if the values I'm
> getting are bad or
> >normal?
> >
> >Thank you
> >
> >
> >.
> >

local administrator access for DBA's - is this required?

Hi
I need to manage a SQL cluster, monitor database and O.S performance and
apply database patches. Do I require local admin rights for this? If not
what is the workaround please?
Problem is, my organisation is very reluctant to grant local admin rights.
Is there a Microsoft article on this type of issue (I couldn't find one).
Thanks!
MilesHI
Use the SQL server account if you need local admin privilege on server.
Andras Jakus MCDBA
"Miles" wrote:

> Hi
> I need to manage a SQL cluster, monitor database and O.S performance and
> apply database patches. Do I require local admin rights for this? If not
> what is the workaround please?
> Problem is, my organisation is very reluctant to grant local admin rights.
> Is there a Microsoft article on this type of issue (I couldn't find one).
> Thanks!
> Miles
>

local administrator access for DBA's - is this required?

Hi
I need to manage a SQL cluster, monitor database and O.S performance and
apply database patches. Do I require local admin rights for this? If not
what is the workaround please?
Problem is, my organisation is very reluctant to grant local admin rights.
Is there a Microsoft article on this type of issue (I couldn't find one).
Thanks!
MilesHI
Use the SQL server account if you need local admin privilege on server.
Andras Jakus MCDBA
"Miles" wrote:
> Hi
> I need to manage a SQL cluster, monitor database and O.S performance and
> apply database patches. Do I require local admin rights for this? If not
> what is the workaround please?
> Problem is, my organisation is very reluctant to grant local admin rights.
> Is there a Microsoft article on this type of issue (I couldn't find one).
> Thanks!
> Miles
>

local administrator access for DBA's - is this required?

Hi
I need to manage a SQL cluster, monitor database and O.S performance and
apply database patches. Do I require local admin rights for this? If not
what is the workaround please?
Problem is, my organisation is very reluctant to grant local admin rights.
Is there a Microsoft article on this type of issue (I couldn't find one).
Thanks!
Miles
HI
Use the SQL server account if you need local admin privilege on server.
Andras Jakus MCDBA
"Miles" wrote:

> Hi
> I need to manage a SQL cluster, monitor database and O.S performance and
> apply database patches. Do I require local admin rights for this? If not
> what is the workaround please?
> Problem is, my organisation is very reluctant to grant local admin rights.
> Is there a Microsoft article on this type of issue (I couldn't find one).
> Thanks!
> Miles
>