Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


What’s Running on my SQL Server?
Published: Nov 29, 2020
I’d be shocked if anyone reading this blog post hasn’t heard someone in their company saying the following… “SQL Server is slow… what’s running on it?”…

That question is the bane of all DBAs lives… but it needn’t be if we have a means of telling what’s currently running on our servers…

Now, before I hand over my code I want to point out that there’s a piece of code under the name sp_whoIsActive which is the benchmark for all things “currently running on my SQL Server” and I would recommend that whole heartedly. Simply Google “sp_whoIsActive” or “Adam Machanic” and you’ll find it.

My intention is not to compete, and the reason I’ve included my own version here is because mine’s a lot smaller, simpler, and works when I don’t want the complexity of the sp_whoIsActive code.

It provides a simple overview of what’s running right now, including the SQL statement, the larger SQL text from the batch as a whole, the reads, writes, execution plan, and also the amount of Transaction Log that the session has used to date.

Although I will use sp_whoIsActive a lot of the time, I do find myself using this a lot in order to get a much smaller and faster snapshot of the data, therefore I’m offering it up as an alternative for those who want to run a cut down version or maybe those who want to snapshot their server on a schedule due to this being less resource intensive and less likely to suffer blocking than the more impressive sp_whoIsActive…

select s.session_id spid, s.last_request_start_time, s.status,
              
convert(xml, '<?query -- ' + substring(t.text, r.statement_start_offset / 2,
              (
                    
case when r.statement_start_offset = -1
                                  
then dataLength(t.text)
                                  
else r.statement_end_offset
                    
end - r.statement_start_offset
              
) / 2) + '--?>') sqlStatement,
              
'(' + convert(varchar(100), r.wait_time) + 'ms) ' + r.wait_type waitInfo,
              
r.blocking_session_id, r.cpu_time, r.logical_reads, r.writes,
              
dt.database_transaction_log_bytes_used logUsedBytes,
              
dt.database_transaction_log_bytes_reserved logReservedBytes,
              
s.host_name, s.program_name, s.login_name,
              
db_name(r.database_id) databaseName, convert(xml, '<?query --' + t.text + '--?>') sqlTextFull,
              
q.query_plan queryPlan
from sys.dm_exec_sessions s
join sys.dm_exec_requests r
on s.session_id = r.session_id
left join sys.dm_tran_session_transactions st
on s.session_id = st.session_id
left join sys.dm_tran_database_transactions dt
on st.transaction_id = dt.transaction_id
cross apply sys.dm_exec_sql_text(r.sql_handle) t
cross apply sys.dm_exec_query_plan(r.plan_handle) q
where s.is_user_process = 1
and s.status != 'sleeping'
and s.session_id != @@spid


Hopefully you’ll find this as useful as I do…

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.