SQL Server CPU Usage Tips for Performance Tuning

Problem

SQL Server can be a resource intensive process. When SQL Server acquires too much CPU, everything feels slower. Simple queries take longer, reports lag, and users start complaining. High CPU usage is one of the most common performance problems for DBAs. You need to find out why SQL Server is using so much CPU and how to reduce it. In this tip, we will walk through how to identify top CPU usage queries and mitigation.

Solution

There are couple of ways to find top CPU bound queries, in this article we will use dynamic management views (DMVs).

SQL Server CPU Utilization

SQL Server acquires available CPU cycles based on the workload. If there are a lot of processes that need CPU time, the overall system can become extremely slow.

Here are some common contributors to CPU pressure:

High logical reads due to missing indexes and/or out-dated statistics
Sudden increase in workload – a spike in users and processes could produce extra load on the system. Even a few large volume queries from a single user may affect the CPU.
Busy system with a lot of concurrency – users and processes
Queries running in parallel
Long running queries
Lots of calculations in query
Sorting
Use of temp tables
Other applications running on the server
Compression operations – data, backups, zip files, etc.

In this tip, we will only analyze high CPU usage occurring due to missing indexes and outdated statistics.

CPU Load Simulation

For simulating CPU stress, we will use a free tool SQLQueryStress.

For simulating CPU stress, let’s execute the below query using SQLQueryStress and generate load on your SQL Server (Figure-1).

SELECT TOP (1000) 
    [WorkOrderID] 
   ,[ProductID]
   ,[OrderQty]
   ,[StockedQty]
   ,[ScrappedQty]
   ,[StartDate]
   ,[EndDate]
   ,[DueDate]
   ,[ScrapReasonID]
   ,[ModifiedDate]
FROM [AdventureWorks2022].[Production].[WorkOrder]
WHERE OrderQty = 5;
GO
 
SELECT TOP (1000)     
    [BusinessEntityID]
   ,[PersonType]        
   ,[NameStyle]        
   ,[Title]        
   ,[FirstName]        
   ,[MiddleName]        
   ,[LastName]        
   ,[Suffix]        
   ,[EmailPromotion]        
   ,[AdditionalContactInfo]        
   ,[Demographics]        
   ,[rowguid]        
   ,[ModifiedDate]    
FROM [AdventureWorks2022].[Person].[Person]    
WHERE FirstName = 'ken';
GO

Figure – 1: Putting stress on SQL Server using SQLQueryStress

A Simple CPU Utilization Check

A simple way to check CPU usage is to review Task Manager on the server you are troubleshooting. Open Task Manager by right clicking on the taskbar and find the SQL Server process to see the corresponding metrics. Below we can see overall CPU usage is 99% and SQL Server is using 89% (Figure-2).

Figure-2: Task manager

Top CPU-Intensive Queries

The query below will help you find the top 10 queries that are using CPU resources. You can adjust the TOP value to see more data.

SELECT TOP 10  
   COALESCE(DB_NAME(st.dbid),DB_NAME(CONVERT(INT, qp.dbid))) AS [DatabaseName],
   qs.creation_time AS [PlanCreationTime],
   qs.last_execution_time AS [LastExecutedTime],
   qs.execution_count AS [ExecutionCount],
   qs.total_worker_time / 1000 AS [Total_CPU_Time_ms],
   (qs.total_worker_time / qs.execution_count) / 1000 AS [Avg_CPU_Time_ms],
   (qs.total_worker_time / 1000) AS [Cumulative_CPU_Time_All_Executions_ms],
   (qs.total_logical_reads + qs.total_logical_writes) AS [TotalLogicalIO],
   (qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count AS [AvgLogicalIO],
   SUBSTRING(st.text,
              (qs.statement_start_offset / 2) + 1,
              ((CASE qs.statement_end_offset
                   WHEN -1 THEN DATALENGTH(st.text)
                   ELSE qs.statement_end_offset
                 END - qs.statement_start_offset) / 2) + 1) AS [QueryText],
    qp.query_plan AS [ExecutionPlan]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE COALESCE(DB_NAME(st.dbid),DB_NAME(CONVERT(INT, qp.dbid))) NOT IN ('master', 'model', 'msdb', 'tempdb','DBADashDB')
ORDER BY qs.total_worker_time DESC;

Queries executed throughSQLQueryStresscreated tremendous pressure on the CPU and the above query identified them (Figure-3).

Figure-3: Top CPU-intensive queries

Queries Using High CPU with Missing Indexes

Missing indexes is a contributor to high CPU utilization. When an index is missing, SQL Server cannot quickly find the required rows. Instead of performing an Index Seek, it has to perform a table scan or index scan. That means it reads every row in the table to find the matching data. Which requires additional CPU cycles. The following query will display the top 10 queries with at least one missing index (Figure-4).

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT  
    COALESCE(DB_NAME(st.dbid),DB_NAME(CONVERT(INT, qp.dbid))) AS [DatabaseName],
    qs.creation_time AS [PlanCreationTime],
    qs.last_execution_time AS [LastExecutedTime],
    qs.execution_count AS [ExecutionCount],
    qs.total_worker_time / 1000 AS [Total_CPU_Time_ms],
    (qs.total_worker_time / qs.execution_count) / 1000 AS [Avg_CPU_Time_ms],
    (qs.total_worker_time / 1000) AS [Cumulative_CPU_Time_All_Executions_ms],
    (qs.total_logical_reads + qs.total_logical_writes) AS [TotalLogicalIO],
    (qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count AS [AvgLogicalIO],
    SUBSTRING(st.text,
              (qs.statement_start_offset / 2) + 1,
              ((CASE qs.statement_end_offset
                   WHEN -1 THEN DATALENGTH(st.text)
                   ELSE qs.statement_end_offset
                 END - qs.statement_start_offset) / 2) + 1) AS [QueryText],
    qp.query_plan AS [ExecutionPlan]
FROM    (
    SELECT  TOP 10 *
    FROM    sys.dm_exec_query_stats 
    ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.query_plan.value('count(//p:MissingIndexGroup)', 'int') > 0
GO

Figure-4: Top CPU-intensive queries with at least one missing index

If you click on the execution plan, it will open an execution plan window and show the missing index notification (Figure-5).

Figure-5: Query with missing index

You can execute the following query to generate scripts for missing index creation.

SELECT TOP 25  
   DB_NAME(dm_mid.database_id) AS DatabaseID,
   dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
   dm_migs.last_user_seek AS Last_User_Seek,
   OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
   'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
   + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
   + CASE
   WHEN dm_mid.equality_columns IS NOT NULL 
   AND dm_mid.inequality_columns IS NOT NULL THEN '_'
   ELSE ''
   END
   + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
   + ']'
   + ' ON ' + dm_mid.statement
   + ' (' + ISNULL (dm_mid.equality_columns,'')
   + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
   IS NOT NULL THEN ',' ELSE
   '' END
   + ISNULL (dm_mid.inequality_columns, '')
   + ')'
   + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

Executing the above query will show output like (Figure-6).

Figure-6: Query with missing index

Important Note:

Indexes can make or break your query or system. Before creating a new index, you should evaluate existing indexes, the necessity of new indexes, and overall performance impact. Otherwise, it may affect your system negatively.

Finding Out-dated Statistics

The query optimizer depends on statistics to estimate the cardinality or number of rows of a query result. It is the knowledge of how many rows are in a table, and how many of those rows will satisfy the various search and join conditions, and so on. Based on this, the optimizer creates the best query plan. When statistics are outdated, the optimizer cannot guess accurately the distribution of data. With a bad cardinality estimation, it may pick a nested loop join instead of a hash join, or skip using a useful index and may force more CPU work.

The following query will show all statistics data for the entire database (Figure-7).

SELECT DISTINCT  
   OBJECT_NAME(s.[object_id]) AS TableName,
   c.name AS ColumnName,
   s.name AS StatName,
   STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated,
   DATEDIFF(d,STATS_DATE(s.[object_id], s.stats_id),getdate()) DaysOld,
   dsp.modification_counter,
   s.auto_created,
   s.user_created,
   s.no_recompute,
   s.[object_id],
   s.stats_id,
   sc.stats_column_id,
   sc.column_id
FROM sys.stats s
JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
CROSS APPLY sys.dm_db_stats_properties(sc.[object_id], s.stats_id) AS dsp
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1)
ORDER BY DaysOld;
GO

Figure-7: Outdated statistics

Once you identified the out of date statistics, you can update all statistics using the following query.

EXEC sp_updatestats

Note that updating statistics will incur performance hit, as it will force recompiles of execution plans, which consumes CPU and I/O resources. You may observe a performance dip after running while SQL Server is recompiling and caching new plans.

Next Steps

M A A Mehedi Hasan

M A A Mehedi Hasan has been working with EBS Group since 2005. He started his career as a Software Developer and he is now Chief Technology Officer (CTO) of EBS Group. In his long journey, he had the opportunity to work with various technologies like the Microsoft Data Platform, SMS/USSD/IVR based Telecom Value Added Service, and Audio/Video streaming.

As a Microsoft certified Azure Database Administrator Associate, he manages large databases, testing and deployment, performance tuning, long term capacity planning, and streamlining operational workflow. He is also a certified AWS Cloud Practitioner and his team is migrating on-prem services to AWS.

Being a member of techforumbd, a Bangladeshi tech community, he regularly speaks and organizes sessions at local and virtual PASS chapters, SQL Saturdays, and Azure conferences. He also has a course about SQL Server available on the Ghoori Learning and Ostad.app platforms.

In his free time, he loves exploring and learning as much as possible about SQL Server.