{"id":1339,"date":"2012-05-21T00:00:00","date_gmt":"2012-05-21T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/a-first-look-at-sql-server-2012-availability-group-wait-statistics\/"},"modified":"2021-08-24T13:40:10","modified_gmt":"2021-08-24T13:40:10","slug":"a-first-look-at-sql-server-2012-availability-group-wait-statistics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/a-first-look-at-sql-server-2012-availability-group-wait-statistics\/","title":{"rendered":"A first look at SQL Server 2012 Availability Group Wait Statistics"},"content":{"rendered":"
\n

Now that SQL Server 2012 is released, we can expect to start seeing new and unfamiliar wait types appear in the output of sys.dm_os_wait_stats<\/b>, sys.dm_os_waiting_tasks<\/b> and extended event sessions. For those unfamiliar with wait types and wait statistics, the key concept to understand is that the SQL Server engine was designed to track areas where a task may need to wait for “something”. That “something” may be a resource, synchronization activity or queue. For example, a task may have to wait to acquire an Update lock on a data page because another task has already acquired it and is still holding the resource. When this occurs, that waiting task is moved off the scheduler and set to a waiting state along with an associated wait type, which in this example would be “LCK_M_U”. Once the resource becomes available, the task is signaled that the resource is available and the task then gets back in line on the scheduler. Once it has its scheduler time, it acquires the resource and continues with its work. A single task may encounter multiple waits during its lifetime, cycling repeatedly through a running, waiting and runnable state until complete.<\/p>\n

\n

Note<\/strong>: For an in depth overview of wait statistics, see Tom Davidson’s white paper, “SQL Server 2005 Waits and Queues<\/a>“. While this paper was written for SQL Server 2005, it is still very relevant to SQL Server 2012.<\/i><\/p>\n<\/div>\n

As of SQL Server 2012 CU1, SQL Server tracks 649 different wait types. Not all wait types are relevant – with many wait types representing the accumulation of normal background tasks. While some wait types can be ignored, other wait types should indeed be paid attention to in your overall performance troubleshooting efforts. It’s best to treat wait statistics as a pointer<\/i> for further research. Think of wait statistics as the initial symptoms that help you efficiently direct your next steps instead of blindly searching for possible culprits.<\/p>\n

Of the new wait types, there are at least 50 related to AlwaysOn Availability Groups. AlwaysOn Availability Groups are one of the more prominent new feature areas added to SQL Server 2012, adding new capabilities for both high availability and disaster recovery. If there was a performance issue related to availability groups, what wait types would we expect to see? For example, if transactions on the primary replica were slow to commit due to disk latency issues on the secondary synchronous replica, what wait type would accumulate? This article will explore the answers to this question.<\/p>\n

With so many variations and potential deployment configurations, consider this to be a first look, rather than a complete detailing of every possible pattern. For this article, I’m starting off with the following configuration:<\/p>\n