September 29, 2013 at 2:51 pm
Dear Experts
How to drop all auto sys statistics in the whole database
Thanks lot
September 29, 2013 at 3:05 pm
DROP STATISTICS <table name>.<statistics name>
Why do you want to do this?
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
September 29, 2013 at 3:11 pm
I have read an article about droping the old and reduntant stats , to keep only the used stats
copied
" This may sound like a radical idea at first, but think about it. What do you have to gain or lose? Dropping all auto stats will place some temporary stress on the system. As queries come in, the query optimizer will begin recreating those statistics that we just dropped. Every query that adheres to a certain pattern that requires a statistics to be created, will wait. Once. Soon, typically in a matter of minutes for highly utilized systems, most of the missing statistics will be already back in place and the temporary stress will be over. But now, only the ones that are really needed by the current workload will be re-created and all the redundant ones just came off the expensive maintenance tab."
" It’s a price you must pay to get better plans which means better performance "
do you have any advise
Thanks lot
September 29, 2013 at 3:27 pm
It's not going to get you better plans or better performance. If the stats you dropped weren't needed, they were never used. If they were needed, they'll be recreated. Stats that are unused can't, by definition affect plans (if they could, then they wouldn't be unused)
It'll reduce the time of any 'update all stats' maintenance jobs, but that's about it.
If you want to do it, be advised that you will get higher load and slower queries initially, so don't do it at peak time and don't do it repeatedly. Also note that the recreated stats will be sampled, which if you've been updating stats with fullscan could result in poor query plans until that update with fullscan kicks in.
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