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 |
|
sureshot
Yak Posting Veteran
72 Posts |
 Posted - 2005-03-09 : 23:17:45
|
| I've got a production DB with hundreds of simultaneous users that every once in a while (once or twice every few months) will have a string of timeouts. I notice blocking if I happen to catch it in the act. It doesn't seem to be the same query blocking all of the time though and I usually catch the tail end of it if I'm around to catch it at all. I was considering running a trace for deadlock and deadlock chain and seeing where that gets me. Is this trace something I could run daily let's say 18 hours/day during traffic hours w/o putting undue load on the server?How should I go about analyzing the results from the trace? I fear that there's simply a lot of traffic and page locks are being escalated to table locks and it's causing problems. I've been solving this in general by trying to optimize all of the queries/SP's involved to make them faster and properly indexed, but what can I really do when I reach the point that all of them are pretty tight and it's still happening? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-03-10 : 10:17:34
|
Rather than keeping a trace running on production, I've done this before which worked nicely. exec this from a job that re-occurs every few seconds:insert blockInfo (blockedCmd, blockedByCmd, blockTime)Select blocked.cmd [blocked] ,blocker.cmd [blockedby] ,getDate()From Master.dbo.sysProcesses blocked with (nolock) JOIN Master.dbo.sysProcesses blocker with (nolock) ON blocked.blocked = blocker.spidWhere blocked.blocked > 0 Be One with the OptimizerTG |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-10 : 10:19:12
|
that is cute, man...Go with the flow & have fun! Else fight the flow  |
|
|
|
sureshot
Yak Posting Veteran
72 Posts |
Posted - 2005-03-10 : 10:36:54
|
| That's an interesting idea. It's only telling me if it's a SELECT, UPDATE, etc though. I'd like to get the real command text from DBCC inputbuffer(). Is that stored in a table that I can join on? I was also thinking of joining on syslockinfo to see what locks are being held. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-03-10 : 10:45:59
|
| The app we were testing only used SPs. If an SP is blocking or being blocked then cmd column has the SP name.I'm sure there's a way to capture the data you're interested in. I'll do a little checkin'.Be One with the OptimizerTG |
|
|
|
sureshot
Yak Posting Veteran
72 Posts |
Posted - 2005-03-10 : 10:56:10
|
| http://vyaskn.tripod.com/fn_get_sql.htmThis looks promising. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-03-10 : 11:29:52
|
| Another thing you may want check is if you are getting a lot of stored procedure recompiles. If that is happening, you can see the results that you describe: people are blocked for a little while, and by the time you check it out, everything is fine.Basically, under certain conditions, stored procedure compiles block activity until they complete.I had this happen in an application I was supporting, and it drove me crazy until I figured it out. We were getting it maybe once per day, and it was a serious problem.The solution was to redo the procedures to make them compile faster, and less often.This article is a good explanation of the situation:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.aspYou may also find this helpful:http://www.sqlservercentral.com/columnists/bkelley/procedurecache.aspCODO ERGO SUM |
|
|
|
sureshot
Yak Posting Veteran
72 Posts |
Posted - 2005-03-10 : 12:31:04
|
| Thanks. I will check that out as well.I found this procedure sp_who_3 that looks ilke it will present all the information I need on deadlocks. I will give this a shot.http://www.databasejournal.com/scripts/article.php/3391341 |
|
|
|
|
|
|
|
|
Detecting Deadlocks