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 & Non-Clustered Indexes AND s.name <> 'Audit' -- Exclude Audit Schemas AND ips.page_count > 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.