Skip to main content
added 216 characters in body
Source Link
Kris Gruttemeyer
  • 3.8k
  • 2
  • 22
  • 43

I'd go with just updating stats.

I think Jeff Moden once said that "Rebuilding indexes is just an incredibly expensive way to update your stats".

Also, running UPDATE STATISTICS gets you both index and columns stats updates. Rebuilding indexes only does the index itself so the column stats are stale unless they hit the "20% data change + 500 records" criteria to trigger the auto-update. On large tables, that may be a while and not frequent enough.

SQL2014 and lower - Default sample size will not go parallel, only FULLSCAN will

SQL2016 - Default sample size can also go parallel (Parallel Statistics Update)

The syntax I usually use (In this case we do FULLSCAN [StatisticsSample=100]):

EXECUTE [dbo].[IndexOptimize] 
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@StatisticsSample=100

This way, you get all the benefit of updated statistics (which the optimizer will thank you for) without all the I/O thrashing and logging of physical index structure maintenance.

Additional reading on TF2371, which allows the stats update on large tables to be triggered at lower percentages. If you're on SQL 2016, this is enabled by default.

https://support.microsoft.com/en-us/help/2754171/controlling-autostat-auto-update-statistics-behavior-in-sql-server

I'd go with just updating stats.

I think Jeff Moden once said that "Rebuilding indexes is just an incredibly expensive way to update your stats".

Also, running UPDATE STATISTICS gets you both index and columns stats updates. Rebuilding indexes only does the index itself so the column stats are stale unless they hit the "20% data change + 500 records" criteria to trigger the auto-update. On large tables, that may be a while and not frequent enough.

The syntax I usually use (In this case we do FULLSCAN [StatisticsSample=100]):

EXECUTE [dbo].[IndexOptimize] 
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@StatisticsSample=100

This way, you get all the benefit of updated statistics (which the optimizer will thank you for) without all the I/O thrashing and logging of physical index structure maintenance.

Additional reading on TF2371, which allows the stats update on large tables to be triggered at lower percentages. If you're on SQL 2016, this is enabled by default.

https://support.microsoft.com/en-us/help/2754171/controlling-autostat-auto-update-statistics-behavior-in-sql-server

I'd go with just updating stats.

I think Jeff Moden once said that "Rebuilding indexes is just an incredibly expensive way to update your stats".

Also, running UPDATE STATISTICS gets you both index and columns stats updates. Rebuilding indexes only does the index itself so the column stats are stale unless they hit the "20% data change + 500 records" criteria to trigger the auto-update. On large tables, that may be a while and not frequent enough.

SQL2014 and lower - Default sample size will not go parallel, only FULLSCAN will

SQL2016 - Default sample size can also go parallel (Parallel Statistics Update)

The syntax I usually use (In this case we do FULLSCAN [StatisticsSample=100]):

EXECUTE [dbo].[IndexOptimize] 
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@StatisticsSample=100

This way, you get all the benefit of updated statistics (which the optimizer will thank you for) without all the I/O thrashing and logging of physical index structure maintenance.

Additional reading on TF2371, which allows the stats update on large tables to be triggered at lower percentages. If you're on SQL 2016, this is enabled by default.

https://support.microsoft.com/en-us/help/2754171/controlling-autostat-auto-update-statistics-behavior-in-sql-server

Source Link
Kris Gruttemeyer
  • 3.8k
  • 2
  • 22
  • 43

I'd go with just updating stats.

I think Jeff Moden once said that "Rebuilding indexes is just an incredibly expensive way to update your stats".

Also, running UPDATE STATISTICS gets you both index and columns stats updates. Rebuilding indexes only does the index itself so the column stats are stale unless they hit the "20% data change + 500 records" criteria to trigger the auto-update. On large tables, that may be a while and not frequent enough.

The syntax I usually use (In this case we do FULLSCAN [StatisticsSample=100]):

EXECUTE [dbo].[IndexOptimize] 
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@StatisticsSample=100

This way, you get all the benefit of updated statistics (which the optimizer will thank you for) without all the I/O thrashing and logging of physical index structure maintenance.

Additional reading on TF2371, which allows the stats update on large tables to be triggered at lower percentages. If you're on SQL 2016, this is enabled by default.

https://support.microsoft.com/en-us/help/2754171/controlling-autostat-auto-update-statistics-behavior-in-sql-server