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
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.
GREATE! Thank you!