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.
Recommendation:
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.
Object
|
Counter
|
You Want
|
Description
|
Memory
|
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
|
% Processor Time
|
< 80%
|
Percentage of elapsed time the processor spends executing non-idle threads.
|
SQLServer: Buffer Manager
|
Buffer cache hit ratio
|
>90%
|
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
|
Compilations/sec
|
< 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
|
Recompilations/sec
|
< 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.
|
System
|
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.
|
