As you might already know that I am a SQL Server Performance Tuning Expert who also acts as a “on-demand consultant” for many organizations across the world who have performance tuning or any other issue related to SQL Server. Once I was working with one of my clients and their DBA asked a question. In this blog, we would learn about possible ways to handle a situation where DBCC CHECKDB is indefinitely waiting for PREEMPTIVE_OS_DEVICEIOCONTROL.
Yesterday I posted a blog about a new DMV, which is introduced in SQL Server 2016. You can read the blog post here: Get Wait Stats Related to Specific Session ID With sys.dm_exec_session_wait_stats. After reading the blog post, I have received quite a few emails about SQL Server Wait Stats Collection Scripts. One of the question was is my earlier script still valid to collect the SQL Wait Stats for SQL Server 2016 and Later Versions.
First thing first, this feature of getting wait stats related to Specific Session ID with sys.dm_exec_session_wait_stats will only work with SQL Server 2016 or later version of SQL Server. If you attempt to run this on earlier versions of SQL Server, you will for sure get an error.
There are many wait types in SQL Server and not all of them are documented. Long ago I wrote blog on PREEMPTIVE and Non-PREEMPTIVE waits. SQL SERVER – PREEMPTIVE and Non-PREEMPTIVE – Wait Type – Day 19 of 28. In this blog post we will take a look at how to solve wait PREEMPTIVE_OLEDB_RELEASE.
NOTE: This script will just work fine with earlier version of SQL Server (2008, 2012, 2014) along with SQL Server 2016. It will give the expected results in all the SQL Server versions. This script will help you identify resource bottlenecks in your SQL Server.
A few years ago, I wrote the script which will help us identify SQL Wait Stats and Queues. It is a very popular script. You can find the link to the script over here: Introduction to Wait Stats and Wait Types. However, it has been a long time since I wrote that script and it was written for SQL Server 2008.