SQL 2016 Maintenance Plan Index Rebuild tool - Scan Type RRS feed

All replies

  • For more info, read the documentation for sys.dm_db_index_physical_stats. This choice determines whether the last parameter should be LIMITED (fast), DETAILED or SAMPLE.

    Limited (the default in the GUI as well as in the DMV) means that it determines frag by traversing the level *above* the leaf level. This is good because it if far less data to read.

    Detailed means that it reads the leaf. This is bad since it is way more data to read compared to above. Sp, why would you want to do that? Because detailed can tell you things that how full the pages are, on average. This is a useless setting in the maint plan, since this info isn't used. So, allowing us to specify detailed is a very bad design choice in the maint plan since it doesn't improve anything, it only makes the plan to be slower.

    Sample means that it samples. Gives the same info as detailed but it samples the number of rows (pages) to read. It is plausible that sample is quicker for tremenduously large indexes, but I doubt you will ever see any advantage using sample (compared to limited/fast).

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, April 9, 2019 12:40 PM
  • Thanks Tibor. You answered it.

    So when you manually run sys.dm_db_index_physical_stats to look at index frag, BOL indicates that it uses the B tree levels to do some of its work for indexes. So I take it that this is the same as limited?


    Tuesday, April 9, 2019 2:23 PM
  • Assuming that BOL is correct, then it should say that it uses the level above the leaf to get the value when you use LIMITED. And if you say DETAILED then it uses the leaf level.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, April 9, 2019 2:40 PM