CODEWITHSUNDEEP
sqlsql-serverstatistics
Aki
Aki

Reputation: 626

Update statistics maintenance plan

I've got a very old and large database that hasn't been very well maintained over the past 8 years.

I'm no expert either but I've had to manage this over the years but not given it much time.

My question is, can a UPDATE STATISTICS really do such a great improvement to performance as I noticed one of my large queries on a table with over 128 million records went from over 1 min to run to 1 second. Why is that?

Secondly, the tables are already set to auto-update statistics but I noticed that over a few days, the database gets very slow and updating statistics makes things quicker again for a little time.

I done a UPDATE STATISTICS WITH FULLSCAN and it took about 45 minutes locally. Is that the same benefits as re-index?

I don't have any maintenance plans or scheduled jobs to update statistics on the whole database but was wondering if that's a good idea, and what is the best option for me.

Many thanks Aki

Upvotes: 0

Views: 433

Answers (1)

Jason Campbell
Jason Campbell

Reputation: 146

UPDATE STATISTICS helps the query optimizer make the best decisions on how to query your data. So yes, you definitely can get remarkable improvements when your Stats are up to date.

reindex will help with fragmentation so that's treating a different problem

Upvotes: 1

Related Questions