Here are are some tips that I’ve learned from our company’s Database Whisperer in the last year. Every once in a few months, I stumble upon some weird database problem and I’m glad I can call Michael for help. Here are some tips I wanted to write down after tracking down a problem earlier this week.
Set a low blocked process threshold. Here’s how to configure it using SQL Server Management Studio. Run a stored procedure sp_configure and look for a variable named blocked process threshold in its output. If the configured value (in seconds) is too high, run the following commands to set it to 5 seconds:
sp_configure 'blocked process threshold', 5
reconfigure
How to get Blocked process reports using SQL Server Profiler. Open SQL Server Profiler and start a new trace. In the TraceProperties dialog, Events Selectiontab, uncheck all events.
Check the Show all events check-box. In the long menu of events, expand the Errors and Warnings node and check Blocked process report. Check Show all columns and check all possible check-boxes in the Blocked process report row.
How to interpret the blocked process report. The report contains, in an XML-like format, information about the blocked and the blocking process. The inputbuf elements let you see the SQL code your program is running, but sometimes they only identify the executing database object by ID, like this:
Proc [Database Id = 11 Object Id = 1744113354]
Then a simple query (in SQL Server Management Studio)
select object_name(1744113354)
identifies this object as a certain stored procedure. Okay, I probably won’t forget this part.
The blocked-process element contains execution stack, but there is one problem: its frames are presented in a very cryptic way, like this:
<frame line="1" stmtstart="74" sqlhandle="0x02000000ec598609171ca0e0d3bae80cc5a4c0e34169572c"/>
To decode them, the following one-line query is handy:
select * from sys.dm_exec_sql_text(0x02000000ec598609171ca0e0d3bae80cc5a4c0e34169572c)
That’s about it for now. For real database knowledge, read Michael’s blog. Here, I just want to write down a few things that were important to me in the last few days to make sure I don’t forget.
