Diagnosing and Resolving SQL Server High CPU Usage with Profiler and Index Tuning

Problem Overview

A production SQL Server instance showed sustained 100% CPU usage. Monitoring indicated that the SQL Server process was the primary consumer, and disconnecting the network instantly lowered CPU, pointing to a query‑related bottleneck.

Trace Collection with SQL Server Profiler

A 20‑minute trace was captured and saved as an *.rtc file. Analysis of the trace isolated two heavy statements:

-- Query 1: Retrieve the latest 30 alarm events
SELECT TOP 30 a.orderno, a.AgentBm, a.AlarmTime, a.RemoveTime, c.Name AS AddrName,
       b.Name AS MgrObjName, a.Ch, a.Value, a.Content, a.Level,
       ag.Name AS AgentServerName, a.EventBm, a.MgrObjId, a.Id, a.Cfmoper,
       a.Cfm, a.Cfmtime, a.State, a.IgnoreStartTime, a.IgnoreEndTime,
       a.OpUserId, d.Name AS MgrObjTypeName, l.UserName AS userName,
       f.Name AS AddrName2
FROM   eventlog AS a
       LEFT JOIN mgrobj AS b ON a.MgrObjId = b.Id AND a.AgentBm = b.AgentBm
       LEFT JOIN addrnode AS c ON b.AddrId = c.Id
       LEFT JOIN mgrobjtype AS d ON b.MgrObjTypeId = d.Id
       LEFT JOIN eventdir AS e ON a.EventBm = e.Bm
       LEFT JOIN agentserver AS ag ON a.AgentBm = ag.AgentBm
       LEFT JOIN loginUser AS l ON a.cfmoper = l.loginGuid
       LEFT JOIN addrnode AS f ON ag.AddrId = f.Id
WHERE  (MgrObjId IN (SELECT Id FROM MgrObj WHERE AddrId IN ('','02100000','02113000',...))
   OR  mgrobjid IN ('00000000-0000-0000-0000-000000000000','11111111-1111-1111-1111-111111111111'))
ORDER BY AlarmTime DESC;

-- Query 2: Count total alarm records
SELECT COUNT(*)
FROM   eventlog AS a
       LEFT JOIN mgrobj AS b ON a.MgrObjId = b.Id AND a.AgentBm = b.AgentBm
       LEFT JOIN addrnode AS c ON b.AddrId = c.Id
       LEFT JOIN mgrobjtype AS d ON b.MgrObjTypeId = d.Id
       LEFT JOIN eventdir AS e ON a.EventBm = e.Bm
WHERE  MgrObjId IN (SELECT Id FROM MgrObj WHERE AddrId IN ('','02100000',...))
  AND mgrobjid NOT IN ('00000000-0000-0000-0000-000000000000','11111111-1111-1111-1111-111111111111');

Both statements suffered from expensive sorting, multiple joins, and full table scans, causing high CPU consumption.

Root Causes of High CPU

Frequent compilation/recompilation of execution plans.

CPU‑intensive operators: ORDER BY, DISTINCT, aggregations (e.g., AVG, SUM).

Join algorithms (Nested Loop, Hash) that require substantial CPU.

Unselective COUNT(*) on large tables leading to full scans.

Execution‑Plan Analysis

For the top‑30 query the plan showed a Sort operator consuming ~94% of the cost. Replacing the ORDER BY AlarmTime with ORDER BY orderno (the clustered key) reduced runtime from 2‑3 s to <1 s, but the ordering semantics differed.

Index to Eliminate Sort

Creating a non‑clustered index on AlarmTime allowed the optimizer to produce an index‑ordered result set, removing the Sort operator.

IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID('eventlog') AND name = 'IX_eventlog_AlarmTime')
BEGIN
    CREATE NONCLUSTERED INDEX IX_eventlog_AlarmTime
        ON dbo.eventlog (AlarmTime);
END;

After the index build the plan no longer contained a Sort node and CPU usage dropped noticeably.

Optimizing the COUNT Query

The original count query performed two scans of the 200 k‑row eventlog table. Adding a simple index on MgrObjId did not help because the query used IN / NOT IN predicates and selected columns that were not covered.

Covering Index

A covering (included) index that contains the filtered column and the columns referenced in the SELECT list forces an index seek and eliminates the table scan.

IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID('eventlog') AND name = 'IX_eventlog_MgrObjId')
BEGIN
    CREATE NONCLUSTERED INDEX IX_eventlog_MgrObjId
        ON dbo.eventlog (MgrObjId)
        INCLUDE (EventBm, AgentBM);
END;

Post‑creation the execution plan showed an Index Seek, and CPU consumption for the count operation fell dramatically.

Additional Optimization Techniques

Implement server‑side push notifications so clients query only when new alerts arrive.

Replace COUNT(*) with COUNT(0) when the result set does not require column values.

Pre‑fetch distinct MgrObjId values in a separate step before joining to reduce join overhead.

Add appropriate indexes to related tables (e.g., management objects, address nodes).

Partition the large eventlog table and rebuild indexes during off‑peak windows to mitigate insert‑performance impact.

Verification Procedure

To confirm the impact of the index changes, the following DMV query can be used to capture per‑statement CPU metrics before and after the modifications:

SELECT TOP 10
    qs.sql_handle AS [SQL Statement],
    qs.last_execution_time,
    (qs.total_logical_reads + qs.total_physical_reads + qs.total_logical_writes) / qs.execution_count AS [Avg IO],
    (qs.total_worker_time / qs.execution_count) / 1000000.0 AS [Avg CPU (sec)],
    (qs.total_elapsed_time / qs.execution_count) / 1000000.0 AS [Avg Elapsed (sec)],
    qs.execution_count,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY [Avg CPU (sec)] DESC;

Comparing the average CPU time for the two target queries before and after index creation demonstrates a clear reduction.

Summary of Best Practices

Identify the high‑CPU process and capture offending statements with SQL Server Profiler.

Inspect execution plans to locate costly operators such as Sort and Table Scan.

Create targeted non‑clustered indexes on columns used in ORDER BY and WHERE clauses.

When possible, build covering indexes (including needed columns) to force index seeks.

Validate performance gains by measuring CPU before and after changes.

Consider complementary tactics: server‑side push, query rewriting, and table partitioning to sustain low CPU usage.