Identifying CPU pressure in the context of SQL Server

Signal vs Resource Waits

SELECT
SUM(wait_time_ms – signal_wait_time_ms) as [ResourceWaitTotal]
, CAST(100.0 * sum(wait_time_ms – signal_wait_time_ms)
/ SUM(wait_time_ms) as numeric(20, 2)) AS [ResourceWait%]
, SUM(signal_wait_time_ms) AS [SignalWaitTotal]
, CAST (100.0 * sum(signal_wait_time_ms)
/ SUM (wait_time_ms) AS numeric(20, 2)) AS [SignalWait%]
FROM sys.dm_os_wait_stats
WHERE
wait_type not in (
‘CLR_SEMAPHORE’
, ‘LAZYWRITER_SLEEP’
, ‘RESOURCE_QUEUE’
, ‘SLEEP_TASK’
, ‘SLEEP_SYSTEMTASK’
, ‘WAITFOR’
)

Top 50 queries by CPU consumption


SELECT TOP 50
queryStats.total_worker_time/queryStats.execution_count AS [Avg CPU Time]
, queryStats.execution_count
, SUBSTRING(queryText.text,queryStats.statement_start_offset/2,
(CASE WHEN queryStats.statement_end_offset = -1
THEN len(convert(nvarchar(max), queryText.text)) * 2
ELSE queryStats.statement_end_offset end –
queryStats.statement_start_offset) / 2)
AS query_text
, dbname=db_name(queryText.dbid)
FROM sys.dm_exec_query_stats queryStats
CROSS APPLY sys.dm_exec_sql_text(queryStats.sql_handle) AS queryText
ORDER BY
[Avg CPU Time] DESC

Advertisements

Troubleshooting transaction log full issues

Below SQL code helps in identifying orphaned or long-running transactions.

SELECT s.session_id ,
s.status ,
s.host_name ,
s.program_name ,
s.login_name ,
s.login_time ,
s.last_request_start_time ,
s.last_request_end_time ,
t.text
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE s.session_id = spid

SELECT st.session_id ,
st.is_user_transaction ,
dt.database_transaction_begin_time ,
dt.database_transaction_log_record_count ,
dt.database_transaction_log_bytes_used
FROM sys.dm_tran_session_transactions st
JOIN sys.dm_tran_database_transactions dt
ON st.transaction_id = dt.transaction_id
AND dt.database_id = DB_ID(‘master’)
WHERE st.session_id = spid

How to identify I/O latency issues

Below SQL code helps in identifying the I/O latency issues in a SQL Server system on a per-file basis.

SELECT
–virtual file latency
ReadLatency = CASE WHEN num_of_reads = 0
THEN 0 ELSE (io_stall_read_ms / num_of_reads) END,
WriteLatency = CASE WHEN num_of_writes = 0
THEN 0 ELSE (io_stall_write_ms / num_of_writes) END,
Latency = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE (io_stall / (num_of_reads + num_of_writes)) END,
–avg bytes per IOP
AvgBPerRead = CASE WHEN num_of_reads = 0
THEN 0 ELSE (num_of_bytes_read / num_of_reads) END,
AvgBPerWrite = CASE WHEN io_stall_write_ms = 0
THEN 0 ELSE (num_of_bytes_written / num_of_writes) END,
AvgBPerTransfer = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE ((num_of_bytes_read + num_of_bytes_written) /
(num_of_reads + num_of_writes)) END,
LEFT (mf.physical_name, 2) AS Drive,
DB_NAME (vfs.database_id) AS DB,
–vfs.*,
mf.physical_name
FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs
JOIN sys.master_files AS mf
ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id
–WHERE vfs.file_id = 2 — log files
— ORDER BY Latency DESC
— ORDER BY ReadLatency DESC
ORDER BY WriteLatency DESC;
GO

From http://sqlskills.com/BLOGS/PAUL/post/How-to-examine-IO-subsystem-latencies-from-within-SQL-Server.aspx

Where is SQL Server spending most of it’s time waiting (Wait stats)

Below SQL code helps in identifying the bottlenecks in a SQL Server system based on wait types.

WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] – [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N’CLR_SEMAPHORE’, N’LAZYWRITER_SLEEP’,
N’RESOURCE_QUEUE’, N’SQLTRACE_BUFFER_FLUSH’,
N’SLEEP_TASK’, N’SLEEP_SYSTEMTASK’,
N’WAITFOR’, N’HADR_FILESTREAM_IOMGR_IOCOMPLETION’,
N’CHECKPOINT_QUEUE’, N’REQUEST_FOR_DEADLOCK_SEARCH’,
N’XE_TIMER_EVENT’, N’XE_DISPATCHER_JOIN’,
N’LOGMGR_QUEUE’, N’FT_IFTS_SCHEDULER_IDLE_WAIT’,
N’BROKER_TASK_STOP’, N’CLR_MANUAL_EVENT’,
N’CLR_AUTO_EVENT’, N’DISPATCHER_QUEUE_SEMAPHORE’,
N’TRACEWRITE’, N’XE_DISPATCHER_WAIT’,
N’BROKER_TO_FLUSH’, N’BROKER_EVENTHANDLER’,
N’FT_IFTSHC_MUTEX’, N’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’,
N’DIRTY_PAGE_POLL’, N’SP_SERVER_DIAGNOSTICS_SLEEP’)
)
SELECT
[W1].[wait_type] AS [WaitType],
CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
[W1].[WaitCount] AS [WaitCount],
CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
[W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) – [W1].[Percentage] < 95; — percentage threshold
GO