May 28, 2015 at 5:15 am
In sql 2012 i want to track the blocking details at a specific time is any query to get the data ?
May 28, 2015 at 5:35 am
ramyours2003 (5/28/2015)
In sql 2012 i want to track the blocking details at a specific time is any query to get the data ?
If the specific time is now, then you can use the DMVs to get at the information. You could also use something like sp_who2 or similar procedure; there are a few of them out there.
If the specific time is in the past, I think you're going to have to look at monitoring solutions that keep track of history.
May 28, 2015 at 5:51 am
can you provide those dmv's other than sp_who?
May 28, 2015 at 6:09 am
sys.dm_exec_requests will show you the immediately running queries that are experiencing blocking. You can combine that with sys.dm_exec_sql_text to get the query and sys.dm_exec_query_plan to get the execution plan.
There is nothing in SQL Server that automatically gathers this information out of the box. You can set something up though. Extended Events offers mechanisms for doing exactly this. Here's an example[/url] (I haven't tested this one personally, but it'll get you started).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 28, 2015 at 6:15 am
Here's the query to pull from the DMVs that Grant mentioned, but this will only get you what's going on right now.
SELECT er.session_id spid, er.blocking_session_id blocking_spid, st.text
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st;
For looking at what was blocking, you'll need to use EE like Grant suggested (albeit I've never done it) or look at a monitoring package that keeps track of history.
Edit: Fixed an oops in the query. 😉
May 28, 2015 at 6:17 am
I agree with Grant I have used Jonathan Kehayias's Extended Events script to get an idea of blocking, I just changed it slightly because I knew the database_ID that I wanted to concentrate on and I let in run for a while.
cheers.
May 28, 2015 at 6:58 am
BL0B_EATER (5/28/2015)
I agree with Grant I have used Jonathan Kehayias's Extended Events script to get an idea of blocking, I just changed it slightly because I knew the database_ID that I wanted to concentrate on and I let in run for a while.cheers.
I did a quick search. If I'd thought about it, I would have gone straight to Jonathan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply