Waitstats performance metrics in SQL 2000 vs SQL Server 2005

By: Jeremy Kadlec | Read Comments | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More

Problem
From SQL Server 2000 to 2005, many of the core SQL Server system metrics have migrated from static commands to dynamic management views and functions that can be queried to gather statistics in real time.  The new dynamic management views and functions offer a great deal of flexibility to troubleshoot system issues, especially for WAITSTATS where the the number of milliseconds are captured for threads that are waiting on resources to complete their current batch or query.  This information is very valuable to gain an insight into how applications are using SQL Server and the cause and effect of waiting for resources (memory, CPU, disk, etc.).  Armed with this information, it should be possible to better tune your applications for greater levels of concurrency and throughput.

Solution
In SQL Server 2000, the command to capture the wait stats was DBCC SQLPERF('WAITSTATS'), although the SQL Server 2000 Books Online documentation always referenced 'LOGSPACE' to assess the current transaction log usage for all databases.  In SQL Server 2005, the corresponding dynamic management object is sys.dm_os_wait_stats. 

Let's take a look a the examples of each and how they compare.

Example - SQL Server 2000

Example - SQL Server 2005

What are the column mappings between the two commands?

What are the wait types that sys.dm_os_wait_stats captures?

• Latches and locks
• Broker
• Backups
• Transaction logs
• Pages
• Checkpoint and lazy writer
• For a complete listing of the 100+ SQL Server 2005 wait stats visit - sys.dm_os_wait_stats.

Next Steps

• As you begin to review your SQL Server 2000 monitoring scripts and begin to convert them to use the SQL Server 2005 objects and syntax, be sure to include wait stats in your analysis.
• The sys.dm_os_wait_stats DMV is valuable for assessing the performance issues related to a query or batch, so if you are experiencing these types of low level issues or need to pinpoint the problem, spending some time getting to know this table should pay high dividends.
• Click here for a complete listing of the dynamic management views and functions.

Related Tips: More | Become a paid author

Last Update: 8/2/2006

Share:

Share

Tweet

Connect with MSSQLTips.com Follow us on twitter... Let's connect on facebook! Join us on Google+

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Try the free performance monitoring tool from Idera! Write, edit, and explore SQL effortlessly with SQL Prompt. Need SQL Server help and not sure where to turn? Reach out to expert consultants for a Health Check. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Join the over million SQL Server Professionals who get their issues resolved daily. Learn SQL Server 2012, Performance Tuning, Development, Administration, Replication and more - free webcasts