Why do i need to update stats after backup and restore?

Question

Why do i need to update stats after backup and restore?

curious_sqldba

SSC-Dedicated

Points: 36502
More actions
February 16, 2011 at 11:09 am

#232266

I copied a 400 gb db to another server and the queries are running terribly slow. I think i need to update stats, but why would i need to? I thought statistics are within the db. Why would it get stale?

Viewing 8 posts - 1 through 8 (of 8 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Most likely they were stale when you did the backup. To avoid things like this, do your reindexing/statistic updating before you do your backups.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

curious_sqldba SSC-Dedicated Points: 36502 More actions · Answer 2

Thanks. I ran trace with event "Peformace Statistics" and that was shown throughout the trace. Is that an indication of bad statistics?

curious_sqldba SSC-Dedicated Points: 36502 More actions · Answer 3

Craig Farrell (2/16/2011)
Most likely they were stale when you did the backup. To avoid things like this, do your reindexing/statistic updating before you do your backups.

Do i need to do this everytime i refresh from the production?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

No. Not unless you consistently have stale stats on the prod database. If you do, that's a far bigger problem than for refresh.

To clarify, there is no requirement to update stats after a backup/restore, unless that restore is to a higher version of SQL (2000 -> 2005)

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

curious_sqldba SSC-Dedicated Points: 36502 More actions · Answer 5

GilaMonster (3/9/2011)
No. Not unless you consistently have stale stats on the prod database. If you do, that's a far bigger problem than for refresh.
To clarify, there is no requirement to update stats after a backup/restore, unless that restore is to a higher version of SQL (2000 -> 2005)

Thanks Gail. Is there a way i can confirm that after restore my stats are good?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

If they were good before the backup, they will be good after the restore. As for are they good in general, well, that's harder to tell. If you're having intermittent poor query performance due to bad stats, I'd say no.

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

1) if restored-to server is upgraded version, MUST update ALL stats with FULLSCAN

2) Is restored-to server exactly as capable as original server, and without additional load on it?

3) have you done a waitstats and fileIO stall analysis to find out why things are slow?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service