Thank you, and Who is on my schedulers?
In the last two days I have had over 2000 page views on the two posts I have put up. I know I am not that good of blogger yet and the posts are not riveting, so I can only assume that it is the work of Joe Sack and Grant Fritchey who have blasted my blog out to the twitter–verse. All I can say is thank you and I will try not to disappoint!
Taking on the job as the SQL Server Cat Lab Manager I can say that there is never a dull moment around here, and I will do my best to pass along as much info as I am able to. My primary blogging focus will always be the things that drive me nuts and the solutions, as I am always a DBA at heart. Hopefully every now and then I can make your life a little easier!
Now to the good stuff! I was torn on what to post today as I have been working on a SQL 2012 cluster that has been giving me and my Windows guy a fit. 100+ Luns on a DL980, and I probably have had 10 rebuilds and an accidental shared cluster drive format, naturally the SQL system databases were there.
Yeah, that never happens in production… Oh wait, yes it does, in my days as a SQL Engineer I worked a few of those cases, gee Mr. Microsoft Engineer I don’t know where my databases went… Grant Fitchey has some recent posts on backups and what are they good for? I shall contemplate that question and get back to you.
So, instead I am going to pass along a t-sql script that I use in all of my labs, and give to customers all the time for figuring out what is going on in SQL at the moment. This particular script focuses on, and only on what is occupying the schedulers at this moment. Not the runnable stuff, not the sleeping, not the suspended, not the waiting stuff only what is on an actual scheduler. Truth be told it is not magical, it is a variation of a hundred others that are out there, but it is one of my favorites.
I like keeping these handy; every DBA has a Kung-fu folder that they use for Monday mornings and application roll outs where nothing has ever changed, but nothing seems to work anymore either, I still have not figured that math out yet.
The script offers the Batch and the Query Text, and the Execution Plan, but only on the sessions currently taking a ride on the scheduler. You will know when to use it, if you do not, give it a try anyway and see what you learn, since it is focusing only on sessions that are on an active scheduler it is reasonably light weight. I have run this on many a server that was acting naughty.
I know this works in SQL 2008/2008 R2/2012. All of the named columns exist in all three versions, however you will note in SQL 2012 we added a few columns, hence me leaving the * in the query at the end even though i know it is redundant. Customize this to your hearts content and then use at your own risk.
select r.session_id,
s.login_name,
r.wait_time,
r.last_wait_type,
r.wait_resource,
r.wait_type,
r.status,
r.blocking_session_id,
r.scheduler_id,
(SELECTSUBSTRING(text,statement_start_offset/2,
(CASEWHENstatement_end_offset=-1 THENLEN(CONVERT(nvarchar(max),text))* 2
ELSEstatement_end_offsetEND-statement_start_offset)/2)
FROMsys.dm_exec_sql_text(sql_handle))ASquery_text,
(SELECTtextFROMsys.dm_exec_sql_text(sql_handle))ASquery_batch,
qp.query_plan,
s.nt_user_name,
s.host_name,
s.program_name,
r.start_time,
r.command,
*
from
sys.dm_os_schedulersscjoinsys.dm_os_taskst
on (sc.active_worker_address=t.worker_address)join
sys.dm_exec_requestsron (r.session_id=t.session_id)
joinsys.dm_exec_sessionsson (r.session_id=s.session_id)
cross applysys.dm_exec_query_plan(r.plan_handle)qp
where r.session_id> 50
Until Next time!
Shep
Further Reading/Homework;
sys.dm_os_schedulers (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms177526.aspx
sys.dm_os_tasks (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms174963.aspx
sys.dm_exec_requests (Transact-SQL)
https://technet.microsoft.com/en-us/library/ms177648.aspx
sys.dm_exec_sessions (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms176013.aspx
sys.dm_exec_query_plan (Transact-SQL)