Home > Performence Tuning, SQL server DBA administration > CPU pressure and optimal number of CPUs to handle the workload

CPU pressure and optimal number of CPUs to handle the workload


CPU pressure and optimal number of CPUs to handle the workload

 

 

AS a DBA we are faced the question from a customer asks :I am running a DSS/ OLAP like SQL job and it takes x amount of time, how can I reduce the time so the SQL job completes sooner, can I add more CPUs ? if yes, how many ? And we are getting high CPU alert also so here need to analysis the problem can be resolved by adding the CPU.

 

I am follow the below query step by step to decide the CPU pressure stage.

——————————————————————————————————————

 

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;

 

Note: 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.

 

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’

–query we can see how many processors SQL Server is using:

 

SELECT COUNT(*) AS proc# 
FROM sys.dm_os_schedulers 
WHERE status = 'VISIBLE ONLINE' 
AND is_online = 1

 

The following query will show current tasks running, CPU time, wait type, etc.:

 

SELECT s2.text, 
session_id,
start_time,
status, 
cpu_time, 
blocking_session_id, 
wait_type,
wait_time, 
wait_resource, 
open_transaction_count
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS s2  
WHERE status <> 'background'

 

Query to find out how many CPU on an average will help to scale the workload better.

 

When concurrent users apply simultaneous DSS/OLAP like CPU intensive workload, there could be CPU pressure. We can conclude presence of CPU pressure  by use of the above command .

 

In this case one can run the below query to find out how many CPU on an average will help to scale the workload better. It might be more informative to collect the below information in short time intervals (many samples) than just once to understand during which time of the workload application the CPU pressure was the most. Single sample will lead to average additional CPUs necessary for the entire workload duration.

 

  1. Reset Wait Stats

dbcc sqlperf(‘sys.dm_os_wait_stats’, clear)–example provided by http://www.sqlworkshops.com

2. Apply workload (you can find sample workload query at the end of this article, you need to execute the sample workload query simultaneously in many sessions to simulate concurrent user tasks).

3. Run the below query to find Additional CPUs Necessary it is important to run the query right after the workload completes to get reliable information.

select round(((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / (convert(float, si.os_quantum) /si.cpu_ticks_in_ms) * cpu_count), 2) as Additional_Sockets_Necessary
from sys.dm_os_wait_stats ws cross apply sys.dm_os_sys_info si where ws.wait_type =‘SOS_SCHEDULER_YIELD’

–example provided by http://www.sqlworkshops.coms

The SQL Server 2008 query:

select round(((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / si.os_quantum *scheduler_count), 2) as Additional_Sockets_Necessary
from sys.dm_os_wait_stats ws cross apply sys.dm_os_sys_info si where ws.wait_type =‘SOS_SCHEDULER_YIELD’

–example provided by http://www.sqlworkshops.com

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: