70-432 : Locks and Latchs (sys.dm_db_index_operational_stats)

This blog article is aimed at people preparing for 70-432 exam.

The MCTS 70-432 exams introduces some of the dynamic management views (DMVs). I like the ideas of DMVs, as a seasoned Oracle DBA I know many of the key Oracle Instance and Database views well. Once of the advantages of having a SQL view (as opposed to a GUI tool) is that he DMV query can be scheduled to run periodically and log key columns over time (very important for a DBA).

Starting with the msdn documentation for dm_db_index_operational_stats we can that it can capture leaf DML operations plus lock and latch waits:

You can use sys.dm_db_index_operational_stats to track the length of time that users must wait to read or write to a table, index, or partition, and identify the tables or indexes that are encountering significant I/O activity or hot spots.

Use the following columns to identify areas of contention.

To analyze a common access pattern to the table or index partition, use these columns:

• leaf_insert_count
• leaf_delete_count
• leaf_update_count
• leaf_ghost_count
• range_scan_count
• singleton_lookup_count

To identify latching and locking contention, use these columns:

• page_latch_wait_count and page_latch_wait_in_ms
  These columns indicate whether there is latch contention on the index or heap, and the significance of the contention.
• row_lock_count and page_lock_count
  These columns indicate how many times the Database Engine tried to acquire row and page locks.
• row_lock_wait_in_ms and page_lock_wait_in_ms
  These columns indicate whether there is lock contention on the index or heap, and the significance of the contention.

To analyze statistics of physical I/Os on an index or heap partition

• page_io_latch_wait_count and page_io_latch_wait_in_ms
  These columns indicate whether physical I/Os were issued to bring the index or heap pages into memory and how many I/Os were issued.

Column Remarks

The values in lob_orphan_create_count and lob_orphan_insert_count should always be equal.

The value in the columns lob_fetch_in_pages and lob_fetch_in_bytes can be greater than zero for nonclustered indexes that contain one or more LOB columns as included columns. For more information, see Index with Included Columns. Similarly, the value in the columns row_overflow_fetch_in_pages and row_overflow_fetch_in_bytes can be greater than 0 for nonclustered indexes if the index contains columns that can be pushed off-row. For more information, see Row-Overflow Data Exceeding 8 KB.

How the Counters Are Reset

The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, see sys.dm_db_index_usage_stats.

The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available. Dropping an index will cause the corresponding statistics to be removed from memory and no longer be reported by the function. Other DDL operations against the index may cause the value of the statistics to be reset to zero.

http://msdn.microsoft.com/en-us/library/ms174281(SQL.90).aspx

Next what is the difference between a lock and a latch? A lock (aka "enqueue") is a request for exclusive or shared owner of some data - a row,page or extent (a set of eight contiguous pages makes up an extent). Now for Oracle a latch is lock on an internal data structure in the SGA:

What is the difference between locks, latches, enqueues and semaphores?

A latch is an internal Oracle mechanism used to protect data structures in the SGA from simultaneous access. Atomic hardware instructions like TEST-AND-SET are used to implement latches. Latches are more restrictive than locks in that they are always exclusive. Latches are never queued, but will spin or sleep until they obtain a resource, or time out.

Enqueues and locks are different names for the same thing. Both support queuing and concurrency. They are queued and serviced in a first-in-first-out (FIFO) order.

Semaphores are an operating system facility used to control waiting. Semaphores are controlled by the following Unix parameters: semmni, semmns and semmsl. Typical settings are:

• semmns = sum of the "processes" parameter for each instance (see init.ora for each instance)
• semmni = number of instances running simultaneously;
• semmsl = semmns

http://www.orafaq.com/wiki/Oracle_database_Internals_FAQ#What_is_the_difference_between_locks.2C_latches.2C_enqueues_and_semaphores.3F

while for SQLServer a latch is often described as a "lightweight lock" and again is about protect internal database engine structures like buffers:

Tips for Using SQL Server Performance Monitor Counters

By : Brad McGehee Aug 24, 2005

A latch is in essence a "lightweight lock". From a technical perspective, a latch is a lightweight, short-term synchronization object (for those who like technical jargon). A latch acts like a lock, in that its purpose is to prevent data from changing unexpectedly. For example, when a row of data is being moved from the buffer to the SQL Server storage engine, a latch is used by SQL Server during this move (which is very quick indeed) to prevent the data in the row from being changed during this very short time period. This not only applies to rows of data, but to index information as well, as it is retrieved by SQL Server.

Just like a lock, a latch can prevent SQL Server from accessing rows in a database, which can hurt performance. Because of this, you want to minimize latch time.

SQL Server provides three different ways to measure latch activity. They include:

• Average Latch Wait Time (ms): The wait time (in milliseconds) for latch requests that have to wait. Note here that this is a measurement for only those latches whose requests had to wait. In many cases, there is no wait. So keep in mind that this figure only applies for those latches that had to wait, not all latches.
• Latch Waits/sec: This is the number of latch requests that could not be granted immediately. In other words, these are the amount of latches, in a one second period, that had to wait. So these are the latches measured by Average Latch Wait Time (ms).
• Total Latch Wait Time (ms): This is the total latch wait time (in milliseconds) for latch requests in the last second. In essence, this is the two above numbers multiplied appropriately for the most recent second.

When reading these figures, be sure you have read the scale on Performance Monitor correctly. The scale can change from counter to counter, and this is can be confusing if you don't compare apples to apples.

Based on my experience, the Average Latch Wait Time (ms) counter will remain fairly constant over time, while you may see huge fluctuations in the other two counters, depending on what SQL Server is doing.

http://www.sql-server-performance.com/tips/sql_server_performance_monitor_coutners_p3.aspx

To finish the following query looks useful, a good example of how to use the sys.dm_db_index_operational_stats view:

Tables where the most latch contention is occurring

select object_schema_name(ddios.object_id) + '.' + object_name(ddios.object_id) as objectName,
indexes.name, case when is_unique = 1 then 'UNIQUE ' else '' end + indexes.type_desc as index_type,
page_latch_wait_count , page_io_latch_wait_count
from sys.dm_db_index_operational_stats(db_id(),null,null,null) as ddios
join sys.indexes
on indexes.object_id = ddios.object_id
and indexes.index_id = ddios.index_id
order by page_latch_wait_count + page_io_latch_wait_count desc

http://sqlblog.com/blogs/louis_davidson/archive/2007/08/26/sys-dm-db-index-operational-stats.aspx