High CPU usage query & Adding Index

CASE: Specific query is consuming up CPU resource cause CPU Spike(100% usage) when the DB hit count is increased.

Type: MSSQL 2012

In such a case, mostly the query is known, so it can be solved by creating index for this query.

But in emergency status, when Application Designer is absent and when the instant response is needed by DBA to solve the issue, the wizard of MS SQL – ease to use — is ready, so the index could be created temporarily as the steps below:

  1. Run SSMS.
  2. Right click the SQL Server Instance of  tree root, select the “Activity Monitor”.
  3. In “Recent Expensive Queries” pane, right click the query in matter, select “display the execution plan”
  4. The step which consuming resource highly, then right click the “Missing index…” (A new tab opened automatically).
  5. In that tab, create the index using the statement that Wizard prepared by editing meta data (like index name…)
  • This case is available only  when the query is evaluated by execution plan as “problem occurred by missing index”

1,912 total views, 6 views today

Leave a Reply