• Home
  • Contact

John Sansom

SQL Server DBA Blog

  • About
    • The Blog
    • John Sansom
    • Contact
  • Ebook
  • Archives
    • Start Here
    • Popular Posts
    • All Posts
    • SFTW
  • Becoming a DBA
    • What it’s Really Like
    • Interview Tips
    • Certification
    • FAQ
  • Books
  • Resources
    • Blog Tools and Technology
    • UK Events Schedule
    • References & Resource Lists
  • Subscribe

SQL Snippet: SQL Server Wait Types

December 29, 2009 by John Sansom 5 Comments

Here is a handy little SQL Snippet that will return information about those all important SQL Server Wait Types for your server. It uses the SQL Server Dynamic Management View (DMV) sys.dm_os_wait_stats in order to extrapolate the desired information. A column has also been added to provide details of the percentage of total wait time that a particular Wait Type is responsible for.

T-SQL Query to rank SQL Server Wait Types by highest percentage of total wait time


SELECT
wait_type,
waiting_tasks_count,
max_wait_time_ms,
resource_wait_time_ms = (wait_time_ms - signal_wait_time_ms),
PercentOfAllResourceWaitTime =
(cast((wait_time_ms - signal_wait_time_ms) as decimal(19,2)) /
(select sum((wait_time_ms - signal_wait_time_ms)) from sys.dm_os_wait_stats))
* 100
FROM sys.dm_os_wait_stats
ORDER BY PercentOfAllResourceWaitTime DESC


I hope you find this SQL Snippet useful in your administration of SQL Server. If you have any queries regarding this snippet, SQL Server Wait Types or anything to do with SQL Server whatsoever then be sure to let me know!

Further Reading

  • SQL Server Best Practice Article: Performance Tuning Wait Queues
  • More DMV Queries: Identify All Active SQL Server Sessions
  • How to identify the most costly SQL Server queries using DMV’s

Filed Under: DMVs, SQL Server Tips, SQLServerCentral Syndication, SQLServerPedia Syndication Tagged With: DMV

About John Sansom

I’m a Microsoft Certified Master(MCM) of SQL Server. I’ve been working with database technology in a variety of flavors for over fifteen years. I absolutely love what I do and genuinely feel privileged to be a part of our tremendous technology community. Got a question about SQL Server or being a DBA? Ask me!

Popular Articles

  • Top 10 Free SQL Server Tools
  • Performing fast SQL Server delete operations
  • How to identify the most costly SQL Server queries using DMV’s
  • Top 10 Junior DBA Interview Tips
  • The Database Administrator’s Primary Responsibility
  • Your Road to Becoming a DBA: Laying a Strong Foundation
  • Top 5 SQL Forums
  • SQL Server Memory Configuration, Determining MemToLeave Settings
  • Script SQL Server Agent Jobs Using PowerShell
  • Using sys.dm_os_ring_buffers to Troubleshoot Connectivity Issues

Categories

  • Administration (38)
  • Blogging (8)
  • Customer Service (5)
  • Disaster Recovery (5)
  • DMVs (4)
  • Index Optimisation (6)
  • Interviews (1)
  • Link Posts (243)
  • Memory (2)
  • Performance Tuning (15)
  • Professional Development (70)
  • Reporting Services (5)
  • Reviews (1)
  • SQL Server Community (144)
  • SQL Server Tips (11)
  • SQLServerCentral Syndication (112)
  • SQLServerPedia Syndication (116)
  • Tools (7)

Copyright © 2023 · Santech Solutions Limited · Powered by the Genesis Framework · Privacy Policy