/* Database+Disk+Performance™: BOE (Back of Envelope) Method – Quick and Dirty Method for Finding High CPU Jobs */ Database+Disk+Performance™ /* */ /* -- -- */ /* Footer ----------------------------------------------- */ #footer { width:660px; clear:both; margin:0 auto; } #footer hr { display:none; } #footer p { margin:0; padding-top:15px; font:78%/1.6em "Trebuchet MS",Trebuchet,Verdana,Sans-serif; text-transform:uppercase; letter-spacing:.1em; }

Sunday, June 18, 2006

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)

3 Comments:

Blogger sexy said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,情趣,情趣,情趣,情趣,A片,視訊聊天室,聊天室,視訊,視訊聊天室,080苗栗人聊天室,上班族聊天室,成人聊天室,中部人聊天室,一夜情聊天室,情色聊天室,視訊交友網

免費A片,AV女優,美女視訊,情色交友,免費AV,色情網站,辣妹視訊,美女交友,色情影片,成人影片,成人網站,A片,H漫,18成人,成人圖片,成人漫畫,情色網,日本A片,免費A片下載,性愛

A片,色情,成人,做愛,情色文學,A片下載,色情遊戲,色情影片,色情聊天室,情色電影,免費視訊,免費視訊聊天,免費視訊聊天室,一葉情貼圖片區,情色,情色視訊,免費成人影片,視訊交友,視訊聊天,視訊聊天室,言情小說,愛情小說,AIO,AV片,A漫,avdvd,聊天室,自拍,情色論壇,視訊美女,AV成人網,色情A片,SEX,成人論壇

情趣用品,A片,免費A片,AV女優,美女視訊,情色交友,色情網站,免費AV,辣妹視訊,美女交友,色情影片,成人網站,H漫,18成人,成人圖片,成人漫畫,成人影片,情色網


情趣用品,A片,免費A片,日本A片,A片下載,線上A片,成人電影,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,微風成人區,成人文章,成人影城,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,臺灣情色網,色情,情色電影,色情遊戲,嘟嘟情人色網,麗的色遊戲,情色論壇,色情網站,一葉情貼圖片區,做愛,性愛,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,美女交友,做愛影片

av,情趣用品,a片,成人電影,微風成人,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,愛情公寓,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,aio,av女優,AV,免費A片,日本a片,美女視訊,辣妹視訊,聊天室,美女交友,成人光碟

情趣用品.A片,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,色情遊戲,色情網站,聊天室,ut聊天室,豆豆聊天室,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,免費A片,日本a片,a片下載,線上a片,av女優,av,成人電影,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,成人網站,自拍,尋夢園聊天室

2:42 AM  
Blogger freefun0616 said...

酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店小姐兼職,
便服酒店經紀,
酒店打工經紀,
制服酒店工作,
專業酒店經紀,
合法酒店經紀,
酒店暑假打工,
酒店寒假打工,
酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店小姐兼職,
便服酒店工作,
酒店打工經紀,
制服酒店經紀,
專業酒店經紀,
合法酒店經紀,
酒店暑假打工,
酒店寒假打工,
酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店小姐兼職,
便服酒店工作,
酒店打工經紀,
制服酒店經紀,
酒店經紀,

,酒店,

5:26 AM  
Blogger freefun0616 said...

酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店小姐兼職,
便服酒店經紀,
酒店打工經紀,
制服酒店工作,
專業酒店經紀,
合法酒店經紀,
酒店暑假打工,
酒店寒假打工,
酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店小姐兼職,
便服酒店工作,
酒店打工經紀,
制服酒店經紀,
專業酒店經紀,
合法酒店經紀,
酒店暑假打工,
酒店寒假打工,
酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店小姐兼職,
便服酒店工作,
酒店打工經紀,
制服酒店經紀,
酒店經紀,

,

1:15 PM  

Post a Comment

<< Home