seeing historical blocking and blcoked processes thru DMV

hi,
below query was bringing results 4 hours ago, but now it is not bringing the same records.. can you infer why/what may cause it to fail?
(I am running from master database).

SELECT *
FROM
sys.dm_exec_connections AS Blocking
JOIN
sys.dm_exec_requests AS Blocked
ON
Blocking.session_id = Blocked.blocking_session_id
JOIN sys.dm_os_waiting_tasks
AS Waits  
ON Blocked.session_id = Waits.session_id
RIGHT OUTER JOIN sys.dm_exec_sessions Sess  
ON Blocking.session_id = sess.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
LVL 5
25112Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel_PLDB Expert/ArchitectCommented:
Hello,
Information provided by DMVs is dynamic in nature as well as SQL Server workload - it is live environment (isn't it?). Therefore blocking query results may differ during time. All depends on how locks are being taken, it is, blocking is a kind of wait - some queries are waiting for resources which are locked at the time.
You can't query historical blocking info because of possible dynamic nature of database queries. Instead you should take snapshots in constant intervals and then query that gathered information to be able to see which sessions were blocked by.
I suggest following query to gather blocking stats:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
Waits.wait_duration_ms / 1000 AS WaitInSeconds
, Blocking.session_id as BlockingSessionId
, DB_NAME(Blocked.database_id) AS DatabaseName
, Sess.login_name AS BlockingUser
, Sess.host_name AS BlockingLocation
, BlockingSQL.text AS BlockingSQL
, Blocked.session_id AS BlockedSessionId
, BlockedSess.login_name AS BlockedUser
, BlockedSess.host_name AS BlockedLocation
, BlockedSQL.text AS BlockedSQL
, SUBSTRING (BlockedSQL.text, (BlockedReq.statement_start_offset/2) + 1,
((CASE WHEN BlockedReq.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), BlockedSQL.text)) * 2
ELSE BlockedReq.statement_end_offset
END - BlockedReq.statement_start_offset)/2) + 1)
AS [Blocked Individual Query]
, Waits.wait_type
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked
ON Blocking.session_id = Blocked.blocking_session_id
INNER JOIN sys.dm_exec_sessions Sess
ON Blocking.session_id = sess.session_id
INNER JOIN sys.dm_tran_session_transactions st
ON Blocking.session_id = st.session_id
LEFT OUTER JOIN sys.dm_exec_requests er
ON st.session_id = er.session_id
INNER JOIN sys.dm_os_waiting_tasks AS Waits
ON Blocked.session_id = Waits.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)
AS BlockingSQL
INNER JOIN sys.dm_exec_requests AS BlockedReq
ON Waits.session_id = BlockedReq.session_id
INNER JOIN sys.dm_exec_sessions AS BlockedSess
ON Waits.session_id = BlockedSess.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
--get queries being blocked by 15 seconds, you can adjust it according to your needs
WHERE Waits.wait_duration_ms > 15000
ORDER BY WaitInSeconds

Open in new window


Regards,
Daniel

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Blocking information aren't stored. When you run that query it only says if actually exists any blocking or not.
If you want to have an historical perspective then you'll need to set a schedule to run that query (every minute for example) and store the information in a table so you can query it later.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why an accepted solution receives 100 points and assisted solution 400 points?
Shouldn't be the opposite?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

25112Author Commented:
I do appreciate the comment, Vitor. I don't say I good at distributing it right. I will take help of mods.

thanks to both experts.
25112Author Commented:
thank you again, experts.
thanks to mod also for quick attention!
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's more logical. Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.