Finding Problem Queries with SQL Profiler - Professional SQL Server 2005 Performance Tuning - page 107

WHERE AddressLine1 = '40 Ellis St.'
AND City = 'Bothell'
AND PostalCode = '98011'
After the threshold value (10 seconds in your sp_configure setting) of a blocking event has been met,
Profiler captures every blocking event. For each blocking event captured in Profiler, it records execution
statements for the blocker. It pinpoints who the blocker is and which process is being blocked. This data
is displayed in XML format as seen in Figure 5-7.
Figure 5-7
Configuring Profiler to capture blocking events makes performance monitoring and tuning tasks much
simpler than ever before. Measuring the frequency of blocking events and number of blocking events per
day will provide you with a quantified measure of performance data.
Capturing Showplan XML Data
One of the new SQL trace events worth mentioning is the Showplan XML event class. In the case of
tuning T-SQL statements or stored procedures, the output of XML data with execution statistics is
very useful.