Incremental statistics performance issues with many partitions

[mrscore]

Is there any way of speeding up the gathering of what partitioned stats needs updating?

Have one table with 2510 partitions, 13 indexes, 23 statistics items in total, so there's a whole lot of information to go thru partition by partition. The job updates a few the first 10-15 minutes (PARTITIONS(56) in the example below), but after that it gets quiet in the log file. After stopping the job, the log file got updated with a few more entries with high partition numbers, but wasn't finished still. Ran it manually as well for over 2.5h, and output was about the same as below with not much progress shown over the high partition numbers. Doing a full scan on everything in the table takes about 15-20 minutes.

... Date and time: 2018-09-27 02:16:02 [SQLSTATE 01000] Command: UPDATE STATISTICS [SDS].[VOUCHER].[TICKET] [_WA_Sys_00000022_106F0A8B] WITH RESAMPLE ON PARTITIONS(56) [SQLSTATE 01000] Comment: ObjectType: Table, IndexType: Column, Incremental: Y, RowCount: 173439, ModificationCounter: 16607 [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2018-09-27 02:16:02 [SQLSTATE 01000] Date and time: 2018-09-27 02:33:16 [SQLSTATE 01000] Command: UPDATE STATISTICS [SDS].[VOUCHER].[TRANS] [UC_TRANS] WITH RESAMPLE ON PARTITIONS(2510) [SQLSTATE 01000] Comment: ObjectType: Table, IndexType: Index, IndexType: Clustered, Incremental: Y, RowCount: 1412901, ModificationCounter: 56515 [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:01 [SQLSTATE 01000] Date and time: 2018-09-27 02:33:17 [SQLSTATE 01000] Date and time: 2018-09-27 02:50:17 [SQLSTATE 01000] Command: ALTER INDEX [IDX_GDATE_NSTID] ON [SDS].[VOUCHER].[TRANS] REBUILD PARTITION = 2510 WITH (SORT_IN_TEMPDB = ON, ONLINE = ON) [SQLSTATE 01000] Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 7812, Fragmentation: 12.8392 [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:06 [SQLSTATE 01000] Date and time: 2018-09-27 02:50:23 [SQLSTATE 01000] Date and time: 2018-09-27 02:50:23 [SQLSTATE 01000] Command: UPDATE STATISTICS [SDS].[VOUCHER].[TRANS] [IDX_GDATE_NSTID] WITH RESAMPLE ON PARTITIONS(2510) [SQLSTATE 01000] Comment: ObjectType: Table, IndexType: Index, IndexType: NonClustered, Incremental: Y, RowCount: 1415164, ModificationCounter: 54488 [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:01 [SQLSTATE 01000] Date and time: 2018-09-27 02:50:24 [SQLSTATE 01000] Date and time: 2018-09-27 03:12:31 [SQLSTATE 01000] Command: UPDATE STATISTICS [SDS].[VOUCHER].[TRANS] [IDX_VCHR_TRNS_1_RPT] WITH RESAMPLE ON PARTITIONS(2434) [SQLSTATE 01000] Comment: ObjectType: Table, IndexType: Index, IndexType: NonClustered, Incremental: Y, RowCount: 1123, ModificationCounter: 1 [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:01 [SQLSTATE 01000] Date and time: 2018-09-27 03:12:32 [SQLSTATE 01000]

Thanks, Jan Paija

[mrscore]

A full run took 6h 40m. Constant load of 17% CPU and a lot of reads in source db and tempdb activity. Looks like it was stuck looping sys.dm_db_incremental_stats_properties when checking with sp_whoisactive. (SELECT @ParamRowCount = [rows], @ParamModificationCounter = modification_counter FROM sys.dm_db_incremental_stats_properties (@ParamObjectID, @ParamStatisticsID) WHERE partition_number = @ParamPartitionNumber)

[mrscore]

Added an index mentioned in another thread here for @tmpIndexesStatistics to boost performance, and it helped a lot, but it's still very slow. The actual stat update of a partition only takes a second, but it can take several minutes to find next partition to update. 20m job execution now takes 2h with incremental on. Attached the execution plan where it seems to spend most of its time. Enormous amount of reads that's going on: dd hh:mm:ss.mss CPU reads writes physical_reads 00 00:01:43.570 95,574 32,627,374 12,038 25,663 Incremental_Stats.zip

[chf1971]

We were running into the same issue and found this thread when googling for the issue.

Even fetching the to-check indexes/statistics ("@CurrentCommand01") took about 20 minutes to complete.

This to-check indexes/statistics fetching delay, the delay caused by consulting "sys.dm_db_incremental_stats_properties" for each partition and a bug that the partitionnumber of the updated statistic (in case of incremental stats) is not logged in the CommandLog table, are all fixed in the attached Indexoptimize script. You can find the changed sections by searching for "Custom" in the script (the old sections are also still in place). (script is based on the IndexOptimize script of "2019-04-28 16:40:00")

IndexOptimize - optimised.zip

Quick tests that I have performed indicate that performance is now back at the same level as before (SQL2012).

@mrscore: can you check and verify that it is also working fine for your environment/database?

Cheers, Charles

[mrscore]

@chf1971, thank you so much! I just ran it in our lab and it cut down the runtime significantly! 40m instead of 2h 20m on one of our databases. In total just on that instance 1h 30m instead of 5h! This would just fly in production.

@olahallengren, any chance you could include this modification in the official release?

Thanks again Charles, really great work!

[chf1971]

@mrscore: you're welcome :-) Glad to be able to contribute something useful to Ola's toolbox ;-)

[JonasNjopOlsson]

@mrscore: you're welcome :-) Glad to be able to contribute something useful to Ola's toolbox ;-)

@chf1971: I have now added a pull request based on your code: https://github.com/olahallengren/sql-server-maintenance-solution/pull/296

Thank you for providing the code!

[mrscore]

@JonasNjopOlsson I've seen several updates released by Ola, but this part isn't implemented yet, right?

[JonasNjopOlsson]

@mrscore That is correct. The code for my pull request was last updated in January 2021 to merge well with the original source code and to be more in line with how Ola has structured the solution. We are using this code actively in various databases with incremental statistics with dramatic positive effects on performance.