Home / server / Questions / 639058
Accepted
Shawn de Wet
Asked: 2014-10-24 02:08:14 +0800 CST

How to tell what process in SQL is making up the Processor Time reported in Activity Monitor

  • 772

In SQL Activity Monitor, I see a constant 35% to 40% CPU usage in the Processor Time scrolling graph. How can I tell what Processes within SQL are responsible for that CPU usage?

sql-server

2 Answers

  1. The only way to see EVERYTHING that makes up what SQL Server is using the processor for, is to turn on Profiler, with ALL options turned on.

    Don't do that. It is, of course, infeasible, and would actually sour the investigation, since Profiler would predominate your processor time.

    I would recommend turning on Profiler, with a strictly limited filter and scope, that would show you the events, queries and procedures that take up your processor time AND that you can control, without unacceptably poisoning the results with too much Profiler CPU usage.

    • 0
  2. Best Answer

    Download and install the sp_whoisactive stored procedure written by Adam Machanic. It is one of the, if not the, best tool out there for ad-hoc monitoring of your SQL Server instance. It will show you how much CPU each session has consumed by either the currently running query (for that session) or over the lifetime of the session (for sleeping sessions). Run it a couple of times to get deltas to see which sessions are actually consuming the processor at that time.

    There are a ton of good posts on getting started with sp_whoisactive. Plugging it into Google and going down the list of first page results will get you a good start. Adam has a 30 days of monitoring series that takes you through all of the features. You will do yourself a great service by getting proficient with it.

    • 0