olahallengren
issues/146 Add optimized handling of incremental statistics for partitioned databases
Solution overview
Fixes issue https://github.com/olahallengren/sql-server-maintenance-solution/issues/146
Based on code in issue #146 - credits to chf1971 (https://github.com/chf1971)
@JonasNjopOlsson : I see that I used a "CONCAT" at one point in the script. This would break when running on pre-SQL2012. Can you edit this line or even remove it? (it's a "print" at line 1800)
@JonasNjopOlsson : I see that I used a "CONCAT" at one point in the script. This would break when running on pre-SQL2012. Can you edit this line or even remove it? (it's a "print" at line 1800)
I removed the PRINT statements so the CONCAT was removed as well. You can check the diff of my commit in my pull request for any other version incompatibilities but I think it should be fine.
I removed the PRINT statements so the CONCAT was removed as well. You can check the diff of my commit in my pull request for any other version incompatibilities but I think it should be fine.
Just checked, looks fine! Thanks! /Charles
Any idea when this might be merged? I'm also running into performance issues with 200 partitions.
The pull request has now been updated to merge cleanly with the main branch.
commit https://github.com/samot1/sql-server-maintenance-solution/commit/741485d1c949fde17518658877df793dc0d7b76b and minor bugfix (missing space) at https://github.com/samot1/sql-server-maintenance-solution/commit/92ea7444d21364404a3e7b9fefa458bdf1460177
I made a new merge (to the version of 6th December 2020) and modified Jonas' changes a bit to be more in the style of Ola's script (using a @table variable instead of a #temptable, do no longer need additional variables, because I save the object / statistic id in the table itself)
Performance on my test server on a DWH-Database with several tables with > 800 partitions (@execute was set to 'N'): with the original script 3:26:58 with the modification 0:02:40 (factor 1:77)
@olahallengren it would be very nice, if you would implement this - it's a big difference if your nightly job runs 4 h (for all databases) or only 3 min for exact the same task....
I have now merged some of the changes from @samot1 into my branch. Thank you for this.
I have also rewritten the code to be even more in line with the @olahallengren solution, outputting statistics data into the new table variable @tmpStatisticsCache both for incremental and non-incremental statistics. This helps further streamline the code. This also has the advantage of placing the execution of the per-database SQL statement which retrieves the statistics data within the BEGIN TRY section in this part of the code so that the same error handling applies to all cases.
I have tested the code in the environments accessible to me and it seems to work well in this new revision as well.
This pull request has now been updated to include the latest changes as of today (2022-01-07) from the main branch and to merge cleanly with it.