August 11, 2013 at 12:25 pm
Is there a way (DMV or query) to pull unused _WA stats? _WA Stats maintenance is taking incredbily long time and I am trying to see if I can ignore _WA unused stats during the maintenace.
August 11, 2013 at 2:43 pm
This worked for me using SQL Server 2000, have not used in a later version.
Give it a try and then come back and post if it worked or did not work in 2005 so others may learn.
SELECT quotename(object_schema_name(OBJECT_ID)) + '.' + quotename(object_name (OJECT_ID)) as ObjectName
, quotename(name) AS statistics_name
, STATS_DATE(OBJECT_ID, stats_id) AS statistics_update_date
, *
FROM sys.stats
-- WHERE OBJECT_ID = OBJECT_ID('[dbo].[objects]')
order by statistics_update_date desc
, ObjectName ;
August 11, 2013 at 2:47 pm
Query runs fine. How do I filter the ones that haven't been used by query optimizer for certain days. I am interested only in _WA stats.
August 11, 2013 at 4:10 pm
Easy method - drop them all and see which ones SQL recreates. Those are the ones it thinks it needs
Accurate method - google for which traceflag turns on the optimiser information when a query is run and then run a full workload against the database, capture all the optimiser output, correlate it all (it's not returned in a grid) and then see which aren't used.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply