Identifying High CPU SQL Processes

Download PDF
Identifying High CPU SQL Processes

Executive Summary

As a SQL Server administrator you are probably familiar with this scenario: The phone rings and it is one of your users saying “the system seems really slow today”, or if you have monitoring and alerting set up you get an email telling you the CPU(s) on your SQL server is maxed at 100%. Regardless of the method of alert, you have to figure out what is causing the problem. You investigate and find that the sqlservr.exe process is using most, if not all of the available CPU cycles. Beyond that however, you have no idea of exactly what in SQL Server is causing the CPU to be pegged at 100%.

When faced with the issue of identifying the exact runaway process or statement that is bringing your SQL Server to its knees you will find that there is no easy solution. There is no single place in the provided tools to dynamically list the percent of CPU taken by each SQL Server thread process.

Even identifying the specific SQL Server process leaves you grasping for more information. You really want to know the exact code or statement that is soaking up precious CPU cycles. Using the techniques described in this paper you will be able to identify the culprit and react appropriately, adding efficiency and value to your organization.



Abstract

Purpose
The purpose of this paper is to describe the methods that can be used to determine what SQL Server process is consuming CPU cycles. Through the use of Performance Monitor, Query Analyzer, Enterprise Manager and Profiler, tools provided with Microsoft Windows and Microsoft SQL Server, the administrator can pinpoint the specific query or operation that is causing high CPU.

Step-by-step instructions, including screen captures, detailing the setup and execution are provided to enable the system administrator to quickly and accurately determine the exact cause and start him on the correct course to fix the problem. A special section addressing cursors and stored procedures is included to deal with situations where the statement causing the problem is not readily apparent as is common in applications such as JD Edwards EnterpriseOne.

A bonus section is included with additional tips to make the methods described in the paper quick and easy to repeat. Several additional tips related to Oracle JD Edwards EnterpriseOne are included.

Total Pages - 22