Query/Performance Tuning using wait types

Question

Post reply

Query/Performance Tuning using wait types

Dean Jones-454305

SSCarpal Tunnel

Points: 4804
More actions
March 1, 2010 at 4:17 am

#139136

I have a question on using wait types to analyse performance issues, my query stems from the fact that within an OLTP
system that runs so many queries/transactions. I need to identify the specific wait type for a particular stored proc/transaction.
Is there a way to do this, I'm concerned that the scope of the wait type covers the entire DB server.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

there is the waittype column in sysprocesses or the dmv sys.dm_os_wait_stats

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

Shriji SSCommitted Points: 1881 More actions · Answer 2

Try this one

http://support.microsoft.com/kb/822101

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Have a look at Andrew Kelly's stuff. His blog is here: http://sqlblog.com/blogs/andrew_kelly/, I think he's written stuff for SQL Mag and other places.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Search web for this word doc: SQL Server 2005 Waits and Queues SQL Server Best Practices Article. It is the Bible for this topic. Also search for track_waitstats_2005

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service