Indexes – Behind the Scenes

In my previous articles we see about index usage statistics and index physical statistics, have a look at missing indexes too

Now we have a clear picture on the below things,

• How our indexes are used ?
• Do we have healthy indexes ?
• Are there any missing indexes ?

Moving ahead, we would like to know what is going on behind the scene, with our indexes. Sometimes we may have healthy indexes, but still puzzled with performance. On such cases we may we get more details from the below DMV.

sys.dm_db_index_operational_stats.

This DMV will give us a crystal clear info about what is happening with our indexes on the operation. Even though the scope of this DMV is very wide, we will look into the below areas in this article.

• Capturing page io latch
• Capturing locking and blocking at the row level.
• Capturing latch waits
• Capturing lock escalation
• Indexes associated with contention

I don’t want my readers to go away from article to know about the terminologies i used in my article.

Latch:- are internal SQL Server locks which can be described as light-weight  and short term synchronization objects.  Latches are not primarily to protect the data to read/write from the disk into memory.  As i told they are synchronization objects for in memory access for any portion of data or logs.

Locks:- are for ensuring transnational integrity which you can control to a large extend (using transaction isolation levels, query and table hints etc). Locks primarily to protect the data to read/write from the disk into the memory. Locking allows to lock different types of resources to be locked by the transactions. Some example of resources are key, page, extent,tables,rows etc.. the SQL Server can obtain locks over this resources.

Latches are internal to the SQL engine and are used to provide memory consistency, whereas locks are used by SQL Server to provide logical transactional consistency.

view source

print?

select DB_name(op.database_id) as DBName,

OBJECT_NAME(op.object_id) as TableName,

sys.name as IndexName,

partition_number,

/*Capturing page io latch*/

page_io_latch_wait_count,

page_io_latch_wait_in_ms,

page_io_latch_wait_in_ms,

/*Indexes associated with contention*/

page_lock_count,

page_lock_wait_count,

page_lock_wait_in_ms,

/*Row locks*/

row_lock_count,

row_lock_wait_count,

row_lock_wait_in_ms,

/*Index lock escalation*/

index_lock_promotion_attempt_count,

index_lock_promotion_count,

leaf_allocation_count,

nonleaf_allocation_count

from sys.dm_db_index_operational_stats(DB_ID("AdventureWorks"),null,null,null) op

inner join sys.indexes sys on sys.object_id = op.object_id and sys.index_id = op.index_id

ORDER BY page_lock_wait_count DESC