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;

1	AAA……….0
2	AAA……….1
…	…….
20	AAA……….19
21	CCC………….0

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:

Index_level	Page_count	avg_page_space_used_in_percent
0	16	92.4280331109464
1	1	92.1793921423277

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.

Index_level	Page_count	avg_page_space_used_in_percent
0	16	47.7074128984433
1	1	92.1793921423277

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.

Index_level	Page_count	avg_page_space_used_in_percent
0	8	95.4395231035335
1	1	40.58561897702

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!

A look at avg_fragmentation_in_percent

The value of this column helps to determine the External Fragmentation of both indexes and tables. When the fragmentation at leaf level of an index occurs, it is known as Logical Fragmentation. When the heap of the table is fragmented, it is known as Extent Fragmentation.

Logical Fragmentation indicates the mismatch of logical order of the index pages against the physical order. For example if one of the pages in the index which is 100, indicates that the next page is 95, the 95^th page is logically out-of-order. But if the next page of the 100^th page is 105, then the 105^th page is considered in order. The pages that have been used for the index can be seen with the DBCC IND command. Let us test this with table we created. We shall start from the beginning.

Run queries 1 and 2. Check the column avg_fragmentation_in_percent in the result of query 2. This is what I got from the query:

Index_level	avg_fragmentation_in_percent
0	43.75
1	0

Normally, a value above 10 for avg_fragmentation­_in_percent in the leaf level of an index is considered logically fragmented. Run the below query and see how pages are logically ordered.

-- query 5
DBCC IND ('TestDatabase', 'TestTable', 2)

PagePID	Index_level	NextPagePID
….	….	….
185	0	194
194	0	119
119	0	127
….	….	….

The above table shows part of the result of DBCC IND. Note that you need to pass the database name as the first parameter and table name as the second parameter. The third parameter accepts values like -2 (for all IAMs), -1 (for all IAMs, data pages, and index pages), 0 (for all IAMs and data pages) or index id. Look at the 194^th page. The next page of 194^th is 119; hence 119^th page is logically out-of-order. Run query 4 again to rebuild the index. Check the results of query 2 and 5. Here is the result of mine.

Index_level	avg_fragmentation_in_percent
0	6.25
1	0

PagePID	Index_level	NextPagePID
….	….	….
197	0	198
198	0	199
199	0	200
….	….	….

See the results of DBCC IND. Pages in the index are reorganized and the logical order matches the physical order. Column avg_fragmentation­_in_percent shows the value as 6.25 which is a good indication that the index is not logically fragmented.

Is Internal Fragmentation always unhealthy?

No. There are situations where it comes in handy by avoiding External Fragmentation (or Logical Fragmentation). For example, if an index is Internally Fragmented, pages have room for new intermediate records; resulting in no page splits for new records that increases the number of pages out-of-order. Let us take a simple example. Run queries 1 and 4 again. Now run query 2. The result of my query 2 is like below:

Index_level	avg_fragmentation_in_percent	Page_count	avg_page_space_used_in_percent
0	6.25	16	92.4280331109464
1	0	1	92.1793921423277

Now run query 3 (Update query). Check the fragmentation again by running query 2:

Index_level	avg_fragmentation_in_percent	Page_count	avg_page_space_used_in_percent
0	6.25	16	47.7074128984433
1	0	1	92.1793921423277

Now the index is Internally Fragmented. Note that the values of index keys. They are something like; AAA…, CCC…, EEE. Assume that we need to insert a record such as BBB… which requires being set between AAA… and CCC… in the index. If the page that contains the last value of AAA (let’s say the page number is 100 and the next one is 101) has enough space, SQL Server does not need to split the page and move half of records to new page. If it has no space, splitting is required and half of the records in 100^th page will be moved to new pages (let’s say it is 120). This changes the logical order of pages. Now the logical order of the index is, 100->120->101 which indicates that the 101^st page is out-of-order. If this happens in large scale, you will see heavy logical fragmentation. But this can be avoided if pages have enough of space.

Run below query to insert some records. It inserts records like BBB, DDD.

-- query 6
DECLARE @a INT

SET @a = 66

WHILE (@a < 86)

BEGIN

INSERT INTO TestTable ([Value])

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

SET @a = @a + 2

END

GO

Check the fragmentation again by running query 2. You will see that still the avg_fragmentation_in_percent is 6.25. The index is therefore not Logically Fragmented because pages had enough spaces for new records. Let’s do the same, but before inserting new records, let’s rebuild the index by removing Internal Fragmentation.

Run queries 1 and 4 again. Now the values of avg_fragmentation­_in_percent and avg_page_space_used_in_percent are 6.25 and 92.4280331109464 respectively. Update the table by running query 3. Rebuild the index again by running query 4. Check the fragmentation again. It may show that the index is logically fragmented. In my result, it says the logical fragmentation is 87.5. Run query 5 to see page numbers. Check leaf level pages (Index level 0). In my case, 119^th page is out-of-order. Rebuild the index again by running query 4. Check the result with query 2 and 5. Now the logical fragmentation is 12.5 but leaf levels pages are not out-of-order.

Let us insert a record by running query 6. Note that the current internal fragmentation level is 95.4395231035335 which indicates no internal fragmentation, hence no space for intermediate records, it might require page split for new records that causes logical fragmentation. Run the query 6 and check with both queries 2 and 5. My result show that the index is logically fragmented. The value of the fragmentation is 93.3333333333333. If you check the result of query 5 (DBCC IND), you will see many out-of-order pages because of page splits. This proves that internal fragmentation helps to avoid logical fragmentation (but not always).

A look at fragment_count and avg_fragment_size_in_pages

These two columns show the number of fragments in the leaf level and average number of pages per fragment. A fragment represents a set of consecutive pages in the same file for an allocation unit (If you want to see page allocation with graphical interface, use a tool like SQL Internals Viewer: http://www.sqlinternalsviewer.com/ ). Because of that, there will be at least one fragment for an index. The maximum number of fragments that an index can have is equal to the number of pages in the leaf level of the index.

When the index has larger fragments, when scanning for the same number of pages, SQL Server can take the advantage of Read Ahead, hence less I/O. Again, when avg_fragment_size_in_pages is between 8 and 32, performance of the scanning operation is considered to be good.

However testing performance based on these two columns is a bit difficult. You may be testing with the support of STATISTICS IO (that gives the number of pages read from the disk and cache). Make sure that you clean the cache before testing by issuing DBCC DROPCLEANBUFFERS.

A look at ghost_record_count

The value of this column can be used to find the number of records that have been deleted but have not physically been removed. These records might introduce some performance problems because they reserve the space used for them holding without releasing the space for new records. Anyway, SQL Server removes deleted records physically in a timely manner, when it has enough resources for the operation.

Just to check this; run query 1 again and run the below query.

-- query 7
DELETE dbo.TestTable

WHERE Id % 2 = 0

It deletes 130 records. Now run query 2 and have a look at ghost_record_count. You may see the value of it as 130 unless SQL Server has not cleaned it up.

The version_ghost_record_count works similar to ghost_record_count but related to Snapshot Isolation (read more about Snapshot Isolation Level with http://www.sql-server-performance.com/articles/dba/isolation_levels_2005_p1.aspx ). The physical removal of these records is done after the transaction is either committed or rolled back.