Detecting CPU Pressure

There are a few different ways to detect CPU pressure, for example, Performance Monitor or SQL Profiler, but in this tip we’ll discuss the wait type SOS_SCHEDULER_YIELD and the DMV sys.dm_os_schedulers.

Some of you might have seen the wait type SOS_SCHEDULER_YIELD and wondered what in the world is this? Books Online (BOL) describes this wait type as:

Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.

SOS_SCHEDULER_YIELD is a fairly common wait type and occurs when there is CPU pressure. SQL Server runs multiple threads and tries to allow all threads to run without problems. However, if all the threads are busy on each scheduler and can’t let other threads run, it will yield itself for another thread, which in turn creates the SOS_SCHEDULER_YIELD wait type.

You can see the top wait stats and the percentage of waits at the current point in time by running the following query by Tim Ford and Glenn Berry.

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', 
'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT') 
) -- filter out additional irrelevant waits 
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; -- percentage threshold;

After running this query on one of my servers I can see that only 2.22% of my waits are due to CPU pressure so I can rule that out as a cause for poor performance. (This is only current since the last SQL Service restart):

SQL Freelancer SQL Server CPU Pressure DMV

If you see a high percentage of this wait type then one way of troubleshooting this is to use the DMV sys.dm_os_schedulers. BOL describes sys.dm_os_schedulers as:

Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor. Use this view to monitor the condition of a scheduler or to identify runaway tasks.

This DMV has several columns including number or workers, active tasks, and the status of each scheduler, etc., which can help troubleshoot certain problems, but the most important column is the one used for measuring queued tasks, the runnable_tasks_count column. This column displays the count of tasks yielding to other tasks resulting in a SOS_SCHEDULER_YIELD wait type. If this column is frequently greater than 0 then CPU pressure may be present and blocking may occur.

To see an average of current tasks and current waiting tasks you can use the following query:

SELECT AVG(current_tasks_count) AS [Avg Current Task], 
AVG(runnable_tasks_count) AS [Avg Wait Task]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
AND status = 'VISIBLE ONLINE'

SQL Freelancer SQL Server CPU Pressure DMV
Click here to view the rest of this post.

PowerPivot with Excel 2013

Excel 2013 changes things up a bit when it comes to installing PowerPivot. In previous versions you had to download the component and install, but with Excel 2013 it comes installed as an add-in, but disabled by default. To enable PowerPivot, open Excel, go to File, Options, Add-Ins, select COM Add-ins and click Go.

SQL Freelancer SQL Server Excel PowerPivot
This will open up the COM Add-Ins dialog box. Click “Microsoft Office PowerPivot for Excel 2013” and hit OK.

SQL Freelancer SQL Server Excel PowerPivot
After successfully enabling PowerPivot, the tab should appear at the top of the Excel spreadsheet.

SQL Freelancer SQL Server Excel PowerPivot

Creating a dashboard

There are a few different ways in which to import data into Excel to use with PowerPivot. Some of these ways include:

  • From database

SQL Freelancer SQL Server Excel PowerPivot

  • From Data Service

SQL Freelancer SQL Server Excel PowerPivot

  • From other sources such as Oracle, Excel, flat files, etc.

For this example, and simplicity sake, I will just run a query and simply copy and paste my results into the Excel spreadsheet. The query results look like this:

SQL Freelancer SQL Server Excel PowerPivot
Once the results are copied and pasted into Excel, click the PowerPivot tab and click Add to Data Model:

SQL Freelancer SQL Server Excel PowerPivot
On the create table dialog box, make sure you select the range for your data and click “My table has headers”

SQL Freelancer SQL Server Excel PowerPivot
After clicking OK, the PowerPivot window should appear. To start creating the dashboard, click PivotTable, PivotChart, then select New Worksheet:

SQL Freelancer SQL Server Excel PowerPivot
Click here to view the rest of this post.