Atlanta .NET Regular Guys

Perf tune your SQL Server

While I was at the SQL Server User Group last night, I brought up the recent problem my company has been having with deadlocks - where we needed to find who was ACTUALLY causing the locks (not the parent SPID, but the low level trigger or nested SQL SPROC).

• I was told to periodically check the sysprocesses table (SP_WHO2) to see what was running.
• I was also referred to this 3rd party tool - SP_LOCK2 (http://www.databasejournal.com/features/mssql/article.php/1442241).
• Finally, I was informed about the SQLH2 (SQL Help and History tool - http://www.microsoft.com/downloads/details.aspx?FamilyID=eedd10d6-75f7-4763-86de-d2347b8b5f89&DisplayLang=en) which we can use to automate the collection of perfmon counter data.

Basically, I was standing there with a room full of 70 DBAs offering suggestions on how they would have found the solution to the problem faster than I could have.  It's really great to know that I have such a fantastic resource at my disposal and I'm sure I learned more in the 3 minutes standing there than I could have searching the web and knowledge bases for hours.

-- Matt Ranlett