Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jagoodie
Starting Member
4 Posts |
 Posted - 2009-12-17 : 13:22:06
|
| I have a SQL Server 2005 with a 4 GB database on a resonably new server with 4gb of RAM and a RAID 10 disk-subsystem. It is used by about 20 people on an Access front-end (don't get me started). About once a week users complain that their applications are slow or un-responsive. (It used to be more often, but I have used some DMF's to find missing indexes and created them)I have set an alert to tell me whenever the lock wait time goes over 5 seconds, which I consider pretty long, considering the small size of this database. Is their any good way to capture either 1. What was causing the lock in the first place, or 2. What and who is actually being lock waited? Whenever I get these alerts I immediately run a sp_who2 to see if their are any blocks, and their never are. Where to start? |
|
|
jagoodie
Starting Member
4 Posts |
Posted - 2009-12-17 : 13:28:02
|
| and by DMF I actually meant DMV (duh!) |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2009-12-18 : 06:26:35
|
| Can you set the alert to do the "sp_who2" automatically as part of the same process of monitoring the lock wait time.....that way you can minimise the chance of the problem disappearing before you look for it. |
|
|
|
|
|
|
Lock wait time - who is causing it?