MSSQL high cpu usage

Question

What are some possible causes and troubleshooting steps to address high CPU usage in an MSSQL server?

Answer 1

High CPU usage in an MSSQL server can be caused by various factors, and addressing it requires thorough troubleshooting. Here are some possible causes and steps to mitigate high CPU usage:

1. Poorly optimized queries: Inefficient or poorly written queries can result in high CPU usage. To address this, identify the queries causing the high CPU usage using tools like SQL Server Profiler or Extended Events. Optimize these queries by reviewing their execution plans, indexing strategies, and query structure.

2. Indexing issues: Inadequate or incorrect indexing can lead to excessive CPU usage. Analyze the indexes used by the problematic queries and ensure they are appropriately designed. Consider adding or modifying indexes to improve query performance.

3. Outdated statistics: Outdated or inaccurate statistics can impact query optimization, leading to excessive CPU usage. Update statistics using the UPDATE STATISTICS command or enable the AUTO_UPDATE_STATISTICS option to ensure the query optimizer has accurate information for query plan generation.

4. Insufficient hardware resources: If the server's hardware resources (CPU, memory, disk I/O) are insufficient for the workload, it can cause high CPU usage. Monitor resource utilization using tools like Performance Monitor or DMVs (Dynamic Management Views) to identify any bottlenecks. Consider upgrading hardware or optimizing server configuration parameters like max degree of parallelism (MAXDOP) and memory allocation.

5. External processes or applications: CPU-intensive external processes or applications running on the same server can impact SQL Server's CPU usage. Identify and optimize these processes by reviewing their resource consumption and adjusting their configurations if necessary.

6. SQL Server configuration: Review the SQL Server configuration settings such as maximum server memory, parallelism settings, and priority boost. Adjust these settings based on your server's specific requirements and workload to optimize CPU usage.

7. Blocking and contention: Long-running or blocked transactions can contribute to high CPU usage. Monitor for blocking using tools like SQL Server Profiler or sp_whoisactive and address any blocking issues by tuning queries, optimizing indexes, or adjusting isolation levels.

8. SQL Server version and patches: Ensure that your SQL Server instance is running the latest service pack and cumulative updates. Microsoft frequently releases patches that address performance-related issues, including high CPU usage.

9. Third-party tools or components: Evaluate the impact of any third-party tools or components integrated with your SQL Server environment. Ensure they are up to date and compatible with your SQL Server version.

Remember, high CPU usage in MSSQL can have multiple causes, and the appropriate troubleshooting steps may vary depending on your specific environment. It's recommended to consult Microsoft documentation, seek assistance from database administrators, or engage with the MSSQL community for further guidance tailored to your situation.

Answer 2

Find the high CPU-consuming queries with the below statement
 

To identify the queries that are responsible for high-CPU activity currently, run the following statement:

SELECT TOP 10 s.session_id,
           r.status,
           r.cpu_time,
           r.logical_reads,
           r.reads,
           r.writes,
           r.total_elapsed_time / (1000 * 60) 'Elaps M',
           SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
           ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.TEXT)
                ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) + 1) AS statement_text,
           COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
           + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
           r.command,
           s.login_name,
           s.host_name,
           s.program_name,
           s.last_request_end_time,
           s.login_time,
           r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

If queries aren't driving the CPU at this moment, you can run the following statement to look for historical CPU-bound queries:
 

SELECT TOP 10 st.text AS batch_text,
    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 statement_text,
    (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
    (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
    (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC