[Update: Version 2.0 uploaded with some bug fixes]

When troubleshooting customer's performance issue that's happening at the moment, we have many tools out of the box. The good old SQL traces that are not needed for the majority of the cases and if it's needed then my first option would ne XE if we are on 2012. Also there are many DMV that we can use and these are the building blocks that SQL Server Management Studio Activity Monitor is built on. However to get the best response and the result sets that you are only interested in you need to use your own script.

I used to write an ad-hoc script every time I'm troubleshooting something based on the situation. There are some great scripts online that covers what I need but I wanted to have my own implementation as simple and function-based so one can use it with filters easily. If you are looking for more comprehensive script, I'd refer to Adam Machanic's whoisactive stored procedure. I don't have much experience using it but he's the first one who used the idea of printing the TSQL code as XML –as far as I know- so will be easier to view in SSMS by clicking on it. I'm using the same idea as well.

 

Eventually I wrote the function dbo.GetCurrentActivity. I'm using it for few months now and if you are a customer whom I promised that it will be uploaded to my blog soon, please accept my apology and find the details below J

 

The function will show only the running/runnable or suspended requests. If we have a session that does nothing then it will not be seen by this function.

The function make it easy for these scenarios

    -Waiting on any waiting type as it shows the waiting summary column

    -Blocking chain as it shows the complete chain so you can get the root blocker

    -It shows the original TSQL when having FETCH API_CURSOR operation

The output of the function is detailed in the table below.

 

session_id SMALLINT

The session id of the request (from sys.dm_exec_sessions)

status NVARCHAR(60)

The request status (from sys.dm_exec_requests)

command NVARCHAR(64)

The command being executed (from sys.dm_exec_requests)

Waiting_summary NVARCHAR(200)

Summary of the waiting type, resource and duration (from sys.dm_os_waiting_tasks)

database_name NVARCHAR(128)

Database name, this column will only show data if running with @lightweight parameter =0 (from dm_exec_plan_attributes)

SQLQuery XML

TSQL query from cross applying dm_exec_sql_text

CursorQuery XML

When SQLQuery column shows FETCH API_CURSOR, this column should have the original statement

wait_duration_ms BIGINT

Wait duration (from sys.dm_os_waiting_tasks)

blocking_session_id SMALLINT

The blocking session if you have a blocking chain, see blockChain column

BlockChain VARCHAR(200)

Shows the blocking chain using the format <sessionID>--><blockedSessionID>. 0 means there's no blocking

elapsed_time_ms BIGINT

Total elapsed time in ms

physical_reads_KB BIGINT

Physical reads converted to KB

logical_writes_KB BIGINT

Logical writes in KB

logical_reads_KB BIGINT

Logical reads in KB

granted_query_memory_KB INT

Granted memory in KB

cpu_time_ms INT

CPU time in ms

host_name NVARCHAR(256)

The host name (the server originating the request)

program_name NVARCHAR(256)

The program name

query_plan XML

Query plan in XML. this column will only show data if running with @lightweight parameter =0 (from dm_exec_plan_attributes)

fn_version VARCHAR(10)

To keep track of the current version. If you faced any issues with the function please reply with the version number you have.

 

To setup the function, execute the script file from the location below. The function will be installed by default on the master database. Then from any other database you can call

SELECT * FROM master.dbo.GetCurrentActivity(1); --with lightweight parameter =1, quick execution

SELECT * FROM master.dbo.GetCurrentActivity(0); --with lightweight parameter =0, slower execution but more details.

 

 

The complete script can be found here

 

This blog post is cross posted on

http://blogs.msdn.com/b/mosharaf/

http://www.msharaf.com/blog

 

Enjoy your performance troubleshooting.

 

~Mohamed