Session Based Wait Statistics in SQL Server 2016

As you probably know I am very fond of SQL Server Wait Statistics (shameless book link) and the options they provide for analyzing SQL Server performance. During SQL Server version releases new options became available to monitor and/or analyze Wait Statistics but one option for Wait Statistics analysis was still missing, the ability to monitor […]

Wait Statistics #4 LCK_M_xx

In the 4th article of the Wait Statistics series we will dive into LCK_M_xx waits. This is another wait type you are sure to see in your environment! LCK_M_xx waits As you can guess the LCK_M_xx waits have a relation with locking, they are generated when a query wants to place a lock on an […]

Examining OLEDB waits

On our adventure through the various types of wait statistics we end up at another popular wait type: OLEDB. In this article we will take a closer look at OLEDB waits to help you understand where they come from. OLEDB OLEDB or, Object Linking and Embedding DataBase, is a Microsoft COM interface to access data. Microsoft describes […]

Examining PAGEIOLATCH waits

In a previous article we looked at CXPACKET waits, a wait type that is can be on the top of your server’s list if you enabled parallelism using the MDOP setting. Another wait type that you probably will be seeing in your top 10 of waits (unless you have blazing fast storage) is PAGEIOLATCH_XX where the XX is the […]

Analyzing CXPACKET waits

One of the most common wait types you will see on a SQL Server is the CXPACKET wait type. This wait type is usually at the top of the list when you query the sys.dm_os_wait_stats on servers where you configured the MDOP setting to be anything but ‘1’. CXPACKET waits have everything to do with how your […]

How wait statistics are generated

In our last article “Why wait statistics are important and how you can start collecting them now!” we discussed a method of capturing wait statistics and a way to analyze them. Some background information on how wait statistics are generated by the SQLOS will certainly help you with your analysis! Schedulers The first important part […]