Troubleshoot CPU issue

 **Solving High CPU Utilization in SQL Server** 🔧

Recently, I encountered a high CPU utilization issue on one of our SQL Server instances, which was impacting performance and responsiveness. Here's how I approached and resolved the issue:

### **Symptoms:**
- Consistently high CPU usage
- Slow query performance
- Delays in application responses

### **Investigation Steps:**

1. **Identify the Culprit Queries:**
- Used **SQL Server Management Studio (SSMS)** to monitor active sessions and identify long-running queries.
- Ran the following query to find the top CPU-consuming queries:
```sql
SELECT TOP 10
qs.sql_handle,
qs.execution_count,
qs.total_worker_time AS CPU_Time,
qs.total_elapsed_time AS Total_Time,
qs.total_logical_reads AS Reads,
qs.total_logical_writes AS Writes,
SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS query_text
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY
qs.total_worker_time DESC;
```

2. **Analyze Query Execution Plans:**
- Analyzed execution plans for the top queries to identify any inefficiencies or missing indexes.

3. **Check Index Usage:**
- Used the **Database Engine Tuning Advisor** to suggest missing indexes and other optimizations.
- Verified the existing indexes and their usage patterns.

4. **Look for System Bottlenecks:**
- Monitored system resources (CPU, memory, disk I/O) to ensure there were no other bottlenecks.

### **Solutions Implemented:**

1. **Optimized Queries:**
- Refactored inefficient queries to reduce CPU load.
- Added necessary indexes based on execution plan analysis.

2. **Updated Statistics:**
- Ensured that statistics were up-to-date to help the SQL Server query optimizer make better decisions.
```sql
EXEC sp_updatestats;
```
3. **Index Maintenance:**
- Scheduled regular index maintenance tasks to rebuild and reorganize fragmented indexes.

4. **Resource Management:**
- Configured **Resource Governor** to manage workload distribution and prevent a single process from consuming too much CPU.

### **Results:**
- Significant reduction in CPU utilization
- Improved query performance and application responsiveness
- More stable and predictable system behavior

### **Takeaway:**
High CPU utilization in SQL Server can often be traced back to inefficient queries or poor indexing strategies. Regular monitoring and maintenance are key to maintaining optimal performance.

If you've faced similar issues, I'd love to hear about your experiences and solutions! Feel free to share your thoughts.

-- Database wise CPU stats

WITH DB_CPU_Stats
AS
(
SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName],
SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (
SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID
)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
DatabaseName,
[CPU_Time_Ms],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
--WHERE DatabaseID > 4 -- system databases
--AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);