Forum Discussion
Cleanup of auto created statistics
Hello everyone,
during annual database checkup i found that there were a lot of auto created statistics, which weren't used.
It seems that DBMS doesn't cleanup unused auto created statistic on his own.
i guess i can drop them with the following query:
DROP STATISTICS Schema.TableName._WA_Sys_XXXXXXXXXXXXXXXXXXXX
But i am still thinking whether it is safe, about impact on the performance.
Has anybody had any experience with that?
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
8 Replies
Hi
I am new to the forum and I would like to know how I can create a discussion on a SQL-Security topic
If anyone can show me that would be really nice.
Regards
Fred
Just click the button Start Discussion on the upper right corner of the space page of SQL Server Engine. After edit your question, choose tags like SECURITY and SQL you want under the editor.
thanks rodgerkong!!!!
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?
Dear rodgerkong
This select shows all auto created statistics:
SELECT
ObjectSchema = OBJECT_SCHEMA_NAME(s.object_id) COLLATE Latin1_General_CI_AI,
ObjectName = object_name(s.object_id) COLLATE Latin1_General_CI_AI,
StatsName = s.name COLLATE Latin1_General_CI_AI
INTO #t_stats_auto_created
FROM sys.stats s cross apply sys.dm_db_stats_properties(s.object_id,s.stats_id) sp
LEFT JOIN sys.indexes i on sp.object_id = i.object_id AND sp.stats_id = i.index_id
WHERE OBJECT_SCHEMA_NAME(s.object_id) != 'sys' AND s.name like'%WA%';
This one shows the statistics which are used in quries:
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
ObjectSchema = x1.statsinfo.value('@Schema', 'sysname') COLLATE Latin1_General_CI_AI,
ObjectName = x1.statsinfo.value('@Table', 'sysname') COLLATE Latin1_General_CI_AI,
StatsName = x1.statsinfo.value('@Statistics', 'sysname') COLLATE Latin1_General_CI_AI
INTO #t_stats_used
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY qp.query_plan.nodes('//OptimizerStatsUsage/StatisticsInfo') x1(statsinfo)
WHERE x1.statsinfo.value('@Statistics', 'sysname') like '%WA%'
ORDER BY ObjectSchema, ObjectName;
in the further steps you can create temp tables where you put the input and make except operation between them.
The output is going to be statistics which were auto created and didn't exist in query plans in query store.
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
