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]  
 ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent  
 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.

  • Since you have an answer you may enjoy this video and the link below it. I’d reindex still, but I’d be sure I was focused on backups, corruption, and performance gotchas like maxdop, tempdb, etc. check out Ozars first responder kit, Adam Mechanic sp_WhoIsActive, and keep active here! Welcome!
    – S3S
    Aug 24, 2019 at 2:37
  • Already seen that video! As a matter of fact, I think I have seen all of his videos 3 times now.. lol. I have live training with him 2 weeks from now though!
    – Data Dill
    Aug 25, 2019 at 13:02

1 Answer 1


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.

  • ahhhhh I was trying to decode his maintenance script for the longest time!! THANK YOU so much! If I wanted to loop through all of my databases on the server what is the best performing method? I could query sys.databases into a temp table and then loop through each ID with a cursor? I know cursors are no bueno, but seeing as these are sys tables it shouldn't be an issue and my company has the hardware so CPU isn't a problem. My end goal is to use a powershell script to gather this data into a central CMS to report on btw.
    – Data Dill
    Aug 23, 2019 at 21:08
  • 3
    @Dylan How you iterate through the databases is largely irrelevant in terms of performance. You're still going to go off and do things in each database that take a lot longer than the iteration of the loop, doesn't matter if it's a cursor or while loop. What exactly do you need to do in a loop though? You should be able to just turn Ola's stuff on and stop thinking about it. Aug 23, 2019 at 21:32
  • Fyi .. Ola has parallel option as well. the script uses queue table for parallel operations.
    – Kin Shah
    Aug 24, 2019 at 4:03
  • He uses a parameter @DatabaseID to pass into as one of the arguments for querying sys.dm_db_index_physical_stats. I have 2 separate (not clustered) servers I need to run this against for ALL databases on each server. The powershell script to query this against both servers is easy, but what is the best way to query for all databases on a given server? In other situation I queried the sys.databases (object_id) into a temp table and looped through that with a cursor.
    – Data Dill
    Aug 25, 2019 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.