As a SQL Server user, it is important to be able to identify resource bottlenecks in order to optimize performance. In this article, we will discuss a script that can help you identify these bottlenecks and improve the overall performance of your SQL Server.

The script provided below is compatible with earlier versions of SQL Server (2008, 2012, 2014) as well as SQL Server 2016. It has been widely used and proven to give accurate results across different versions.

SELECT wait_type AS Wait_Type, 
       wait_time_ms / 1000.0 AS Wait_Time_Seconds,
       waiting_tasks_count AS Waiting_Tasks_Count,
       wait_time_ms * 100.0 / SUM(wait_time_ms) OVER() AS Percentage_WaitTime
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN 
      (list of excluded wait types)
      AND wait_time_ms >= 1
ORDER BY Wait_Time_Seconds DESC

When you run this script, it will provide you with a list of resource bottlenecks in your SQL Server. The wait types that are excluded from the query are typically harmless or innocent wait types that do not indicate any performance issues.

It is important to note that this script is constantly being improved and updated to accommodate new wait types. If you come across any wait types that you believe should be added or removed from the exclusion list, you can provide feedback to the script’s author.

Resolving resource bottlenecks is crucial for optimizing SQL Server performance. By identifying and addressing these bottlenecks, you can ensure that your SQL Server is running efficiently and providing optimal performance for your applications.

For further assistance, you can send your SQL Wait Stats to the script’s author via email in an Excel file. They will provide you with quick feedback and recommendations to resolve any resource bottlenecks.

Make sure to bookmark this page as it contains valuable resources and links related to SQL Wait Stats. By regularly monitoring and addressing resource bottlenecks, you can ensure the smooth operation of your SQL Server.

Remember, the process of improving the script and identifying resource bottlenecks is an ongoing effort. Stay updated with the latest versions and continue to optimize your SQL Server for better performance.