I remember writing an article about Query specific wait statistics being available inside execution plans in SQL Server 2016 SP1. The way that works is when you view the actual execution plan of a query (through SSMS or the XML) you are able to see the wait information that specific query ran into during execution. […]
SQL Server 2016 SP1 added loads of cool new features and options. One of those is a change that expands the amount of information that is stored inside an execution plan. Since I love using Wait Statistics as a way to identify performance bottlenecks, I was literally jumping through the room when I found out […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]