How to query/monitor index fragmentation? I have been a DBA for 3 months now at my first job out of college and I am having a significant amount of difficulty trying to figure out how to accurately query and report on index fragmentation across 2 of our production servers (2 separate clusters, but that doesn't matter for this specific question... let's just pretend I have 1 server).

We use Ola Hallengren's scripts for index maintenance already, but I need to query all of our databases on our server and get the fragmentation % along with the table name, index name, and database name.

Here is my current query that doesn't seem to be correct:

SELECT s.[name] +'.'+t.[name]  AS table_name  
 ,i.NAME AS index_name  
 ,dbd.name AS [database_name]  
 ,index_type_desc  
 ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent  
 ,page_count  
 FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'LIMITED') ips  
INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]  
INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]  
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id =   i.index_id)  
INNER JOIN sys.databases AS dbd on dbd.database_id = ips.database_id  
WHERE i.type IN (1,2) -- Include ONLY Clustered &amp; Non-Clustered Indexes  
AND s.name &lt;&gt; 'Audit' -- Exclude Audit Schemas  
AND ips.page_count &gt; 100 --Exclude small page counts  
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'   
AND ips.index_level = 0 -- Current level of the index. 0 for index leaf  
levels... Greater than 0 for nonleaf index levels.


Even when I remove the WHERE clauses it still seems my data is incorrect.  One of my records/tuples shows a table in a database with an index that has 1700 pages, but when I manually go to that index it only has 1?  I have been working on this for hours and can't seem to figure out what is going on.  I have read all of the documentation for this DMV.
Since you are using Ola's script - he has provided a script to check Index Fragmentation - see FAQ

In addition, fragmentation on very small tables does not affect performance. Microsoft has a white paper about this:
“Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages).”
By default, IndexOptimize does not reorganize or rebuild indexes with less than 1000 pages. If you want to change this setting, you can use the @MinNumberOfPages parameter.