One of our clients contacted ABCloudz to request help with their SQL Server app’s performance issues. They upgraded from SQL Server 2008 R2 to SQL Server 2019 and began experiencing problems shortly after.

We analyzed their database system and discovered a complex issue with updating SQL Server statistics. These stats are used by the Query Optimizer to create an execution plan for each query. The problem was that the time to update statistics was taking much longer than the planned maintenance window for the production system.

The problem

The client lacked current statistics, which significantly degraded their app’s performance. As a result, the client had to refuse further statistics updates because they could cause similar performance issues. As an example, the update of statistics on their production database would take too much time.

The lack of regular statistical updates prevented SQL Server from assessing the latest data. This meant that the client’s Query Optimizer delivered execution plans based on outdated statistics and they couldn’t update such stats with newly inserted or modified data. The end result was a situation where the client faced the problem of multiple replication subscribers using the outdated statistics.

The solution

ABCloudz utilized industry best practices to help the client fix their issues. Specifically, we developed a custom process that separates statistics updates into groups. The groups differ by their table sizes and sample rates. This process also helped us minimize the update time for the production instance, as well as the replication subscribers.

Here are the basic steps we took to solve the issue:

  • Determined the appropriate sample rate for statistical updates in different tables.
  • Provided an opportunity to capture individual statistics before posting an update. To ensure this, we went to the Advanced Scripting Options page in SSMS and chose the most appropriate option for Script Statistics.
  • Built multiple groups for statistics updates, each having its own schedule.
  • Provided an option of rolling back to previous statistics in case of performance issues. Users can take old statistics that are scripted using SSMS and run the associated UPDATE or CREATE STATISTICS COMMANDS in the generated script for any objects that have regressed.
-- Example

CREATE STATISTICS [_WA_Sys_00000007_02FC7413] ON [dbo].[dimRules]([ChangeCategory]) WITH STATS_STREAM = 0x0100000001000000000000000000000043059C940000000091040000000000005104000000000000A7023F04A70000001E0000000000000008D0003400803F000700000034B8F4007DAA0000EF01000000000000EF0100000000000000000000D9899D3D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0000000C0000000100000010000000B3AB5C410080F7430000C040B3AB5C410000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000130000000000000000000000000000002702000000000000DD02000000000000E50200000000000060000000000000008500000000000000AA00000000000000CB00000000000000ED0000000000000015010000000000003C0100000000000071010000000000008A01000000000000AD01000000000000CE01000000000000FE01000000000000300010000000E841000000000000803F040000010025004265686176696F724368616E67653000100000007842000000000000803F04000001002500427265616B696E674368616E6765300010000000C040000000000000803F04000001002100446570726563617465643000100000003041000000000000803F04000001002200496E666F726D6174696F6E3000100000000040000000000000803F040000010028004D6967726174696F6E20426C6F636B65723000100000009842000000000000803F040000010027004D6967726174696F6E426C6F636B65723000100000000040000000000000803F040000010035004D6967726174696F6E426C6F636B65725F46616C7365506F7369746976653000100000000040000000000000803F040000010019004E413000100000005443000000000000803F040000010023004E6F742050726F76696465643000100000008841000000000000803F040000010021004E6F74446566696E65643000100000004041000000000000803F040000010030005061727469616C6C79537570706F72746564466561747572653000100000006842000000000000803F04000001002900556E737570706F7274656446656174757265FF01000000010000002200000008000000280000002800000000000000000000005A0000004265686176696F724368616E67657265616B696E674368616E6765446570726563617465644D6967726174696F6E426C6F636B65724E6F742050726F7669646564446566696E6564556E737570706F72746564466561747572650A0000004000000000C001000000820D010000040D0E0000810A1B00008610250000C0033500008E093800000207410000041248000000EF01000000000000
GO
  • Allowed roll-forward to statistics updates on a distinctive system. The principle is quite like the rollback scenario above. A user generates the statistics commands on the test system after tuning. After that, they run the statements against the production system.

We also captured metadata about statistics on a test server for large tables that included over 200 million rows storing over 100 GB in size. The captured statistics were replayed on the production system. This enabled us to update the statistics for all production system tables.

The production impact of updating the statistics with FULLSCAN was reduced from more than 10 hours to a few seconds. This delivered some great benefits to SQL Server Query Optimizer as it could produce better performing execution plans compared to working with outdated statistics.

Finally, we replayed the captured statistics on replication subscribers. This enabled us to reduce the overall I/O.

Now, the client can use new SQL Server features to manage statistics more effectively. With the 2014 version of SQL Server, there’s a method for using incremental statistics for a large partitioned table to avoid high I/O table scans while gathering statistics. With the 2016 release, SQL Server has a new dynamic threshold that can automatically update statistics. As a result, automatic statistics updates became more resilient. For tables with more than 500 rows, statistics will be updated more frequently.

Embrace our expertise

Questions? Ask ABCloudz database modernization specialists for help! We can handle the problem described in this case and many more. Ask us for a consultation to get the most relevant technical insights. Also, contact us to discuss your needs and start fixing your application performance issues now.

Ready to start the conversation?