1058
Part VIII: Performance Tuning and Optimization
FIGURE 47-3
Activity Monitor displays information about the current locks and any blocking going on. In
this instance, spid 54 is blocked by spid 51, which is blocked by spid 52.Using Profi ler
You can also use SQL Server Profi ler to watch blocked processes using the Error and
Warnings: Blocked Process Report event (see Figure 47-4).The catch to using Profi ler is that by default the server is not confi gured to fi re the
blocked process event. To enable it, you must confi gure the
blocked process threshold setting. In addition, that’s an advanced option, so you
must fi rst enable Show Advanced Options. The following snippet sets the blocking dura-
tion to 1 second.sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'blocked process threshold', 1;
GO
RECONFIGURE;c47.indd 1058c47.indd 1058 7/31/2012 10:23:28 AM7/31/2012 10:23:28 AM
http://www.it-ebooks.info