In my TSQL2sDay index summary post, that I’d be writing a few posts on the information that is contained in sys.dm_db_index_operational_stats. The posts will be the following:
- Index Black Ops Part 1 – Locking and Blocking
- Index Black Ops Part 2 – Page IO Latch, Page Latch
- Index Black Ops Part 3 – Index Usage
- Index Black Ops Part 4 – Index Overhead and Maintenance
- Index Black Ops Part 5 – Page Splits
- Index Black Ops Part 6 – Fill Factor vs. Page Splits
I’m a bit behind – so without further ado on to part four – index overhead and maintenance. For the current post, we are going to look at the data that is contained in sys.dm_db_index_operational_stats that can be used to measure index overhead and maintenance.
Index Overhead
To measure overhead on an index we’ll be looking at the writes at the leaf and non-leaf levels for an index. These are measured with some of the same columns that were used in the last post, Index Black Ops Part 3 – Index Usage. These columns are:
- leaf_insert_count (bigint) – Cumulative count of leaf-level inserts.
- leaf_delete_count (bigint) – Cumulative count of leaf-level deletes.
- leaf_update_count (bigint) – Cumulative count of leaf-level updates.
- leaf_ghost_count (bigint) – Cumulative count of leaf-level rows that are marked as deleted, but not yet removed. These rows are removed by a cleanup thread at set intervals.
- nonleaf_insert_count (bigint) – Cumulative count of inserts above the leaf level.
- nonleaf_delete_count (bigint) – Cumulative count of deletes above the leaf level.
- nonleaf_update_count (bigint) - Cumulative count of updates above the leaf level.
For overhead, we want to consider the amount of writes that occur on an index to identify those that SQL Server is spending more time writing to. A high number of writes does not indicate a negative amount of overhead. It only informs on where the focus of write activity is occurring. To demonstrate these columns in action we’ll run the script listed below. This script will execute the following activity:
- Create a table named dbo.IndexMaintenance
- Insert 100,000 rows into dbo.IndexMaintenance
- Update 33,334 rows in dbo.IndexMaintenance
- Query sys.dm_db_index_operational_stats to demonstrate the number of write operations
- Query sys.dm_db_partition_stats to demonstrate the number of pages allocated to the index.
Here is the needed script:
USE tempdb;
GO
IF OBJECT_ID('dbo.IndexMaintenance') IS NOT NULL
DROP TABLE dbo.IndexMaintenance;
CREATE TABLE dbo.IndexMaintenance
(
ID int
,Value uniqueidentifier
,CreateDate datetime
,CONSTRAINT PK_IndexMaintenance PRIMARY KEY CLUSTERED (ID)
);
WITH
l0 AS (SELECT 0 AS C UNION ALL SELECT 0),
l1 AS (SELECT 0 AS C FROM L0 AS A CROSS JOIN L0 AS B),
l2 AS (SELECT 0 AS C FROM l1 AS A CROSS JOIN l1 AS B),
l3 AS (SELECT 0 AS C FROM l2 AS A CROSS JOIN l2 AS B),
l4 AS (SELECT 0 AS C FROM l3 AS A CROSS JOIN l3 AS B),
l5 AS (SELECT 0 AS C FROM l4 AS A CROSS JOIN l4 AS B),
nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM l5)
INSERT INTO dbo.IndexMaintenance
SELECT TOP (100000)
n, NEWID(), GETDATE()
FROM nums
ORDER BY n;
UPDATE dbo.IndexMaintenance
SET Value = NEWID()
WHERE ID%3=1;
SELECT leaf_insert_count + leaf_delete_count + leaf_update_count + leaf_ghost_count AS leaf_overhead
,nonleaf_insert_count + nonleaf_delete_count + nonleaf_update_count AS nonleaf_overhead
,leaf_allocation_count
,nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.IndexMaintenance'),NULL, NULL)
SELECT in_row_used_page_count
,in_row_reserved_page_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.IndexMaintenance')
If the script ran properly, you should have received the following output:
As the output shows, there were 133,334 writes at the leaf level on the index for dbo.IndexMaintenance. These writes were 100,000 inserts and 33,334 updates. At the non-leaf level where were 459 writes. These non-leaf writes corelate to the 459 leaf pages that were allocated for the index. Take those pages and add in the first page of the index and then the non-leaf page allocated for the index. With that you get the number of pages allocated in sys.dm_db_partition_stats.
The question is, of course, how does one use this information. The trouble here is that it depends. The index overhead is going to help highlight indexes that have a lot of write activity against them. Being that the operations happen at the row level, you can translate these operations to the number of rows affected.
We know from above that there were 133,334 writes happened on the index in the demonstration script. Is this bad? Or is this good? This is where the use of the index and the purpose of the database will be important information. When one index is updated more frequently than others it will be important to understand why. Is the index built on a volatile column that maybe should be indexed and referenced differently? Or is there a column included in the index that maybe should be removed to reduce write activity?
These and other questions will be at the purview of your database. The benefit of looking at index overhead is that these spot of high overhead can be deduced from the system.
Index Maintenance
Now that we’ve looked into overhead, we should also look at the maintenace that occurs on an index. There are times when gaps open up inside indexes during delete operations and whole pages of data are no longer populated with data. This type of activity is tracked through two more columns:
- leaf_page_merge_count (bigint) – Cumulative count of page merges at the leaf level.
- nonleaf_page_merge_count (bigint) – Cumulative count of page merges above the leaf level.
Let’s continue the demonstration above and execute the following statements against the table already created:
DELETE FROM dbo.IndexMaintenance
WHERE ID BETWEEN 10000 AND 90000
SELECT
leaf_insert_count + leaf_delete_count + leaf_update_count + leaf_ghost_count AS leaf_overhead
,nonleaf_insert_count + nonleaf_delete_count + nonleaf_update_count AS nonleaf_overhead
,leaf_allocation_count
,nonleaf_allocation_count
,leaf_page_merge_count
,nonleaf_page_merge_count
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.IndexMaintenance'),NULL, NULL)
SELECT in_row_used_page_count
,in_row_reserved_page_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.IndexMaintenance')
This script added executed the following actions:
- Deleted 80,000 rows from dbo.IndexMaintenance
- Query sys.dm_db_index_operational_stats to demonstrate the number of pages deallocated from the index.
- Query sys.dm_db_partition_stats to demonstrate the number of pages allocated to the index.
As a result of the script, the following query results should be displayed:
As you can see in the leaf_page_merge_count column there were 338 leaf pages in the index merged into other pages. This number does represent the difference in the number of pages allocated to the index but rather as I stated the operations that occurred merging the pages.
This information has been useful with my client in finding indexes where large numbers of rows are being removed from an index leading to deallocation of pages. After the deallocation, these pages may be reallocated as new rows for this or another index – and which will contribute to index fragmentation.
Index Read Overhead
In this last section, I would want to provide some secret sauce on a way to count all of the reads that have occurred on an index. Unfortunately at this time, SQL Server doesn’t have any DMVs, that I know of, that provide this information.
The benefit of this would be that with counts on both reads and writes on an index the true overhead of an index can be determined. For instance, if the database is writing to substantially more rows than are being read – the performance hit on the writes may outweigh the benefit of the performance improvement the provided to the reads.
This information would be extremely beneficial and so much so, that if you agree I’d like to have you vote it up on Microsoft Connect.
Related posts:
- Index Black Ops Part 3 – Index Usage
- Index Black Ops Part 2 – Page IO Latch, Page Latch
- Index Black Ops Part 1 – Locks and Blocking
