"Who who, Who who...
... I really want to know ..?"
OK, we all have our servers, and that they always work absolutely magnificently, we never have to look at whats going on under the bonnet-hood and all is well.
OK, so i've clearly just woken up in a Soho doorway, and a policeman knew my name...
So we have our expensive monitoring tools telling us what is running so we can look back in time too. Great, but that that's not always what we want, we want a super fast way of checking now. Microsoft gave us sp_who (and sp_who2), and this is still tied into keyboard shortcuts by default in SSMS, slap CTRL+1 in a query window and see for yourself.
So its there, and works, you can use it to get a fixed snapshot view of what's going on. Great prior to SQL 2005 (omg 18 years ago, i'm so old) but things have moved on somewhat since. We have awesome community code, out there, such as sp_whoisactive by the Master Machanic (Releases · amachanic/sp_whoisactive (github.com)) , or the Blitz tools from the Overlord Ozar (First Aid - Free Scripts, Downloads, Videos (brentozar.com)). Both of these are just amazing tools from brilliant folks, enhanced by the community, and I thoroughly encourage people to check them out and bind their use to hotkeys in SSMS, it's what I do.
"But I have Activity Monitor!"
Good for you, but its grouping and omission of Wait types really annoys me personally, and it does loads more behind the scenes that I don't want.
But that really isn't the purpose of this post. You're running something, but do you know what its executing to retrieve the information, where its getting it from and how it ties together? Learning this opens up the doors to you knowing how to customise your scripts to your exact requirements.
Time to go all proverby....
"If you give a person a fish, you feed them for a day.If you teach a person to fish, you feed them for a lifetime.”
Oops, I said this blog was nothing to do with fishing too. My bad.
Dynamic Management Objects
Or DMOs, made up from Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs). I think these may be my all time favourite functionality improvement across my years of SQL. Oh, and Query Store.... ah, and Availability Groups... And... OK, i'll stop there.
Lets focus on the basic ones that you may want to look at, and the join columns to see the snapshot of what is going on right now. Or now.... Or even...now. But not just then, you missed just then, and its gone.
Lets start with the first DMV:
sys.dm_exec_requests
This contains rows on all currently executing requests in SQL, its pretty much the main one we use as our starting point, as we want to see what's going on right now. It has many columns, but we'll focus on things like:
Session_id - Or SPID for us old folks. (Joins to most other DMVs) This is what we use as our joining column for many of the other DMVs that we'll discuss.
Start_time - When the request was recieved by SQL.
Status - What is SQL doing with it currently - Running/Runnable/Rollback etc.
Command - What sort of TSQL is it? Select/Insert/Delete/Backup etc.
database_id - Which DB the request is running against?
Next some columns on how much resource is being used. Fat queries can be discovered from here.
cpu_time
total_elapsed_time
reads
writes
logical_reads
granted_query_memory (in pages) - we'll join to a different DMV though for more detailed info.
Now some columns around data we want if things aren't running perfectly well.
Blocking_Session_id - The session_id of anything thats blocking this request. Looking for blocking? You need this column.
wait_type - What the request is waiting on if blocked
wait_time - ...and for how long? (in ms)
wait_resource - ...and on what?
last_wait_type - What the request was previously waiting on.
Next some columns that let us have a look at the code thats in that request.
sql_handle (APPLY (join) to sys.dm_exec_sql_text below) - Unique value for the query text of the batch/proc being executed
plan_handle - (APPLY (join) to sys.dm_exec_sql_plan below) - Unique value for the query plan of the batch/proc being executed.
statement_start_offset - the start position of the code currently running from the batch/proc
statement_end_offset - the end position of the code currently running from the batch/proc
These last 2 columns require a little more manipulation via SUBSTRING on the text column to break down the text. (the division by 2 is because the strings are stored as unicode, 2 bytes for 1 character )
SUBSTRING(text, statement_start_offset / 2,
( CASE WHEN statement_end_offset = -1 THEN DATALENGTH(text)
ELSE statement_end_offset
END - statement_start_offset ) / 2)
AS statement_executing,So we now have the unique handle of what is running, and the start/end positions of the specific code within that handle, and a link to get the plan. Excellent.
Some additional information that you might be interested in:
transaction_isolation_level - How restricted/open is the request on the locks in use?
dop - How parallel is the request?
open_transaction_count - how many transactions are open?
query_hash - This groups similar queries by a hash values. So if you desire, you can aggregate those pesky queries that are bleeding your server dry from "death from 1000 cuts". You'll soon be chasing down root cause problem queries faster than Keith Moon at a free bar.
And that's just the first DMV, you don't get anything like that with sp_who, or it's difficult second album, sp_who2.
Lets build our information out more, to give us context, or just the ammunition for the blame-thrower for seeing who's breaking our server in the first place. Troubleshooting, now that's The Real Me.
sys.dm_exec_query_memory_grants
Holds rows containing information relating to memory usage and allocations for query execution.
Join to this via session_id to sys.dm_exec_requests.
Many of the following columns are self explainitory.
We can get detailed memory information around what the requests are doing. If you're having trouble with RESOURCE_SEMAPHORE waits, then these columns will be of interest. The below relate to query execution memory, too much requested at any one point, and you may get system wide trouble.
granted_memory_kb
ideal_memory_kb
requested_memory_kb
max_used_memory_kb
used_memory_kb
scheduler_id
query_cost
sys.dm_db_task_space_usage
Join to this via session_id to sys.dm_exec_requests.
Contains a row for tempdb allocation allocation and deallocation. Use this information to see which session(s) are killing tempdb.
All values are in pages, so just multiply by 8 to get the value in KB.
user_objects_alloc_page_count - Temp tables, table variables etc.
user_objects_dealloc_page_count
internal_objects_alloc_page_count - Work tables, spools, sort spills etc.
internal_objects_dealloc_page_count
sys.dm_exec_sessions
Join to this via session_id to sys.dm_exec_requests.
Returns one row per authenticated session on SQL Server. Start polishing our request data, with meaningful information.
Useful columns for targetting the blamethrower include:
login_name - Who?
host_name - From where?
program_name - Using what?
row_count
Remember, this is the session scope, which could have contributed many requests since the connection to SQL was made so we also have some collective metrics too:
reads
writes
logical_reads
sys.dm_exec_sql_text
APPLY to this via sql_handle to sys.dm_exec_requests.
This is the DMF that we use to get the actual text from the sql_handle. As its a DMF, we pass the sql_handle (or plan handle) in as a parameter. The table returned includes:
dbid - The database id (Thanks MS for the consistency there!)- can join to database_id in other views if you require.
objectid - the object_id if its a procedure being executed. - can join to object_id in other views.
text - The text of what is running in lovely human readable format.
sys.dm_exec_query_plan
APPLY to this via plan_handle to sys.dm_exec_requests.
As above, this is the DMF that we use to get the estimated query plan from the plan_handle. Note: SQL 2019 upwards has another DMV (sys.dm_exec_query_plan_stats) that contains additional runtime metrics within the plan.
Again, its a DMF, so we pass the plan_handle in as a parameter. The table returned is similar to query_text, but instead of text, we have:
query_plan- the query plan in XML format, ready for visualisation. Present this in SSMS and its clickable directly to the graphical representation.
That's got the base DMVs covered for a current snapshot. Remember, this isn't a replacement for sp_whoisactive etc, but hopefully an insight into the DMVs in question, and how you can join them together to create custom monitoring scripts that are tailored for your environment and use case.
You may not want/need all columns, or maybe you'll want more, or different DMVs? The choice is yours.
Its a benefit that custom scripts will have over inbuilt monitoring procs, downloading a prewritten procedure or even using Activity Monitor *spit*.
AND you learn far more about how things tie together internally...
What's the point of this post? There are much better options than sp_who, sp_who2, dbcc inputbuffer and activity monitor, you know, ye olde tools back from My Generation. Please, stop using old commands you'll so so much better without them. Use the incredible free tools our there, but try to gain an understanding of where the information comes from and as previously mentioned and most importantly, know what you are executing, you'll soon be on Bassmasters.
Thanks
Rodrophenia
Here's an example of some of this thrown together.
--Currently running requests
select req.session_id, req.status, req.blocking_session_id,
req.reads, req.writes, req.logical_reads, req.total_elapsed_time, req.cpu_time,
req.wait_type, req.wait_time, req.granted_query_memory,
SUBSTRING(text, req.statement_start_offset / 2,
( CASE WHEN req.statement_end_offset = -1
THEN DATALENGTH(text)
ELSE req.statement_end_offset
END - req.statement_start_offset ) / 2)
AS statement_executing,
datediff(SECOND,last_request_start_time,getdate()) as Statement_Duration_s,
datediff(SECOND,start_time,getdate()) as Batch_Duration_s,
tsu.user_objects_alloc_page_count*8 TempdbUserAllocKB,
tsu.user_objects_dealloc_page_count*8 TempdbUserDEAllocKB,
tsu.internal_objects_alloc_page_count*8 TempdbInternalAllocKB,
tsu.internal_objects_dealloc_page_count*8 TempdbInternalAllocKB,
ses.host_name, ses.program_name, ses.login_name,
db_name(req.database_id) AS 'Database_Name',
text as FullBatch,
req.statement_start_offset,
req. statement_end_offset,
qmg.granted_memory_kb, qmg.ideal_memory_kb, qmg.requested_memory_kb, qmg.max_used_memory_kb,qmg.used_memory_kb, qmg.dop, qmg.scheduler_id, qmg.query_cost,
ses.row_count,
last_wait_type,
CASE req.transaction_isolation_level
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
ELSE 'Unspecified' END AS transaction_isolation_level,
req.scheduler_id, start_time, command,
qp.query_plan
from sys.dm_exec_requests req
inner join sys.dm_exec_sessions ses on req.session_id = ses.session_id
left join sys.dm_exec_query_memory_grants qmg on qmg.session_id=req.session_id
inner join sys.dm_db_task_space_usage tsu on req.session_id=tsu.session_id
OUTER APPLY sys.dm_exec_sql_text(req.sql_handle)
OUTER APPLY sys.dm_exec_query_plan(req.plan_handle) AS qp
where ses.is_user_process = 1 and req.session_id != @@SPID
order by Text