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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s