We often get a complaint from our client or application team that this particular query sometimes performs worst or at this specific time, the whole system performs very poorly. And, they want you to find the cause of performance issue. To start with basic performance troubleshooting, you need historical data which can tell you what kind of workload the SQL Server was having at that particular time. In other words, what all the things were going on the SQL Server when the customer encountered the issue.

The real challenges come when your company doesn’t have any monitoring tool which captures activities of the SQL Server, or they have a tool, but it gives you information in bits and pieces. In these scenarios, it becomes more complicated for you to fix the issue.

In this posts, you will learn four simple steps to capture precisely useful current workload data of your live SQL Server system which you can use for the troubleshooting purpose.

1. Create a table to store currently running queries
   
2. Capture currently running queries using a couple of DMVs
3. Schedule a job which will run periodically and keep populating step 2 data into the created table
4. Archive historical data

Step-1:

Create a table called “CurrentlyRunningQueries_Snapshot_History” by running below mentioned script under your desired database

USE <YourdatabaseName>
GO
CREATE TABLE CurrentlyRunningQueries_Snapshot_History(
			SPID SMALLINT,
			StartTime DATETIME,
			DBName SYSNAME,
			HostName SYSNAME,
			LoginName NCHAR(256),
			SPIDState NCHAR(60),
			RequestCommand NVARCHAR (max),
			ElapsedTime INT,
			CPUTime INT,
			RequestRead BIGINT,
			RequestWrites BIGINT,
			RequestLogicalRead BIGINT,
			SubQuery NVARCHAR (max),
			ParentQuery NVARCHAR (max),
			QueryPlan XML,
			Current_WaitType NCHAR (64),
			Current_WaitTime BIGINT,
			Last_WaitType NCHAR (64),
			RequestWaitResource NCHAR (512),
			RequestBlockingSPID SMALLINT,
			RequestTimeout INT ,
			OpenTransactionCount INT,
			RowsCount BIGINT,
			TaskCompletedPercent FLOAT,
			Memory_UsagesInKB INT,
			QuerySnapshotRuntime DATETIME

)

Step-2:

Capture the current workload of the SQL Server into the created table by running below mentioned script. The script is one of my all time favorite script which I use most of the time in troubleshooting performance issues on production systems.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

INSERT INTO <YourdatabaseName>.<dbo>.CurrentlyRunningQueries_Snapshot_History

SELECT
      dmes.session_id AS SPID,
	  dmer.start_time,
      UPPER (DB_NAME(dmer.database_id)) DBName,
      dmes.host_name AS HostName,
      UPPER(dmes.login_name) AS LoginName,
      UPPER(dmer.status) AS SPIDState,
      dmer.command as RequestCommand,
      dmer.total_elapsed_time AS ElapsedTime,
      dmer.cpu_time AS CPUTime,
      dmer.reads AS RequestRead,
      dmer.writes AS RequestWrites,
      dmer.logical_reads AS RequestLogicalRead,
      SUBSTRING (dmest.text, (dmer.statement_start_offset/2) + 1, 
	  ((CASE WHEN dmer.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), dmest.text)) * 2
      ELSE dmer.statement_end_offset  END - dmer.statement_start_offset)/2) + 1) AS SubQuery,
      UPPER(REPLACE(LTRIM(LTRIM(CONVERT(varchar(2000),REPLACE(REPLACE(REPLACE(dmest.text,'-',''),'=',''),'*','')))),CHAR(9),'')) AS ParentQuery,
      dmeqp.query_plan AS QueryPlan,
      dmer.wait_type AS Current_WaitType,
      dmer.wait_time AS Current_WaitTime,
      dmer.last_wait_type AS Last_WaitType,
      dmer.wait_resource AS RequestWaitResource,
      dmer.blocking_session_id AS RequestBlockingSPID,
      dmer.lock_timeout AS RequestTimeout,
      dmer.open_transaction_count OpenTransactionCount,
      dmer.row_count RowsCount,
	  dmer.percent_complete TaskCompletedPercent,
	  (dmes.memory_usage*8) AS Memory_UsagesInKB,
	  GETDATE()
FROM
      sys.dm_exec_sessions dmes
	  INNER JOIN  sys.dm_exec_requests dmer ON dmer.session_id = dmes.session_id
      CROSS APPLY sys.dm_exec_sql_text(dmer.sql_handle) dmest
      CROSS APPLY sys.dm_exec_query_plan(dmer.plan_handle) dmeqp
      LEFT JOIN   sys.dm_exec_cached_plans dmecp ON dmecp.plan_handle = dmer.plan_handle
      LEFT JOIN   sys.dm_db_task_space_usage dmtsu ON dmtsu.session_id = dmer.session_id and dmtsu.request_id = dmer.request_id
      LEFT JOIN   sys.dm_db_session_space_usage dmssu ON dmssu.session_id = dmes.session_id
WHERE dmes.session_id <> @@SPID
ORDER BY 11 DESC

If you run the above query (without insert step), you will get output like below snapshot. Kindly click on the image to zoom it.

Below are the columns which you will be getting as the query result. I have mentioned the description of the columns so that you can have a better understanding of them.

SPID  – SQL Server process ID or session ID
StartTime – Timestamp when the SPID started
DBName – The SPID is executing against which database ID
HostName – Name of the workstation or server from there the SPID was initiated
LoginName – Which Login started the SPID
SPIDStatus – Status of the SPID – Running, Runnable, Sleeping…, etc
RequestCommand – What type of command is being processed. i.e – Select, Update..etc
ElapsedTime – Total time elapsed in milliseconds since the SPID started
CPUTime – CPU time in milliseconds that is used by the SPID
RequestRead – Number of reads performed by the SPID
RequestWrites – Number of writes performed by the SPID
RequestLogicalRead – Number of logical reads that have been performed by the SPID.
SubQuery – Which particular statement is running your parent query
ParentQuery – It gives complete query script
QueryPlan – Execution plan of the query
Current_WaitType – The SPID is waiting for what resource.
Current_WaitTime – This column returns the duration in milliseconds, of the current wait
Last_WaitType – This column returns the type of the last wait
RequestWaitResource – If the SPID is currently blocked, this column gives the resource for which the SPID is waiting.
RequestBlockingSPID – ID of the session that is blocking the SPID
RequestTimeout – Lock time-out period in milliseconds for this request
OpenTransactionCount – Number of transactions that are open for this request
RowsCount – Number of rows that have been returned to the client by this request
TaskCompletedPercent – Percentage of work completed by following commands backup, restore, rollback…,etc
Memory_UsagesInKB – Number of 8-KB pages of memory used by this session.
QuerySnapshotRuntime – What time the workload was captured

After going through all the columns, you can relate that we are capturing entirely useful information in one shot which includes query execution plan, wait type, type of commands, login details, the SPID is coming from which server or workstation, blocking details, and many more…

In the second part of this posts, you will be learning about how to schedule a job to capture periodic workload data and archive the historical workload data based on your requirement.

Hope, you find “Capturing Current Workload of SQL Server – part1” as useful posts!