Index related DMVs and DMFs

SQL Server 2005 introduced three new index-related dynamic management views and functions. All these views and functions are information rich and allowing to analysis of indexes. This

A look at avg_page_space_used_in_percent

This output column gives a clear indication of internal fragmentation of a particular level of an index. Internal fragmentation occurs when the number of pages allocated for the levels of an index are not fully used. Even though internal fragmentation has a minimal impact on the performance of seeking individual records, it may hinder the performance of ordered range scan since it has to read all the pages that belong to the index. Let’s do a small test by executing the below code; it creates a table, inserts records, and finally adds an index on the column Value.

-- query 1
IF OBJECT_ID(N'dbo.TestTable', 'U') IS NOT NULL

BEGIN

DROP TABLE dbo.TestTable

END

GO

CREATE TABLE TestTable (Id int IDENTITY(1,1) PRIMARY KEY, [Value] varchar(900))

GO

DECLARE @a INT, @b int

SET @a = 65

WHILE (@a < 91)

BEGIN

SET @b = 0

WHILE (@b < 20)

BEGIN

INSERT INTO TestTable ([Value])

SELECT REPLICATE(CHAR(@a), 445) + CONVERT(VARCHAR(10), @b)

SET @b = @b + 1

END

SET @a = @a + 2

END

GO

CREATE INDEX IX_TestTable ON dbo.TestTable([Value])

GO

If you query the table, the inserted records should look like below;

Note that the size of the column Value is 900 bytes and the size of the inserted values is around 450 bytes per record. The above batch inserts 260 records that makes the total size of the index approximately 450B x 260. Since an index page can hold only 8KB, an index page can hold approximately 17 of the above records, which ultimately that ends up with approximately 15 pages for the index.

Execute sys.dm_db_index_physical_stats function against this index.

-- query 2
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.TestTable', N'U'), 2, NULL, 'DETAILED')

Let’s have a look at avg_page_space_used_in_percent:

The index_level 0 represents the leaf level of the index and it has used 16 pages for holding index data. The column avg_page_space_used_in_percent says that pages in the leaf levels are almost full and the index is not internally fragmented. Now, let’s update the Value column by reducing the size of the index key.

-- query 3
UPDATE dbo.TestTable

SET [Value] = LEFT([Value], 1)

WHERE Id % 2 = 0

This reduces the size of 130 index keys; therefore the index should not need 16 pages to hold the data. Note that we do this purposely in order to see the fragmentation. You may not do the same in a production environment unless it is required. Now the size of the 130 keys is 130 bytes and to hold all index keys it needs approximately 57KB (8 pages). Let’s execute sys.dm_db_index_physical_stats again. You will see a result similar to the below table.

 Notice the value of avg_page_space_used_in_percent for index level 0 (i.e. leaf level) which shows that the average page space used is around 48%, which is certainly an inefficient use of space. Note that this is not applicable if the fill-factor is set for the index. A value below 75% indicates internal fragmentation. As mentioned above, it might slow down the reading of ordered range data from the Value column since SQL Server has to read through 16 pages of data, even though 16 pages are not required for holding them. You can run a SELECT statement for the Value column and see the number of pages read by looking at IO STATISTICS. Make sure that SQL Server is actually using the index for querying data, before checking the IO STATISTICS by enabling Include Actual Execution Plan.

We can fix this issue by either reorganizing or rebuilding the index. Reorganizing is recommended if the value is between 75 and 60. A rebuild is required if the fragmentation is below 60. So, in this case we will need to rebuild it.

-- query 4
ALTER INDEX IX_TestTable ON dbo.TestTable REBUILD

Execute the sys.dm_db_index_physical_stats function again and see the average page usage.

Rebuilding has done the job. Now the index uses only 8 pages for holding updated Values and almost all index pages are fully used. No internal fragmentation!