Investigating SQL Server VDB Growth Due to Statistics Updates (KBA10772)

Issue

In the hours or days after a VDB is Provisioned or Refreshed, the database may show an unexpected growth in the size of space consumed on the Continuous Data Engine even when the workload is primarily read activity.

This might be visible from the Datasets screen (as Dataset Storage) and from the Storage Capacity Screen (as Current Copy Size).

In some cases, this issue may be caused by automatic statistics updates occurring on the Virtual Database (VDB), especially if the AUTO_UPDATE_STATISTICS parameter has been disabled on your Source Database.

The VDB Provision and Refresh process may cause the value of the AUTO_UPDATE_STATISTICS parameter to be reset to the default value for the instance. SQL Server may then begin to automatically recalculate statistics for tables as they are queried.

Note:

This is part of a series of knowledge base articles that describe causes of unexpected database growth. For an overview of other possible causes, please see Investigating SQL Server VDB Growth (KBA10774).

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Date	Release
Jan 25, 2024	19.0.0.0
Dec 20, 2023 | Jan 10, 2024	18.0.0.0 | 18.0.0.1
Nov 21, 2023	17.0.0.0
Oct 18, 2023	16.0.0.0
Sep 21, 2023	15.0.0.0
Aug 24, 2023	14.0.0.0
Jul 24, 2023	13.0.0.0
Jun 21, 2023	12.0.0.0
May 25, 2023	11.0.0.0
Apr 13, 2023	10.0.0.0 | 10.0.0.1
Mar 13, 2023 | Mar 20, 2023	9.0.0.0 | 9.0.0.1
Feb 13, 2023	8.0.0.0
Jan 12, 2023	7.0.0.0
Releases Prior to 2023
Major Release	All Sub Releases
6.0	6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1, 6.0.13.0, 6.0.13.1, 6.0.14.0, 6.0.15.0, 6.0.16.0, 6.0.17.0, 6.0.17.1, 6.0.17.2
5.3	5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0
5.2	5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1
5.1	5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0
5.0	5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1, 5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

Troubleshooting

To determine if unexpected growth is triggered by automatic statistics updates, it may be helpful to determine when the activity is being triggered. The Resources → Dataset Performance screen can be used to view datasets that have experienced a high read or write workload in the previous day.

In cases where the Automatic Statistics Update database behavior is likely to be the cause, the following behavior is normally seen:

• A large number of database writes are seen shortly after the database is provisioned, or as soon as a client connects and begins to use the database.
• The workload continues for several minutes or several hours, then stops.
• This workload is not seen again unless the VDB is Refreshed again.
• The AUTO_UPDATE_STATISTICS option of the Source database is False.
  
  

Resolution

During VDB Provision and Refresh operations, the AUTO_UPDATE_STATISTICS parameter is inherited from the model database of the Target SQL Server instance, and not the dSource.

This is described further in the document Inheritance of database properties during SQL Server VDB operations (KBA6278).

If you wish to ensure that database statistics are not automatically updated as tables are queried, consider one of the following resolutions:

• If the issue occurs on a single VDB that is rarely refreshed, you can manually change the Auto Update Statistics setting for the VDB via SQL Server Management Studio. It will be reset the next time the VDB is Refreshed.
   
• If the issue affects all databases on a SQL Server Instance, you can change the default AUTO_UPDATE_STATISTICS setting for the model database on that instance. This setting will automatically affect all new databases on that instance, including those Provisioned or Refreshed by the Continuous Data Engine.
   
• If the issue occurs for a single VDB that is frequently Refreshed, you can use a Hook Operation to automatically modify the AUTO_UPDATE_STATISTICS setting during VDB Provision and Refresh operations. An example hook script to modify this property is available in the document Inheritance of database properties during SQL Server VDB operations (KBA6278).