If you have ever worked with SQL Server, you may have come across the term “index fragmentation”. Index fragmentation occurs when the logical order of pages in an index does not match the physical order of pages on disk. This can lead to decreased performance and slower query execution times.

While index fragmentation is a common issue in SQL Server, there are different ways to address it. In this article, we will discuss a script that can help you check index fragmentation with row count.

During a consulting engagement, I had a client who was adamant about rebuilding their indexes as a performance tuning technique. Despite my attempts to explain the scientific and systematic methods of tuning a SQL Server, they insisted on starting with the index fragmentation report. To meet their requirements, I developed a script that checks index fragmentation with row count.

Here is the script:

SELECT DB_NAME(ips.database_id) AS DatabaseName,
       SCHEMA_NAME(ob.[schema_id]) SchemaNames,
       ob.[name] AS ObjectName,
       ix.[name] AS IndexName,
       ob.type_desc AS ObjectType,
       ix.type_desc AS IndexType,
       ips.page_count AS [PageCount],
       ips.record_count AS [RecordCount],
       ips.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes ix ON ips.[object_id] = ix.[object_id] 
				AND ips.index_id = ix.index_id
INNER JOIN sys.objects ob ON ix.[object_id] = ob.[object_id]
WHERE ob.[type] IN('U','V')
AND ob.is_ms_shipped = 0
AND ix.[type] IN(1,2,3,4)
AND ix.is_disabled = 0
AND ix.is_hypothetical = 0
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ips.index_level = 0
ORDER BY DatabaseName

Please note that this script is designed to work with all the databases on your SQL Server instance. If you have a large number of databases, the script may run slowly. To speed up the process, you can uncomment each of the WHERE conditions I have specified to filter the results.

It is important to understand that index rebuilding is not always the most effective way to improve performance. In fact, index rebuilding has some disadvantages which we will discuss in future posts. There are many other activities that can provide better performance gains than simply rebuilding indexes.

One common misconception is that indexes do not impact SELECT queries negatively. However, this is not true. Indexes can indeed reduce the performance of SELECT queries. To learn more about this topic, you can refer to my blog post: Can an Index Reduce Performance of SELECT Query?

Remember, when it comes to performance tuning in SQL Server, it is important to consider a holistic approach and not rely solely on index rebuilding. Use tools like the script mentioned in this article to identify index fragmentation and then evaluate the best course of action based on your specific scenario.

Stay tuned for more articles on SQL Server performance tuning techniques and best practices!