Taking a look at Indexes and the cost in terms of management and discovered a couple of things.
There are a couple of Dynamic Management Views that Microsoft elegantly availed for monitoring Index Performance.
In a nutshell, to gauge impact in terms of IO costs; that is Operational breakdown ( as Inserts, Deletes, Updates), Number and duration of Locks, Latch count and time expounded we will use need to hook into
On the other hand, to get size and fragmentation numbers we will access sys.dm_db_index_physical_stats.
In round up, we will dig into the sys.dm_db_index_usage_stats view to get usage stats; how many times the Index was updated as compared to how it was actually used during seeks and scans.
The more interesting DMV for measuring low level IO cost appears to be sys.dm_db_index_operational_stats.
use master go if object_id('dbo.sp_indexPageSplits') is null begin exec('create procedure [dbo].[sp_indexPageSplits] as select 1/0 as [shell] ') end go alter procedure [dbo].[sp_IndexPageSplits] ( @SchemaName sysname = NULL , @TableName sysname = NULL , @IndexName sysname = NULL , @dataspace sysname = NULL , @skipClusteredIndex bit = 1 , @orderBy sysname = 'leafAllocation%' ) as ;with cteIndexSize ( [object_id] , index_id , [RowCount] , [ReservedPageCount] , [SizeInMB] , [RowPerPage] ) as ( SELECT i.object_id , i.index_id , [RowCount] = sum(ps.row_count) , [ReservedPageCount] = sum(ps.reserved_page_count) , [ReservedPageCountInMB] = sum(ps.reserved_page_count * 8192) / 1024 / 1024 , [RowPerPage] = sum(ps.[row_count]) / sum(ps.[reserved_page_count] * 8192) FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id --INNER JOIN sys.partitions p INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.OBJECT_ID AND i.index_id = ps.index_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 group by i.object_id , i.index_id ) , cteIndexColumn ( [object_id] , [index_id] , [index] , [column_id] , [column] , [is_included_column] , [key_ordinal] , [index_column_id] ) as ( select [object_id] = tblI.[object_id] , [index_id] = tblI.index_id , [index] = tblI.name , [column_id] = tblIC.column_id , [column] = tblC.[name] , [is_included_column] = tblIC.is_included_column , [key_ordinal] = tblIC.key_ordinal , [index_column_id] = tblIC.index_column_id from sys.objects tblO inner join sys.indexes tblI ON tblO.object_id = tblI.object_id INNER JOIN SYS.index_columns tblIC ON tblI.object_id = tblIC.object_id AND tblI.index_id = tblIC.index_id INNER JOIN sys.columns tblC ON tblIC.object_id = tblC.object_id AND tblIC.column_id = tblC.column_id ) , IndexSummary ( [schema] , [Table Name] , [Index Name] , is_primary_key , is_unique_constraint , [IndexedColumnNames] , [IncludedColumnNames] , [object_id] , [index_id] ) AS ( SELECT DISTINCT [schema] = schema_name(tblO.schema_id) , tblO.name AS [Table Name] , tblI.name AS [Index Name] , tblI.is_primary_key , tblI.is_unique_constraint , SUBSTRING( ( SELECT ', ' + tblIC.[column] as [text()] FROM cteIndexColumn tblIC WHERE tblIC.object_id = tblI.object_id AND tblIC.index_id = tblI.index_id AND tblIC.is_included_column = 0 ORDER BY tblIC.key_ordinal FOR XML Path('') ), 2, 10000 ) AS [Indexed Column Names] , ISNULL(SUBSTRING ( ( SELECT ', ' + tblIC.[column] as [text()] FROM cteIndexColumn tblIC WHERE tblIC.object_id = tblI.object_id AND tblIC.index_id = tblI.index_id AND tblIC.is_included_column = 1 ORDER BY tblIC.key_ordinal , tblIC.index_column_id FOR XML Path('') ) , 2 , 10000 ), '' ) AS [Included Column Names] , tblI.object_id , tblI.index_id FROM sys.indexes tblI INNER JOIN SYS.index_columns tblIC ON tblI.index_id = tblIC.index_id AND tblI.object_id = tblIC.object_id INNER JOIN sys.objects tblO ON tblO.object_id = tblI.object_id WHERE tblO.[type] = 'U' ) Select objectname = object_schema_name(IOPS.object_id) + '.' + object_name(IOPS.object_id) , indexname = ind.name , ind.is_disabled , indexType = ind.type_desc , [fileGroup] = da.name , cteIS.[IndexedColumnNames] , [fillFactor] = ind.fill_factor , cteISize.[RowCount] , cteISize.[ReservedPageCount] , cteISize.[SizeInMB] , [leafInserts] = IOPS.leaf_insert_count , [leafWrites] = IOPS.leaf_insert_count + IOPS.leaf_update_count + IOPS.leaf_delete_count , [leafAllocationCount] = leaf_allocation_count , [leafAllocationInsert%] = cast ( (leaf_allocation_count * 100.00000) / NULLIF ( ( IOPS.leaf_insert_count ) , 0 ) as decimal(10, 5) ) , [leafAllocation%] = cast ( (leaf_allocation_count * 100.00000) / NULLIF ( ( IOPS.leaf_insert_count + IOPS.leaf_update_count + IOPS.leaf_delete_count ) , 0 ) as decimal(10, 5) ) , [waitInMS] = cast ( (row_lock_wait_in_ms + page_latch_wait_in_ms) as bigint ) , [waitInMinutes] = cast ( (row_lock_wait_in_ms + page_latch_wait_in_ms) * 1.0000 / ( 1000 * 60) as decimal(10, 5) ) , [lockWaitCount] = ( IOPS.row_lock_wait_count + IOPS.page_lock_wait_count ) , [latchWaitCount] = ( IOPS.page_latch_wait_count ) , [wait/ms] = cast ( ( IOPS.row_lock_wait_count + IOPS.page_lock_wait_count + IOPS.page_latch_wait_count ) / NULLIF ( ( ( row_lock_wait_in_ms + page_lock_wait_in_ms + page_latch_wait_in_ms ) * 1.0000 ) , 0 ) as decimal(10, 5) ) , [waitLatch/ms] = cast ( ( IOPS.page_latch_wait_count ) / NULLIF ( ( ( IOPS.page_latch_wait_in_ms ) * 1.0000 ) , 0 ) as decimal(10, 5) ) from sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) IOPS inner join sys.indexes ind on IOPS.[object_id] = ind.[object_id] and IOPS.[index_id] = ind.[index_id] inner join sys.objects tblO on ind.[object_id] = tblO.[object_id] inner join sys.schemas tblS on tblO.[schema_id] = tblS.[schema_id] left outer join sys.data_spaces da on ind.data_space_id = da.data_space_id inner join cteIndexSize cteISize on ind.[object_id] = cteISize.[object_id] and ind.[index_id] = cteISize.[index_id] inner join IndexSummary cteIS on ind.[object_id] = cteIS.[object_id] and ind.[index_id] = cteIS.[index_id] where objectproperty(IND.object_id,'IsUserTable') = 1 /* Filter out records that allocation count is zero */ and ( ( IOPS.leaf_allocation_count ) ) != 0 /* Filter out records that do not have changes */ and ( IOPS.leaf_insert_count + IOPS.leaf_update_count + IOPS.leaf_delete_count ) != 0 and tblS.[name] = case when @SchemaName is null then tblS.[name] when @SchemaName = '' then tblS.[name] else @SchemaName end and tblO.name = case when @TableName is null then tblO.name when @Tablename = '' then tblO.name else @TableName end and da.name = case when @dataspace is null then da.name when @dataspace = '' then da.name else @dataspace end and ( (@skipClusteredIndex = 0) or ( (@skipClusteredIndex = 1) and (ind.index_id != 1) ) ) order by case @orderBy when 'leafAllocation' then (leaf_allocation_count) when 'leafAllocationInsert%' then ( (leaf_allocation_count) * 100.0000 ) / NULLIF ( ( IOPS.leaf_insert_count ) , 0 ) when 'leafAllocation%' then ( (leaf_allocation_count) * 100.0000 ) / NULLIF ( ( IOPS.leaf_insert_count + IOPS.leaf_update_count + IOPS.leaf_delete_count ) , 0 ) when 'leafWritesCount' then ( IOPS.leaf_insert_count + IOPS.leaf_update_count + IOPS.leaf_delete_count ) when 'lockwaitCount' then ( IOPS.row_lock_wait_count + IOPS.page_lock_wait_count ) when 'latchwaitCount' then ( + IOPS.page_latch_wait_count ) when 'wait' then cast ( ( row_lock_wait_in_ms + page_lock_wait_in_ms + page_latch_wait_in_ms ) * 1.0000 / ( 1000 * 60) as decimal(10, 5) ) when 'waitLatch' then page_latch_wait_in_ms when 'lockWaitLatch/ms' then cast ( ( IOPS.page_latch_wait_count ) / NULLIF ( ( ( page_latch_wait_in_ms ) * 1.0000 ) , 0 ) as decimal(10, 5) ) when 'lockWait/ms' then cast ( ( IOPS.row_lock_wait_count + IOPS.page_lock_wait_count ) / NULLIF ( ( ( row_lock_wait_in_ms + page_latch_wait_in_ms ) * 1.0000 ) , 0 ) as decimal(10, 5) ) end desc go exec sys.sp_MS_marksystemobject '[dbo].[sp_IndexPageSplits]' go
% leaf_allocation_count / leaf_insert_count
- Clustered Index
- Correlation between the Number of Leaf Pages Allocated and Leaf Writes written, does not reveal page splits because data for entire row is written.
- That is, not just the Index Key Columns, but the entire column set
- Non-Clustered Index
- Non-Clustered Index impacted in cases where Clustered Key is relatively fat
- The reason is because Clustered Key is repeated as RID for each NC Index
Index Allocation Size
Obviously, low fill factor will cause more page allocation. And, one index will likely be chosen over another simply because a higher fill factor will make the other Index smaller and this more favorable.
Same goes for number of key columns defined.
- Frequently used Objects will suffer the most from latch waits
- One should query sys.dm_os_latch_stats and see which latch_class is expending the most energy
- The query below is an easy way to do so. Results should be correlated against overall Wait Stats
select DMOSLS.* , [%] = ( DMOSLS.waiting_requests_count * 100.00 / DMOSLS.wait_time_ms ) from sys.dm_os_latch_stats DMOSLS where DMOSLS.wait_time_ms != 0 order by (DMOSLS.waiting_requests_count * 100) / DMOSLS.wait_time_ms desc
Online Index Rebuild
Online Index rebuild because it creates an identical storage unit and keeps the current one online up to the last minute when metadata change needs to occur will also result in material Index Size relative to actual number of index rows.