I've been DBA on my first job outside of college for 3 months and have significant difficulty figuring out how index fragmentation on 2 of our production servers (2 separate clusters, but none) can be accurately queried and reported, which is not for this specific question important … just pretend I have 1 server).
We already use Ola Hallengren's scripts for index maintenance, but I need to query all our databases on our server and retrieve the% fragmentation along with the table name, index name, and database name.
Here is my current query that does not 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 if I remove the WHERE clauses, my data still seems to be wrong. One of my records / tuples shows a table in a database with a 1700-page index, but if I manually go to that index, does it have only 1? I worked for hours on it and can not seem to figure out what's going on. I have read the entire documentation for this DMV.