SQL Server Performance - Common Symptoms and Tools (Part I)

A few common complaints from customers due to high CPU utilizationare"SQL Server appears to be hung,” “SQL Server is not responding,""Queries are running slow," "Users cannot connect," "Websites are timing out," and so on.
The most common tools required to investigate high CPU utilizationare the System Monitor (a.k.a. the Performance Monitor), SQL Profiler, and theBlocker Script. More information about the Blocker Script can be found fromthese articles:
http://support.microsoft.com/?id=271509(SQL Server 2000)
http://support.microsoft.com/?id=251004(SQL Server 7.0)
Keep in mind a few key counters/objects of these tools when investigatinghigh CPU issues. Here is the breakdown:
1. System Monitor with the following counters:
• Processor --> % Processor Time
• Process --> % Processor Time (all processes)
• Thread --> % Processor Time (all threads of the SQL Server Instance)
• SQL Server --> SQL Statistics/SQL Compilations/sec
• SQL Server --> SQL Statistics/SQL Re-Compilations/sec
• Server Work Queues --> Queue Length
2. SQL Profiler with the following events:
• SQL:BatchStarting
• SQL:BatchCompleted
• RPC:Starting
• RPC:Completed
• Attention
• Exception
• Show Plan Statistics
3. Blocker Script with a polling interval of 15 seconds.
Investigating the System Monitor
Start with the Processor --> % Processor Timecounter to verify the primary CPU activity. Also check to see if the CPU ishigh for any reasonable amount of time, or if it just spikes occasionally. Ifthe percentages are consistently over 90 percent, then it is a CPU bottleneck.
The next step is to verify if the SQL Server process is the mainconsumer of this CPU utilization. Check the Process -->% Processor Time counter of the SQL Server instance. This value needsto be divided by the number of processors to get an average percentage of CPUutilization of the SQL Server process per processor. If you have high CPU valuesfor the system and low values for the SQL Server process, this indicates thatsome other process is causing the high CPU utilization. It is not a SQL Serverissue at that point. However, if you observe that the culprit is the SQL Serverprocess, please see below for additional steps.
Next review the % Processor Time counterof all the SQL Server threads to identify the threads that are using up allthe CPU. The following article can be used for correlating the thread ID witha particular server process identifier (spid):
http://support.microsoft.com/?id=117559
Once the spid is identified, look into the profiler trace to identifythe CPU intensive query that that spid is running. This query may be tuned toresolve the high CPU utilization.
The SQL Server -->SQL Statistics/SQL Compilations/seccounter would be a good counter to identify if SQL Server is compilinga number of execution plans; a high value for this counter would be indicativeof high CPU utilization.
The SQL Server --> SQL Statistics/SQL Re-Compilations/seccounter will indicate the number of times that the server had to recompile anexecution plan. If the values are higher than 10 percent, then the many recompilationscould be the possible cause of the high CPU utilization.
The Server Work Queues --> Queue Lengthwill indicate the current length of the server work queue for this CPU. If thequeue length is consistently greater than two (2), then it may be an indicationof processor congestion and there may be a need for more or faster processors.
Although the System Monitor will give a good idea where the problemlies, to pinpoint exactly what queries are CPU bound or what type of queriesare getting compiled/recompiled often, you usually need profiler traces to investigatefurther.
Investigating SQL Profiler
Bring up the profiler trace that was captured during the time theSystem Monitor log was also captured, group it by CPU, and look at the largeCPU values for the RPC:Completed or the SQL:BatchCompletedevents. This will indicate the queries that are CPU intensive.
Once you identify these queries, the next step is to tune the queriesand make them less CPU intensive. These could be ad hoc SQL Statements or StoredProcedure executions. Get the output run in the Query Analyzer as follows (enterthe output of the Query Analyzer in TEXT mode by hitting Ctrl ^ T):
Set Statistics IO On
Set Statistics Profile On
Set Statistics Time On
Go
<>
Go
Set Statistics IO Off
Set Statistics Profile Off
Set Statistics Time Off
Go
Get the generated output above, then look for the operators beingused in the execution plan and the cost of each step in the execution plan.Hash Joins and Merge Joins (a Merge Join after explicit Sort of Columns) aregenerally CPU bound compared to a Nested Loop Join.
Most likely the Optimizer is choosing Hashing or Sorting followedby a Merge Join due to the lack of indexes. One of the most important aspectsof query tuning is making sure that the appropriate indexes exist. Run theseCPU intensive statements through the Index Tuning Wizard (ITW) in order to getany index recommendations that may be necessary.
Also, long compilation times of statements can cause excessive usageof CPU resources looking for the "better" plan. Look at the StatisticsTime output for the "parse and compile" times. For example
SQL Server parse and compile time: CPU time = 2032 ms, elapsed time = 2054 ms
That being said, there have been some cases where the Optimizerchooses a Nested Loop Join and this causes high CPU utilization. Remember thatwith a Nested Loop Join operation, for each row of the outer table, all matchingrows in the inner table must be found. Think of it like a For Loop within anotherFor Loop in programming terms. If both the inner and outer tables are very large(lots of iterations), a Hash Join is preferable. So the Optimizer may have chosena Nested Loop Join based on stale or no statistics on the columns in the joincriteria. If the statement in question is a Stored Procedure, apart from theprevious reasons, a poor execution plan may be selected because of “parametersniffing.” Please see bug 362818, which has a workaround.
If you would like to troubleshoot the issue based on a profilertrace, compilations can be identified by looking at the SP:CacheInsertevent. If you see a SP:CacheInsert event every timea query is executed, this may indicate the query is not getting parameterized.Select * from master..syscacheobjects can also showif the query is being parameterized. Maybe using sp_executesqlor modifying the ad hoc statement to a stored procedure is a good idea to eliminatethe extra time the Optimizer spends in compiling the statement every time itis executed.
The following are some of the causes of a stored procedure being recompiled:
• Changing SET options between executions
• Objects are not owner qualified in the stored procedure, and also when they are invoked
• Schema changes on the objects while the procedures are being executed
• References to temporary tables created in different stored procedures
Look at the SP:Recompile event in the profilertrace and pay close attention to the EventSubClassdata column. The value for the EventSubClass will letyou know the reason for the recompilation. You can then avoid the unnecessaryrecompilations so as to avoid high CPU utilization. Please see the followingKB Article which has more information on this:
http://support.microsoft.com/?id=308737
Another way that you can avoid these excessive recompiles is byusing the query hint OPTION (KEEPFIXED PLAN). Please use caution in implementingthis workaround to fix recompilation issues, as there are caveats to using thishint.
Investigating the Blocker Script Output
There may also be a CPU intensive query that started but never completed.For such queries you will not see the amount of CPU used in the profiler trace.In this case, the output from the Blocker Script will be useful. Review theCPU column of the sysprocesses table. Look for an increasingtrend of the CPU column for a particular spid. By matching the times, you canidentify the corresponding query this particular spid is executing from theprofiler trace.
Last Words
Resolving high CPU utilization issues can be very time-consuming,especially when you don‘t know where the problem lies. Try to solve the problem,and not the symptoms. With proper optimization techniques, such as adding properindexes, redesigning badly written queries, and so on, you can avoid almostall of these issues.
© 2003 Microsoft