Understanding the AUTO_DROP Option in SQL Server 2022

With the release of SQL Server 2022, Microsoft introduced several enhancements to improve database performance and manageability. One such feature is the AUTO_DROP option for statistics. In this blog post, we’ll explore what the AUTO DROP option is, how it works, and why it’s beneficial for database administrators and developers.

The Challenge with Manually Created Statistics

Before SQL Server 2022, manually created statistics could sometimes interfere with schema changes. If you or a third-party tool created statistics on a user database, those statistics objects might block modifications to the database schema. This was particularly problematic during operations like dropping or altering tables and columns, as the presence of these statistics could lead to errors or require additional steps to remove them manually.

Introducing the AUTO_DROP Option

SQL Server 2022 addresses this issue by introducing the AUTO_DROP property for statistics. When this option is enabled, statistics are created in a mode that allows them to be automatically dropped when they interfere with schema changes. This behavior is similar to how auto-created statistics operate.

Key Features:

• Automatic Cleanup: Statistics with AUTO_DROP enabled will not block schema changes. Instead, they are dropped as necessary.
• Consistency with Auto-Created Statistics: Manually created statistics with AUTO_DROP behave like auto-created statistics, providing a consistent experience.
• Default Setting: Starting with SQL Server 2022, AUTO_DROP is enabled by default on all new and migrated databases.

How to Use the AUTO_DROP Option

Creating Statistics with AUTO_DROP

When creating statistics manually, you can enable the AUTO_DROP option as follows:

 CREATE STATISTICS [YourStatisticsName] ON [Schema].[TableName]([Column1], [Column2]) WITH AUTO_DROP = ON; 

This command creates a statistics object that will automatically drop if it conflicts with future schema changes.

Updating Existing Statistics

You can also update the AUTO_DROP setting on existing statistics:

 UPDATE STATISTICS [Schema].[TableName] [YourStatisticsName] WITH AUTO_DROP = ON; 

Checking the AUTO_DROP Setting

To verify which statistics have the AUTO_DROP option enabled, you can query the sys.stats system view:

 SELECT object_id, [name], auto_drop FROM sys.stats WHERE auto_drop = 1; 

This query will list all statistics with AUTO_DROP enabled.

Best Practices and Considerations

• Auto-Created Statistics: Note that auto-created statistics always use AUTO_DROP. Attempting to change the AUTO_DROP setting on auto-created statistics may result in errors.
• Database Migration: When restoring or migrating databases from earlier versions to SQL Server 2022, it’s advisable to run sp_updatestats to ensure that statistics metadata is updated appropriately.
• Avoiding Errors: By using AUTO_DROP, you reduce the likelihood of encountering errors during schema modifications due to lingering statistics objects.

Conclusion

The AUTO_DROP option in SQL Server 2022 simplifies database schema management by ensuring that manually created statistics do not hinder schema changes. By enabling AUTO_DROP, you allow SQL Server to manage these statistics more effectively, leading to smoother deployments and fewer interruptions. You can connect with me on LinkedIn.

Reference: Pinal Dave (https://blog.sqlauthority.com)