Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Troubleshooting performance procedure

Author  Topic 

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-12-17 : 19:09:10
Hi all,

A user emails you that he's suffering performance issues on a particular app/db. You go to Activity Monitor and see that indeed that CPU/Wait Time etc are inordinately high, and you want to see what sql is being run, and whether it is in the form of a Sproc, injected SQL or a scheduled job firing in response.

Where would you find this out?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-17 : 19:48:51
DBCC INPUTBUFFER(spidNoGoesHere), run a trace, or query the DMVs.

CPU being high can be okay in activity monitor as it's a cumulative number since that session has logged on. Wait time could be cumulative too, can't remember at the moment and not near BOL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-18 : 03:11:37
Wait times are cumulative for the current wait. When the thread stops waiting, it's set back to 0.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-12-18 : 07:50:42
quote:
Originally posted by tkizer

DBCC INPUTBUFFER(spidNoGoesHere), run a trace, or query the DMVs.

CPU being high can be okay in activity monitor as it's a cumulative number since that session has logged on. Wait time could be cumulative too, can't remember at the moment and not near BOL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



Thanks Tara. Am I right in thinking that the decision of which tool to use is something of a sliding scale between speed and detail, ie if you want a quick, broad idea you'd use the Input Buffer from the comfort of your own Management Studio, but if you want a thorough audit of the problem you'd use a trace?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-18 : 16:45:16
I don't use activity monitor much except to check blocking. I use traces for most of my performance troubleshooting. I also use the Missing Indexes DMV report, performance dashboard, and Wait Stats DMV report. If hardware is suspected, I add in PerfMon.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-12-18 : 20:17:03
quote:
Originally posted by tkizer

I don't use activity monitor much except to check blocking. I use traces for most of my performance troubleshooting. I also use the Missing Indexes DMV report, performance dashboard, and Wait Stats DMV report. If hardware is suspected, I add in PerfMon.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



Performance dashboard? Is that a DMV, or a specialist report in SSRS or something?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-19 : 00:04:28
It's a separate download from the SQL Server site on microsoft.com, have a look around. It gets loaded into Management Studio and provides some very cool performance things.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -