Friday, February 25, 2011

Identify SQL Server CPU Bottlenecks

We experience regular slowdowns on our Microsoft SQL database. After analyzing the memory usage we would like to continue the root cause investigation by examining CPU bottlenecks.

Here is my general recommendations to recognize CPU related bottlenecks in SQL Server. 

There are many reasons for CPU related performance problems on SQL Server. The main reason for CPU bottlenecks is insufficient hardware resources. CPU utilization can usually be reduced by configuration changes and query tuning so think before you rush out to buy faster or more processors.

You can use Performance Monitor to check the load on your CPU. Look for the Processor:% Processor Time counter: if it regularly exceeds 80% of the processor time per CPU then most probably you're facing a CPU related bottleneck.

Type 'perfmon' run and click enter, now performance window will open you have to create counter log and monitor below counters.

These are many counters, I usually prefer below counters to find any performance related issue in my production box. Right click on "Counter log" and select "New Log Setting" it will ask for name give any name, and click ok, 

After clicking OK, now new pop up box will open, here 3 tabs you can see, in general tab click 'Add Counter', again one more pop up box will open like below.

Here you have to select 'performance object' first then highlight the 'counter name' and click 'add'. Do the same for following counters.

    Memory – Available MBytes
    Paging File – % Usage
    Physical Disk – Avg. Disk sec/Read
    Physical Disk – Avg. Disk sec/Write
    Physical Disk – Disk Reads/sec
    Physical Disk – Disk Writes/sec
    Processor – % Processor Time
    SQLServer: Buffer Manager – Buffer cache hit ratio
    SQLServer: Buffer Manager – Page life expectancy
    SQLServer: General Statistics – User Connections
    SQLServer: Memory Manager – Memory Grants Pending
    SQLServer: SQL Statistics – Batch Requests/sec
    SQLServer: SQL Statistics – Compilations/sec
    SQLServer: SQL Statistics – Recompilations/sec
    System – Processor Queue Length

After adding above counters click close, now you came back to previous window, now go 'Log File' tab, in this tab select 'log type' is "Text File (Tab Delimited)" and click 'configure' and select the file location, where you want to save the output.

And 3rd Tab is for Scheduling to do run this in a any particular time to capture the data, if suppose if any specific time you are facing CPU bottleneck or something else you can automate this.

After running one or two days or hours of these counters, and stop the counters, format the excel sheet for easy understanding like below, put the formulas for Average, median, min, max, Std Deviation. and you can easily figure it out what was the maximum value and it average value for each individual counters.

Below sheet are just recommendations and not exact recommendations because each scenario and each server configuration is different from other. You must collect a base-line of data to compare the above values.

You Want
Available Mbytes
> 100MB
Unused physical memory (not page file).
Paging File
% Usage
< 70%
Amount of Page File in use, which indicates the server is substituting disk space for memory.
Physical Disk
Avg. Disk sec/Read
< 8ms
A key measure of disk latency representing the average time, in milliseconds, of each read to disk where > 20 is poor, <20 is good/fair, <12 is better, <8 is best
Physical Disk
Avg. Disk sec/Write
< 8ms (non cached)
< 1ms (cached)
A key measure of disk latency representing the average time, in milliseconds, of each write to disk, where non-cached writes ( > 20 poor, <20 fair, <12 better, <8 best) differ significantly from cached writes (> 4 poor, <4 fair, <2 better, <1 best ). For OLTP databases, the lower this number the better, especially for disks holding the transaction log.
Physical Disk
Disk Reads/sec

Physical Disk
Disk Writes/sec

% Processor Time
< 80%
Percentage of elapsed time the processor spends executing non-idle threads.
SQLServer: Buffer Manager
Buffer cache hit ratio
The percentage of pages that were found in the memory. Higher the value the better. Preferred around the 90% mark. This included the availability of the procedure and the data cache in the system.
SQLServer: Buffer Manager
Page life expectancy
> 300
Tells, on average, how many seconds SQL Server expects a data page to stay in cache. The target on an OLTP system should be at least 300 (5 min). When under 300, this may indicate poor index design (leading to increased disk I/O and less effective use of memory) or, simply, a potential shortage of memory.
SQLServer: General Statistics
User Connections
See Description
The number of users currently connected to the SQL Server. This counter should roughly track with “Batch Requests/Sec”. They should generally rise and fall together. For example, blocking problems could be revealed by rising user connections, lock waits and lock wait time coupled with declining batch requests/sec.
SQLServer: Memory Manager
Memory Grants Pending
See Description
Total number of processes waiting for a workspace memory grant.
SQLServer: SQL Statistics
< 10% of the number of Batch Requests/Sec
Number of times that Transact-SQL compilations occurred, per second (including recompiles). The lower this value is the better. High values often indicate excessive adhoc querying and should be as low as possible. If excessive adhoc querying is happening, try rewriting the queries as procedures or invoke the queries using sp_executeSQL. When rewriting isn’t possible, consider using a plan guide or setting the database to parameterization forced mode.
SQLServer: SQL Statistics
< 10% of the number of SQL Compilations/sec
Number of times, per second, that Transact-SQL objects attempted to be executed but had to be recompiled before completion. This number should be at or near zero, since recompiles can cause deadlocks and exclusive compile locks. This counter’s value should follow in proportion to “Batch Requests/sec” and “SQL Compilations/sec”. This needs to be nil in your system as much as possible.
Processor Queue Length
< 4 per CPU
Number of threads waiting for CPU cycles, where < 12 per CPU is good/fair, < 8 is better, < 4 is best.
What Perfmon counters should I monitor and what, CPU Bottleneck, Detecting Processor Bottlenecks, CPU Bottleneck What is it, and how it affects your application, Identifying and Addressing CPU Bottleneck, What is bottleneck, How to resolve application slow down, application slow, How to Identify SQL Server CPU Bottlenecks, SQL Performance Analysis, SQL Server 2005 CPU bottleneck, SQL Server 2008 CPU bottleneck, SQL Server 2008 R2 CPU bottleneck, SQL Server 2012 CPU bottleneck, Detect CPU Bottleneck in SQL Server, Beware of CPU bottlenecks on SQL Server, How to Monitor for High CPU utilization in SQL Server, Slow in the Application, Fast in SSMS, Application is Slow, How do I monitor SQL Server performance, Checklist for Analyzing Slow-Running Queries, SQL Query slow in .NET application but instantaneous in SQL Server, How to Identify Slow Running Queries with SQL Profiler, Troubleshooting CPU bottleneck, Sporadically Slow Calls From .NET Application To SQL Server, SQL Troubleshooting for Database Server Slowness, Potential Bottlenecks for Performance, Four Resource Bottlenecks to Monitor in SQL Server 2008, Identify Bottlenecks, Troubleshooting Performance Problems in SQL Server 2012, Top SQL Server 2005 Performance Issues for OLTP Applications, Troubleshooting Application Timeouts in SQL Server, Extending your SQL server timeout setting, Application timeout, System-Level Bottlenecks, Top SQL Server 2005 Performance Issues for OLTP


Post a Comment