BOE (Back of Envelope) Method – Quick and Dirty Method for Finding High CPU Jobs

Situation: Your database server is sitting at 100%. There are not that many users logged into SAP doing work. Somehow, a bad plan is running and using all your resources on the computer. Problem is, you have no idea which spid(s) have the bad plan!

Within SQL Server it is extremely difficult to find which spid is using the most CPU. There are two methods for doing this:

- Using PERFMON, looking at the Thread Performance Object: % Processor Time, ID Process, ID Thread. Select ALL SQL threads (sqlservr/*). Then flipping it to “tabular” report format (ie. NOT the graph) and scrolling over 100 entries to find the thread that is using the most CPU time. Since a query can move from thread to thread, it’s a real tough time. Once you have the thread with the CPU, then get the ID Thread. The ID Thread is the KPID in sysprocesses. Join on this to get the SPID. Then DBCC INPUTBUFFER(spid) to see what the query is running. You can also KILL the spid, too. The ID Process is the NT PID.

select spid

from master..sysprocesses

where kpid =

go

DBCC INPUTBUFFER()

go

- As you can see, this is a difficult process. A quick and dirty method that I use that isn’t 100% accurate, but is about 75% correct is to run the following query. The long running jobs that this query reports tend to be the ones using the most CPU time.

CREATE proc boe_proc as

select datediff(mi, last_batch,getdate())'minutes',spid, waittype, cpu, physical_io, convert(char(15),hostname), convert(char(15),program_name), convert(char(20),getdate()),spid, last_batch, cmd

from master..sysprocesses

where spid > 50 and

cmd not like '%WAIT%' and

datediff(mi, last_batch,getdate()) > 1

order by last_batch

Here’s sample output, it shows the backup running and a batch job that has been going for 5 minutes app server MSSAP32 Batch Process (BTC) 20.

.

exec boe_proc

minutes spid waittype cpu physical_io spid last_batch cmd

----------- ------ -------- ----------- -------------------- --------------- --------------- -------------------- ------ ------------------------------------------------------ ----------------

389 150 0x0422 2424729 6929618 MSSAP01 R3D03(3) unc rd May 9 2002 6:05PM 150 2002-05-09 11:36:02.040 SELECT

65 1687 0x0000 2875 5756332 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1687 0x0000 63828 5756332 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1687 0x0000 62391 5756332 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1687 0x0000 59281 5756332 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1687 0x0000 29703 5756333 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1687 0x0000 27984 5756333 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1687 0x0000 29671 5756333 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1687 0x0000 33641 5756333 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1687 0x0000 30922 5756333 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1687 0x0000 30859 5756333 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1687 0x0000 28438 5756333 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1687 0x0000 30703 5756333 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1687 0x0000 28781 5756333 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1687 0x0000 28719 5756333 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1687 0x0000 29359 5756334 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1687 0x0000 30844 5756334 MSSAP99 SQLAgent - TSQL May 9 2002 6:05PM 1687 2002-05-09 17:00:03.267 BACKUP DATABASE

65 1467 0x0800 7467502 12792823 MSSAP32 R3B14(3) unc rd May 9 2002 6:05PM 1467 2002-05-09 17:00:28.953 SELECT

35 1333 0x0800 6006302 13123578 MSSAP31 R3B17(3) unc rd May 9 2002 6:05PM 1333 2002-05-09 17:30:26.043 SELECT

5 1503 0x0208 643300 6526323 MSSAP32 R3B20(3) unc rd May 9 2002 6:05PM 1503 2002-05-09 18:00:30.493 SELECT

5 1503 0x0404 8078 6526323 MSSAP32 R3B20(3) unc rd May 9 2002 6:05PM 1503 2002-05-09 18:00:30.493 SELECT

5 1503 0x0422 8031 6526323 MSSAP32 R3B20(3) unc rd May 9 2002 6:05PM 1503 2002-05-09 18:00:30.493 SELECT

5 1503 0x0404 8000 6526323 MSSAP32 R3B20(3) unc rd May 9 2002 6:05PM 1503 2002-05-09 18:00:30.493 SELECT

5 1503 0x0404 7453 6526323 MSSAP32 R3B20(3) unc rd May 9 2002 6:05PM 1503 2002-05-09 18:00:30.493 SELECT

5 1503 0x0404 7906 6526323 MSSAP32 R3B20(3) unc rd May 9 2002 6:05PM 1503 2002-05-09 18:00:30.493 SELECT

5 1503 0x0404 7750 6526323 MSSAP32 R3B20(3) unc rd May 9 2002 6:05PM 1503 2002-05-09 18:00:30.493 SELECT

(26 row(s) affected)