SQL SERVER – Sample Script to Check Index Fragmentation with RowCount

If you have ever worked with me on Comprehensive Database Performance Health Check, you may be familiar with my performance tuning techniques that I am NOT big on Index Fragmentation. I believe there are many different effective ways to tune your SQL Server and Index rebuilding is not in my list of top 10 activities. Today let us see a script which I have built for one of my clients to Check Index Fragmentation with RowCount.

During the consulting engagement, we go over many different activities which gives way more performance than Index Rebuilding. As a matter of fact, the index rebuild has some disadvantages, which we will discuss in future posts. No matter how much I explained one of my clients would not believe my scientific and systematic methods to help tune their SQL Server. They just wanted to start with the report of the index fragmentation and wanted to rebuild their indexes. After a brief failed attempt to explain to them the real way to tune the system, I finally gave in and send them the script which checks index Fragmentation with RowCount.

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.partition_number AS PartitionNumber,
       ips.page_count AS [PageCount], -- Only Available in DETAILED Mode
	   ips.record_count AS [RecordCount],
       ips.avg_fragmentation_in_percent AS AvgFragmentationInPercent
-- FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') ips
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED') ips -- QuickResult
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
-- AND ips.page_count >= 1000 -- Filter to check only table with over 1000 pages
-- AND ips.record_count >= 100 -- Filter to check only table with over 1000 rows
-- AND ips.database_id = DB_ID() -- Filter to check only current database
-- AND ips.avg_fragmentation_in_percent > 50 -- Filter to check over 50% indexes
ORDER BY DatabaseName

I am sharing the script as I use them in my consulting engagement and also with the additional filters in the WHERE condition. Please note that this script is for the all the databases on your server instance and if you are running a lot of databases on your system, the script will be very slow in that case, I suggest you start uncommenting each of the WHERE condition I have specified to get necessary results quicker.

Question: Can an Index Reduce the Performance of SELECT Query? There is a huge misconception that Indexes does not impact SELECT queries negatively.

Answer: Of course yes. Here is the blog post which I have written on this topic:  Can an Index Reduce Performance of SELECT Query? – Interview Question of the Week #234

Reference: Pinal Dave (https://blog.sqlauthority.com)