Archive | SOS_Scheduler_Yield RSS for this section

My SQL Server is being crushed under CPU pressure!

So I recently had someone reach out to me with issues on their SQL Server.  No matter what they did, whenever they would reboot the CPU usage of SQL Server would pummel their server.

After gaining access to the system I confirm that the sqlserver process is indeed up around 85%.

I run one of my favorite [stolen] queries:

WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (‘CLR_SEMAPHORE’,’LAZYWRITER_SLEEP’,’RESOURCE_QUEUE’, ‘SLEEP_TASK’
,’SLEEP_SYSTEMTASK’,’SQLTRACE_BUFFER_FLUSH’, ‘WAITFOR’, ‘LOGMGR_QUEUE’,’CHECKPOINT_QUEUE’
,’REQUEST_FOR_DEADLOCK_SEARCH’,’XE_TIMER_EVENT’, ‘BROKER_TO_FLUSH’,’BROKER_TASK_STOP’, ‘CLR_MANUAL_EVENT’
,’CLR_AUTO_EVENT’, ‘DISPATCHER_QUEUE_SEMAPHORE’, ‘FT_IFTS_SCHEDULER_IDLE_WAIT’
,’XE_DISPATCHER_WAIT’, ‘XE_DISPATCHER_JOIN’))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) – W1.pct < 95;

Using this — I was presented with SOS_Scheduler_Yield as the biggest offender by far.  For those of you unfamiliar [tsk tsk!], this confirms the CPU pressure.

A common cause of CPU issues [SOS_Scheduler_Yield] with SQL Server is actually very simple to solve:  missing indexes.

I pulled up another fun script of mine [thank you Brent Ozar]:

SELECT sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
, ‘CREATE NONCLUSTERED INDEX ix_IndexName ON ‘ + sys.objects.name COLLATE DATABASE_DEFAULT + ‘ ( ‘ + IsNull(mid.equality_columns, ”) + CASE WHEN mid.inequality_columns IS NULL
THEN ”
ELSE CASE WHEN mid.equality_columns IS NULL
THEN ”
ELSE ‘,’ END + mid.inequality_columns END + ‘ ) ‘ + CASE WHEN mid.included_columns IS NULL
THEN ”
ELSE ‘INCLUDE (‘ + mid.included_columns + ‘)’ END + ‘;’ AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, ‘isusertable’)=1
ORDER BY 2 DESC , 3 DESC

The results were what you would expect … extremely high impact for essentially all of the tables involved.  After getting permission [always ask for database you are not the administrator of!] — I went to work doing a couple of indexes at a time and assessing the performance.  After the first two indexes, CPU usage was down to 50%.  After another handful of indexes the machine stabilized around 20-30%.

Problem solved.

I hope this helps someone out there.

Please let me know if you have any comments or concerns!