excessive locks

[timscronin]

have a 3rd party sql 2000 app, mostly bad sql. have lock issues, when monitoring sql locks/req per second, I get normally between 500,000 and 1,000,000 requests. For a 4 way box with 16 gig of memory, what is considered an excessive amounts of locks.

 

[icemel]

An unacceptable level is of locks is when you experience slowdowns in query response times due to locking. In my experience, it can just take a few long-running blocking locks to bring a system to a halt. Depends on the resource being locked.

 

[timscronin]

Yes it does get slow from user's perspective. I was considering running sp-lock and look more closely at objects being locked

 

[icemel]

Yes, you can use sp_lock.

I tend to find graphical tools easier to work with. In EM (2000), you can look at locks under "Current Activity" and see exactly which SPIDs are locking/blocking and the corresponding queries. However, it is real-time only.

For a historical perspective, I use Quest's Performance Analysis, which is a great overall tool for monitoring a database.

Then of course, once you figure out which queries are the culprits, you need to look into how to tune those queries/applications that are creating the locks.

Good luck.

 

[SQLDenis]

run exec sp_who2
and look at the BlkBy column

the output is the same as in Enterprise manager only much faster and it doesn't hang

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[icemel]

Yes, EM can hang badly. But can you see the underlying sql using a built-in sp or other query? I'd be interested to know. Thanks

 

[SQLDenis]

yes like i said...sp_who2

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[icemel]

sp_who2 shows a lot of things, but it does not show sql statement text

 

[SQLDenis]

That statement is capped at 255 characters anyway and if there are multiple only the first is shown

get the spid and run DBCC INPUTBUFFER (SPID) that will give you the text

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

you can also use fn_get_sql() ...... I am waiting for SQLBill to post the code ;-)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/