Index related DMVs and DMFs

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.