Checking index fragmentation in my database seems unruly slow. Regardless if I use the DMV sys.dm_db_index_physical_stats (for a specific database, table, or even index) or if I use the SSMS Index Properties window to look at Fragmentation on a specific index, it takes a really long time.

For example, using the Index Properties window will take upwards of 5 minutes to open up for a single index on my largest (~20 billion rows) table.

I do want to push to implement partitioning but until then I have to support an existing index maintenance job and I'm not sure how we can even check index fragmentation when one index on our heaviest table takes about 5 minutes to analyze. (Each of our tables has at least a few indexes.)

Here's a case where it took so long in the Index Properties window that I think it timed out and returned nothing in window: enter image description here


It looks like the other suggestions so far all lead back to sys.dm_db_index_physical_stats

This is where you will have to look to get the index stats, but you could look to use the LIMITED option and restrict it to specific indexes at a time.

With the size of your table, I would want to know how quickly fragmentation occurs to help me confirm that the indexing (particularly the clustered index) is appropriate.

How big is your maintenance window? Is it all consumed by the scanning part?

One other aspect to investigate (you didn't mention which version you were on) is RESUMABLE ONLINE REBUILDS. If you have the space then this might be an option to run as much as you can over multiple maintenance windows.

|improve this answer|||||
  • In general we don't really have a standard maintenance window but we make an exception for this case because we're just doing index reorganizing at the moment. Our maintenance window is 3.5 hours for this operation and was normally failing (being killed) once it tried to defrag our largest table (10s of billions of rows). We were only using 5% as our fragmentation threshold so I raised that to 10% and so far our largest table isn't there yet. It's only been a day since I made the change and am going to see how it's fragmentation changes over the next week. We're on Enterprise Edition. – J.D. Feb 2 at 6:27
  • 1
    I'm doing a similar process with several thousand databases - too many to all run in a single window so tracking fragmentation over time to identify which indexes need more frequent attention and which can be pushed out less frequently. – Martin Cairney Feb 3 at 2:43
  • 1
    You could try to have a maintenance plan that allows to resume in case it got interrupted in the middle of its work. Also consider rebuilding when fragmentation is over 30% : docs.microsoft.com/en-us/sql/relational-databases/… – Hybris95 Feb 3 at 14:07
  • @Hybris95 Could rebuilding over 30% be overall quicker than reorganizing at 5%? – J.D. Feb 3 at 23:35
  • The objective is to reduce the fragmentation as much as possible. So this is usually not a question asked, we usually see the state of the indexes and apply the best method considering their fragmentations. Frequency of the maintenance should only depend on how often maintenance windows are available. – Hybris95 Feb 4 at 9:50

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