One of the customers contacted DB Best to help address performance issues with their SQL Server application. They started experiencing problems after upgrading from SQL Server 2008 R2 up to SQL Server 2016. However, after we analyzed their database system, we discovered a complex issue with updating SQL Server statistics. The Query Optimizer uses these statistics to create an execution plan for each query. Specifically, the time to update statistics was taking longer than the planned maintenance window for their production system.
The problem
After our customer upgraded their database to SQL Server 2016, the lack of current statistics severely degraded performance. Keeping in mind this poor experience, our customer refused further statistics updates to avoid similar performance issues. This was due to the long-time needed to update statistics on their production database.
Since the statistics were not regularly updated, SQL Server couldn’t assess the latest data. Thus, the Query Optimizer produced execution plans based on outdated statistics. Our customer could not update statistics with newly inserted or modified data. To make matters worse, there were multiple replication subscribers with outdated statistics as well.
The solution
We utilized industry best practices and internal expertise to correct issues. Particularly, we developed a custom process that allows for separating statistics updates into groups. These groups depend on the table sizes and sample rates. We also aimed to minimize the update time for the production instance and the replication subscribers.
To solve this issue we followed these steps:
- Determined proper sample rate for updating statistics for different tables.
- Created multiple groups for statistics updates with individual schedules.
- Allowed capture of individual statistics prior and post the update. The easiest way to do this is to Advanced Scripting Options page in SSMS and choose the appropriate option for Script Statistics.
- Allowed roll back to previous statistics in a case of performance issues. Basically, you can take the old statistics you scripted using SSMS and run the generate associated UPDATE or CREATE STATISTICS commands in the generated script for the objects that regressed.
/****** Object: Statistic [_WA_Sys_00000007_02FC7413] Script Date: 8/8/2019 3:24:35 PM ******/
CREATE STATISTICS [_WA_Sys_00000007_02FC7413] ON [dbo].[dimRules]([ChangeCategory]) WITH STATS_STREAM = 0x0100000001000000000000000000000043059C940000000091040000000000005104000000000000A7023F04A70000001E0000000000000008D0003400803F000700000034B8F4007DAA0000EF01000000000000EF0100000000000000000000D9899D3D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0000000C0000000100000010000000B3AB5C410080F7430000C040B3AB5C410000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000130000000000000000000000000000002702000000000000DD02000000000000E50200000000000060000000000000008500000000000000AA00000000000000CB00000000000000ED0000000000000015010000000000003C0100000000000071010000000000008A01000000000000AD01000000000000CE01000000000000FE01000000000000300010000000E841000000000000803F040000010025004265686176696F724368616E67653000100000007842000000000000803F04000001002500427265616B696E674368616E6765300010000000C040000000000000803F04000001002100446570726563617465643000100000003041000000000000803F04000001002200496E666F726D6174696F6E3000100000000040000000000000803F040000010028004D6967726174696F6E20426C6F636B65723000100000009842000000000000803F040000010027004D6967726174696F6E426C6F636B65723000100000000040000000000000803F040000010035004D6967726174696F6E426C6F636B65725F46616C7365506F7369746976653000100000000040000000000000803F040000010019004E413000100000005443000000000000803F040000010023004E6F742050726F76696465643000100000008841000000000000803F040000010021004E6F74446566696E65643000100000004041000000000000803F040000010030005061727469616C6C79537570706F72746564466561747572653000100000006842000000000000803F04000001002900556E737570706F7274656446656174757265FF01000000010000002200000008000000280000002800000000000000000000005A0000004265686176696F724368616E67657265616B696E674368616E6765446570726563617465644D6967726174696F6E426C6F636B65724E6F742050726F7669646564446566696E6564556E737570706F72746564466561747572650A0000004000000000C001000000820D010000040D0E0000810A1B00008610250000C0033500008E093800000207410000041248000000EF01000000000000
GO
- And finally, allowed roll-forward to statistics updated on a different system. This is handled simialr to the rollback scenario above. You generate the statistics commands on the test system after tuning, and then run the statements against your production system.
We performed statistics update with capturing metadata about statistics on a test server for the largest tables. These tables included over 200 million rows with over 100 GB data size. We replayed the captured statistics on the production system. Thus, we updated the statistics for all tables on the production system.
We reduced the production impact of updating statistics with FULLSCAN from 10+ hours to seconds. We agreed that the updated statistics reflect data that is 1-2 days old. SQL Server Query Optimizer benefited from updated statistics to produce better performing execution plans in comparison to statistics being outdated by 5-6 months.
We also managed to replay the captured statistics on replication subscribers and reduced overall I/O, yet updating statistics.
