Creating useful statistics from _WA_Sys stats

  • Article

 

 

You probably well aware that keeping statistics up-to-date is essential for SQL Server performance. You may also well know that if statistics is missing  on the columns of the table SQL Server creates _WA_Sys* stats for you automatically to have something instead of nothing to help optimizer decide which plan to chose. You shall not be relying on this in practice and always strive to keep your stats in a good shape. Script below will help you to convert numerous _WA_Sys* stats on your server into more meaningfully named single column stats. 

 

SELECT

 

N'

DROP STATISTICS [' + schema_name(t.schema_id)+N'].['+ t.name + N'].[' + s.name + N']

GO

 

CREATE STATISTICS [' + c.name + N'] ON [' + schema_name(t.schema_id)+N'].['+t.name + N'] ([' + c.name + N']) WITH FULLSCAN

GO

 

'

FROM sys.stats s (NOLOCK)

INNER JOIN sys.stats_columns (NOLOCK)sic ON sic.stats_id=s.stats_id AND sic.object_id=s.object_id

INNER JOIN sys.tables t (NOLOCK) on t.object_id = s.object_id

INNER JOIN sys.columns c (NOLOCK) on c.column_id = sic.column_id and c.object_id = sic.object_id

WHERE s.name like '%_WA_Sys%'

ORDER BY t.name

 

GO