April 5, 2012 at 7:58 am
I believe that in SQL Server 2005 there was a blocked process report that you could use to identifying blocking.
How can I identify this in SQL Server 2008 R2?
Is there a better approach?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 5, 2012 at 8:00 am
for reporting, Right Click on the database you want to look at. Choose Reports --> Standard Reports --> All Blocking Transactions.
exec sp_who2 will also show Blocks in SSMS.
April 5, 2012 at 8:05 am
Geoff A (4/5/2012)
for reporting, Right Click on the database you want to look at. Choose Reports --> Standard Reports --> All Blocking Transactions.exec sp_who2 will also show Blocks in SSMS.
Thanks the the information.
I identified blocking using sp_who2 before the post but I want to be able to capture it real time.
The all Blocking Transactions only identifies Blocking that is occurring at the time you run the report.
I want to trace and filter events associated with blocking with the relevant information that I need.
Thanks again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 5, 2012 at 8:08 am
what is your threshold for blocking processes?
ours is 30 seconds, so we have a job which runs the t-sql below, into a #temp, waits 30 seconds, runs it again into a second #temp then compares the two and anything which matches gets logged and emailed. might be a starting point for you
SELECT
D.text SQLStatement,
A.Session_ID SPID, ISNULL(B.status,A.status) Status,
A.login_name Login,
A.host_name HostName,
C.BlkBy,
DB_NAME(B.Database_ID) DBName,
B.command,
ISNULL(B.cpu_time, A.cpu_time) CPUTime,
ISNULL((B.reads + B.writes),(A.reads + A.writes)) DiskIO,
A.last_request_start_time LastBatch,
A.program_name
FROMsys.dm_exec_sessions A
LEFT JOIN
sys.dm_exec_requests B ON A.session_id = B.session_id
LEFT JOIN
(SELECT
A.request_session_id SPID,
B.blocking_session_id BlkBy
FROM sys.dm_tran_locks as A
INNER JOIN sys.dm_os_waiting_tasks as B ON A.lock_owner_address = B.resource_address) C ON A.Session_ID = C.SPID OUTER APPLY sys.dm_exec_sql_text(sql_handle) D
April 5, 2012 at 8:12 am
well, if you want to use Profiler to capture blocks, you have to first turn it on via sp_configure.
SP_CONFIGURE'blocked process threshold',10 ;
GO
RECONFIGURE
;
GO
this sets the process to run ever 10 seconds.
run your trace with the Blocked Process Report Event selected. Then turn off the blocked process threshold by reseting you collection value to 0.
SP_CONFIGURE'blocked process threshold',0 ;
GO
RECONFIGURE
;
GO
April 5, 2012 at 9:13 am
I do not see a Blocked Process Report Event in SQL Server 2008 R2?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 5, 2012 at 9:16 am
Look under Errors and Warnings.....
April 5, 2012 at 9:36 am
Geoff A (4/5/2012)
well, if you want to use Profiler to capture blocks, you have to first turn it on via sp_configure.
SP_CONFIGURE'blocked process threshold',10 ;GO
RECONFIGURE
;
GO
this sets the process to run ever 10 seconds.
run your trace with the Blocked Process Report Event selected. Then turn off the blocked process threshold by reseting you collection value to 0.
SP_CONFIGURE'blocked process threshold',0 ;GO
RECONFIGURE
;
GO
What is the default?
10 is a little low, isn't it?
How do I find out what it is currently set at?
If it is at 0 and I change it, that could cause a lot of issues. eyh?:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 5, 2012 at 10:28 am
What is the default? - Zero
10 is a little low, isn't it? - depends on how many blocks you want to see. if a block last longer than 10 seconds, you will see it multiple time. So if the block last 42 seconds, you will see it 4 times.
How do I find out what it is currently set at? - exec sp_configure
If it is at 0 and I change it, that could cause a lot of issues. eyh?:-) - yes, this type of profiling is resource intensive. which is why you turn it on, use it and turn it back off.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply