By: Daniel Calbimonte | Updated: 2023-04-17 | Comments | Related: > SQL Server 2022
Problem
There is a new feature in SQL Server 2022 which is AUTO_DROP statistics. In this article, we will cover what this does and how to enable and disable it.
Solution
This tip will explain the new AUTO_DROP statistic feature and provide some examples. We will also show some common error messages and how to solve them.
Requirements
We will use the following for the examples in this article:
- Install SQL Server 2022
- Install the AdventureWorks database
- Install SSMS
What are SQL Server statistics?
Statistics are objects that contain information about the distribution of data values and SQL Server uses this information to determine how to generate execution plans. Having statistics updated can help performance of SQL Server queries. On the other hand, having statistics out of date can degrade SQL Server performance.
If you run a query, statistics are used to find the best execution plan. That is why having statistics updated is very important.
In SSMS, when you expand a table, you can see the Statistics folder and statistics for the table inside this folder.
We can also see the statistics using T-SQL. The following command shows how to check the statistics:
select * from sys.stats
The result:
There is a new column named AUTO_DROP in the sys.stats view if you use SQL Server 2022 or Azure SQL. AUTO_DROP is 0 (false) if the option is disabled and 1 (true) if the option is enabled.
What is the SQLL Server AUTO_DROP Setting?
The AUTO_DROP setting is new in SQL Server 2022 and also available in Azure SQL and Azure SQL Managed instances.
In earlier versions of SQL Server, statistics could block schema changes but this is not the case in SQL 2022. Let's take a look at an example.
CREATE STATISTICS [myPasswordStats] ON [Person].[Password] (BusinessEntityID, [PasswordHash], [PasswordSalt]) WITH AUTO_DROP = ON;
We create statistics named myPasswordStats on table Person.Password and include three columns: (BusinessEntityID, PasswordHash, PasswordSalt). Finally, we set the AUTO_DROP setting to ON.
We can now see the new statistic:
Right-click on the table, select the design option and delete the PasswordHash column.
Note, the statistics myPasswordStats was removed:
This happened because of the change we made to the structure with the column deletion and now the statistic is obsolete. That is why it is dropped automatically using the auto drop setting.
AUTO_DROP Setting Not Available Prior to SQL Server 2022
If we run the code in SQL Server 2019 or lower versions we will get an error:
CREATE STATISTICS [myPasswordStats] ON [Person].[Password] (BusinessEntityID, [PasswordHash], [PasswordSalt]) WITH AUTO_DROP = ON;
'AUTO_DROP' is not a recognized CREATE STATISTICS option.
The AUTO_DROP setting is not recognized in earlier versions. To check your SQL Server version, refer to this link: How to tell what SQL Server versions you are running.
Deleting Statistics Without AUTO_DROP Enabled
We will run the code without the AUTO_DROP option. The following code will create statistics in a SQL Server 2019 database.
CREATE STATISTICS [myPasswordStats] ON [Person].[Password] (BusinessEntityID, [PasswordHash], [PasswordSalt])
Now, we will try to delete the column:
The error message we get is:
The statistics 'myPasswordStats' is dependent on column 'PasswordHash'.
ALTER TABLE DROP COLUMN PasswordHash failed because one or more objects access this column.
There is a dependence on the column for the statistics that does not allow altering of the table.
Enable or Disable AUTO_DROP Setting
Turn Off
This example shows how to turn off the AUTO_DROP setting.
UPDATE STATISTICS [Person].[Password] [myPasswordStats] WITH AUTO_DROP = OFF;
In this example, [Person].[Password] is the table name, and myPasswordStats is the name of the statistics.
Turn On
This example shows how to turn on the AUTO_DROP setting.
UPDATE STATISTICS [Person].[Password] [myPasswordStats] WITH AUTO_DROP = ON;
Conclusion
SQL Server 2022 now has this new auto drop feature, so consider using this in future development.
Next Steps
To learn more about SQL Server 2022, refer to these links:
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-04-17