Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

drop auto stats Expand / Collapse
Author
Message
Posted Sunday, September 29, 2013 2:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 12:23 PM
Points: 98, Visits: 275
Dear Experts

How to drop all auto sys statistics in the whole database

Thanks lot
Post #1499773
Posted Sunday, September 29, 2013 3:05 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 40,613, Visits: 37,077
DROP STATISTICS <table name>.<statistics name>

Why do you want to do this?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1499779
Posted Sunday, September 29, 2013 3:11 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 12:23 PM
Points: 98, Visits: 275
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
Post #1499780
Posted Sunday, September 29, 2013 3:27 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 40,613, Visits: 37,077
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1499784
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse