search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.91k stars 756 forks source link

Update Statistics very slow on a 3Tb database #798

Open angry-sqldba opened 8 months ago

angry-sqldba commented 8 months ago

Running update statistics on a 3Tb database is very slow

SQL server 2022 ENT Last version of the script Command: EXECUTE dbo.IndexOptimize @Databases = 'ABC', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = 'Index', @OnlyModifiedStatistics = 'Y', @LogToTable = 'Y', @MSShippedObjects = 'Y', @Execute = 'N'

angry-sqldba commented 8 months ago

running sp_updatestats takes just a few seconds (as the statistics are already up to date)

angry-sqldba commented 8 months ago

I have found the issue. commenting out the subquery related to the resumable index fixed the issue.

                                                `+ ',0 AS ResumableIndexOperation'`

                                               ` --+ ', ' + CASE WHEN @Version >= 14 THEN 'CASE WHEN EXISTS(SELECT * FROM sys.index_resumable_operations index_resumable_operations WHERE state_desc = ''PAUSED'' AND index_resumable_operations.object_id = indexes.object_id AND index_resumable_operations.index_id = indexes.index_id AND (index_resumable_operations.partition_number = partitions.partition_number OR index_resumable_operations.partition_number IS NULL)) THEN 1 ELSE 0 END' ELSE '0' END + ' AS ResumableIndexOperation'`
wqweto commented 8 months ago

Btw, can try dumping sys.index_resumable_operations into a temp #Tmp_index_resumable_operations table and base original query on it instead, to test if this speeds up performance on 3TB databases.