How to troubleshoot intermittent lockups

Hi,

I'm an accidental DBA and I was wondering how to troubleshoot what causes Sql Server to lock up some times. When I say “lock up”, I don’t mean completely. I’m talking about a situation everything’s working fine and all of a sudden we start getting complaints that users can’t get into or use SuchAndSuch application which uses Sql Server. So it's not to the point where I have to use the dedicated administratrive connection...but it is simply not responding to requests!

I check the replication monitor and all of our (push subscription) replication processes are backed up and all of them say “Synchronizing” and they’re not making any progress.

I check the activity monitor and if I trace the “blocked by” column I can usually see that there are several processes blocking other things. But it’s hard to determine the root cause, because once one thing blocks another thing, that causes a chain reaction (especially once the replication processes start kicking in).

For instance, this happened earlier today and there were several processes that said “blocked by” process 13. Yet there was no process 13 in the activity monitor (yes I had it ordered by ProcessID and I checked it twice).

I know how to start a profiler trace, but I’m never sure how to interpret the plethora of results to make any type of intelligent determination from them.

Usually what happens is I sit there browsing through the task manager, replication monitor and activity monitor until it *magically* starts responding on its own just as suddenly as it stopped! Of course, the users ask “how did you fix it”? –To which I have no good answer.

If your database is not responding what is the easiest way to determine the root cause? Trial and error takes too long with an intermittent problem and this solution never satisfies upper management!

Thanks,
Nate

Hi Nate,

You should invest a few hours of your time in this webcasts (the return of investment is great IMHO):

"TechNet Support WebCast:Performance troubleshooting and analysis in Microsoft SQL Server 2000" http://support.microsoft.com/kb/838622/en-us

"TechNet Webcast: Troubleshooting Performance Problems in Microsoft SQL Server 2005" http://msevents.microsoft.com/cui/eventdetail.aspx?eventID=1032275646&Culture=en-US

"TechNet Webcast: SQL Server 2005 Troubleshooting: Supportability Features for SQL 2005"
http://msevents.microsoft.com/cui/eventdetail.aspx?eventID=1032275593&Culture=en-US

"TechNet Webcast: Performance Diagnosis in SQL Server 2005"
http://msevents.microsoft.com/cui/eventdetail.aspx?eventID=1032275586&Culture=en-US

For SQL Server 2005 you should take a look at this whitepaper: "Troubleshooting Performance Problems in SQL Server 2005" http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

|||Thanks Cristian. I will definitely check these out!sql