The CPU is one of the most important resource on SQL Server. High CPU utilization may potentially cause bad performance .
How to Detect: Task Manager or Activity monitor is the best way to detect the CPU pressure on server.You may confirm from tasks manager that which process is consuming high CPU on the server.
Is SQL Server consuming high CPU?
Yes, if sqlservr.exe process is consuming high CPU then it is SQL. How to confirm if user sessions are consuming high CPU?
There are two counters in Perfmon to confirm if it is user process or SQL internal process
1. % User Time: High user Time indicates that user processes are consuming high CPU.
2. % Privilege Time: Indicates that SQL internal processes are consuming high CPU. Engage Microsoft SQL server support team immediately if privilege time is high.
How to troubleshoot high user time?
High user time could be the result of various issues.
- Queries with high CPU time
- Bad written query
- Small Buffer pool
- Too Many Compilation and Recompilation
- Too many transactions
- CXPACKET wait type
Let’s discuss each point in detail:
Queries with high CPU time:
SQL server keeps the stats of your queries and it records CPU time and total duration of query execution
Total Duration = CPU time + Total wait time
Total Wait time=Signal wait time + Resource wait time
Resource Wait Time: The query must have the required resources (like Data pages in memory, Memory grants etc...) before executing the query on CPU. The time elapsed for assigning these resource to process is known as Resource wait time.
Signal Wait Time: Once the query has all the required resources, it gets assigned to processor queue and waits for its turn. The time that a query spend in processor queue is known as Signal wait Time. After getting CPU, If any query doesn’t complete in 4 ms then it goes back to processor queue and waits for it’s turn again. The session shows SOS_Scheduler_yield wait when query yields for some other query until it gets the CPU again.
CPU Time: CPU time is the total duration for which a user query actually runs on CPU
If the CPU time is too high for the queries then it is one of the culprit of high CPU utilization.
Resolution: Fine tune the queries with high CPU time
Query to find top CPU consuming queries
SELECT TOP 20 SUBSTRING(eqt.TEXT, (eqs.statement_start_offset / 2) + 1, (
WHEN - 1
END - eqs.statement_start_offset
) / 2
) + 1) Statement
,eqs.total_elapsed_time / 1000000 Total_Duration_In_Sec
,eqs.last_elapsed_time / 1000000 Last_Duration_In_Sec
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_query_plan(eqs.plan_handle) eqp
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) eqt
ORDER BY eqs.total_worker_time DESC
Badly written queries:
If your query is fetching unnecessary rows or columns that are not required by your query, then you are using your CPU to perform lots of unnecessary work. while many physical reads may use lots of CPU cycles to perform IO, many logical reads may also increase the CPU time because of processing large data set.
However order by and group by does not increase the number of reads but unnecessary order by and group by may also increase the cpu time. also, if your server does not have enough memory then it may increase the physical IO because the operators like sort and hash aggregate will spill into tempdb.
Unnecessary loops or cursors may also increase the query run time and CPU time.
- Review the queries where logical or physical reads are too high and the wait time is moderately or too high. Fine tune these queries if applicable.
- Creating appropriate indexes may also reduce the reads significantly.
- Rewrite the query without using loops if possible.
Lack of adequate indexes:
If SQL server does not have adequate indexes, then most of your queries may end up having Table scans or Clustered Index scans and the processor will have to work extra to bring lots of unwanted data in memory. Lack of indexes may also increase the CPU time because of sort or Hash operators in the execution plan.
Resolution: Create adequate indexes of your long running queries. Also, Review Index utilization and missing index suggestions from SQL server
Small buffer pool:
If your buffer pool is small and you continuously see high values for counters like lazy Writes/Sec or Disk queue length and low values of counters like PLE, Buffer cache hit ratio then your server is probably using high CPU to perform physical IOs.
Resolution: Resizing the buffer size to achieve high value of PLE (more than 400 but may vary for each environment/Application) and buffer cache hit ratio (more than 96 %)
Too Many Compilation or Re-compilation:
Too many Compilation or Re-compilation may also cause high CPU utilization. Ideally compilation/sec should not be more than 10% of transactions/Sec and Recompilation/Sec should not be more than 10% of compilation/sec
- Review the queries that uses recompile option at procedure or query level
- Make less use of Ad-hoc queries or parameterize your queries
Too many Transaction:
If the number of transactions are more than usual then high CPU utilization is very obvious
Resolution: Create the benchmark of your server to compare the current scenario with the scenario when your server has been just fine.
Regularly review the resource utilization reports for your SQL servers to prepare for increasing requirement of your application
CXPACKET Wait type: CXPACKET wait type at the top of your wait type and high CPU utilization indicates the parallelism problem. I am not saying that parallelism is a problem but lots of CXPACKET wait type with parallelism is a problem when CPU utilization is often high on SQL server.
1. Follow Microsoft recommendation to set MAXDOP setting (Keep it to default if you don’t have high CPU problem and Lots of CXPACKET wait type.)
2. Change the Cost of parallelism to a more appropriate value (default is 5 and most of your queries get qualify for parallelism if you leave it as default. A value greater than 30 will be more appropriate number but may vary in your environment.)
3. Review index fragmentation report regularly. Re-index/Rebuild the indexes more frequently on highly transactional tables if index gets fragmented well before the next maintenance.
Here is a very good blog by mssqltips to know more about CXPACKET wait.
YouTube : Sql depository
Google + : https://plus.google.com/u/0/107093915534620159617