Forum Discussion
Cleanup of auto created statistics
You can drop the WA_ statistics manually. When the Query Optimizer does not find statistic it needs, it will automatically create it.
But if the table is not modified frequancy, the statistic exists can be used by Query Optimizer without or with few updates. Comparatively, process of creating statistic will slowdown query. So if it's not very necessary, you'd better not to drop statistics manually. After SQL Server 2022, there is a option can drop statistics automatic.
Check the document https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16
Hello rodgerkong ,
thank You for sharing with this.
i have just checked all statistics and indeed all of them had auto drop set to 1.
But my impression is that during the time span nothing has changed significantly.
Maybe a few statistics were deleted for last scan, where only about 400 of almost 2000 are used, based on the query store.
So, this feature works, I guess only in case of "collision" lock SCH-M, and if I don't use them, the collision never happens and obsolete statistics still last in the db.
I think the one possibility is to check based on the query store which stats have never used and drop them manually, that's why I asked for.
So you have to drop statistics manually.
But I'm curious that, because I'm not familar with the query store, how to check it to identify which statistic was or was not used?