When to update index statistics

Question

Post reply

When to update index statistics

srimkumarp

Old Hand

Points: 359
More actions
December 5, 2007 at 2:19 am

#377352

hi,
recently we moved entry logins and databases from old server 2005 to new server 2005. is it complusory to update statistics .if complusory,
When to update index statistics.(down time or peak hours).
can any one pls suggest the above one..
thanks
kumar

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

Chandra Sekhara Vyas Dhara

SSChampion

Points: 10207

December 5, 2007 at 2:22 am

#757256

Any time no problem. It will not effect the performance.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

It is strongly recommended to update all stats after moving a db from SQL 2000 to SQL 2005. 2005 keeps more detailed statistics than 2000 did. The 2005 optimiser can use the older stats, but not as optimally.

I wouldn't think it necessary when moving from one SQL 2005 box to another. The stats are stored within the database and get moved along with everything else

If you decide to update all the stats, I would suggest doing it during down time. Stats updates don't normally interfere with normal usage but they do require IO and CPU usage.

Something like this should work

exec sp_MSforeachtable 'update statistics ?'

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

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

updating stats can be intensive and cause problems - but it depends on your hardware,database and table sizes. If we're talking 50gb databases on reasonable raid with multiple cores ( assuming enterprise and parallelism hasn't been turned off ) then it's a very quick process - however a database of several terabytes with tables of hundreds of millions of rows is another matter.

Best is to try on a test server first, failing that out of hours first to see how long it takes. I'd suggest sp_updatestats which only updates those stats requiring update - so may be quicker. update statistics forces an update on all user and system stats on a table regardless.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

noeld SSC Guru Points: 96590 More actions · Answer 4

I do it with a "FULLSCAN" every time a I migrate a DB from 2k to 2k5

* Noel

EdVassie SSC Guru Points: 60472 More actions · Answer 5

Database statistics only need to be updated when the new values would be statistically different to the old values.

The statistics are used by the optimiser to determine the best access path. Updating a statistic on a 10m row table to let the optimiser know there are now an extra 100k rows is unlikely to change the access path. Updating a 10k row table to let the optimiser know there are now 110k rows is likely to give a different access path.

SQL Server 2005 uses a richer variety of statistics than SQL Server 2000, so moving a database from 2000 to 2005 should also be taken as a reminder to manually update statistics before letting users access the database. Do not rely on automatic statistics update when you move a database from 2000 to 2005, as it could be a long time before you get a consistantly optimal access path.

SQL Server has a database option 'automatically update statistics'. A number of people recommended turing this off with large databases in SQL Server 2000, because a user query could be held up while an automatic statistics update was done. In SQL Server 2005, there is an extra option 'asynchronously update statistics'. My advise is it is safe to have 'automatically update statistics' always set on in SQL Server 2005, but also always set on 'asynchronously update statistics'. This should minimise the maintenance you need to do while avoiding impacting users.

If you do decide to turn off automatic statistics update and instead manually update statistics in SQL Server 2005, it is best to schedule this during off-peak usage, to minimise the CPU and I-O overhead.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 6

Hi Folks

i seemed to have forgotten where the update statistics options are located in SSMS

can somone point me in the right direction as I know i have seen it somewhere

thanks

jim

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

It's convoluted.

Expand out object explorer, database - tables - statistics. Right click on the stats and select properties. Check the box that says update stats. Click ok.

Much faster (I think) to just type out

UPDATE STATISTICS <Table name>

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

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

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 8

Hi Gail

I am sorry but I just do not see that

I get as far as databases>(database name)>tables

i do not see statistics anywhere

Jim

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Might have been added in SQL 2008. You can use the script instead.

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

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

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 10

okay

but i could have sworn i saw it somewhere when i installed sql server 2005

thanks

jim

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 11

Hi gail

just an FYI

found this by Jeremy Kadlec:

http://www.mssqltips.com/tip.asp?tip=1056

nice picture of location under options of properties of the database

thanks

jim

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

That's not updating statistics, that's setting the database-level option 'auto-update statistics' to true or false.

If that's what you were looking for then I apologise, I misunderstood the question.

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

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

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 13

Hi Gail

sorry about that, but what you gave me was also helpful at the database level

Jim

Leo-696352 SSCrazy Points: 2708 More actions · Answer 14

Hi Gail,

I have migrated 2000 database to 2008 and update the statistics once after migrate. Do I need to update the statistics everytime I rebuild the index or how often I need to update the statistics?

Thanks.