SQL Server – Index Operational Stats

Background

Taking a look at Indexes and the cost in terms of management and discovered a couple of things.

 

Glossary

There are a couple of Dynamic Management Views that Microsoft elegantly availed for monitoring Index Performance.

 

 

DMV Focus
sys.dm_db_index_operational_stats Returns current lowore-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.
sys.dm_db_index_physical_stats Returns size and fragmentation information for the data and indexes of the specified table or view in SQL Server.
sys.dm_db_index_usage_stats Returns counts of different types of index operations and the time each type of operation was last performed in SQL Server.

 

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
sys.dm_db_index_operational_stats.

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.

Using

The more interesting DMV for measuring low level IO cost appears to be sys.dm_db_index_operational_stats.

 

Code



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


Discovery

% leaf_allocation_count / leaf_insert_count

  1. 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
  2. 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.

Latch Wait

  1. Frequently used Objects will suffer the most from latch waits
  2. One should query sys.dm_os_latch_stats and see which latch_class is expending the most energy
  3. 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

 

Output:

LatchClass

 

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.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s