Performance

Question

Performance

Austin_123

SSCommitted

Points: 1648
More actions
December 13, 2016 at 11:38 pm

#401206

Hi All,
I have a question which needs your expert advice. Usually, we do rebuild and update stats when we perform database migration between SQL versions.
We have update stats run twice in a week on PROD databases. In our day to day operation If I restore a database from PROD to QA with same SQL edition and version then should we not required to run update stats and rebuild on restored database on QA?
Thanks in advance.
Regards,
Austin

Viewing 7 posts - 1 through 6 (of 6 total)

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

First: with a REBUILD of the indexes the stats are updated also. Thus it is not necessary te run a separate UPDATE STATS again.

In your described situation if you need to run a REBUILD indexes after the restore is depending on the time passed between the REBUILD on the productuion environment and the creation of the backup.

In any way it is far better to decide to REBUILD (or REORGANIZE) indexes on the amount of fragmentation. So run a periodically script to determine the fragmentation and execute the REORG/REBUILD depending on the results. The maintenance plan of Ola Hallengren is a widely accepted solution that includes this and you can find many more solutions with a simple internet search.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

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

Austin_123 (12/13/2016)
In our day to day operation If I restore a database from PROD to QA with same SQL edition and version then should we not required to run update stats and rebuild on restored database on QA?

No, you won't need to. The restored DB will have indexes and stats identical to the production DB at the time of backup.

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

Austin_123 SSCommitted Points: 1648 More actions · Answer 3

Thanks HanShi and Gail for reply.

I agree with you both, recently I had performance issue for the restored databases on QA (with same SQL edition and version ) and we have estimated there were lots of DML operations happened on PROD database (which is usual activity from app end) after weekly optimization and had un-synced the stats substantially.

We have created a daily update stats job with full scan for the objects getting used by app queries, this has resolved the issues. The queries which were taking more than 30 mins on PROD now they are taking 3 mins which was great improvement and our index reorg\rebuild is still same on weekly schedule which is based on percentage of fragmentation.

I got the answer of my specific question in your reply.

Thanks again.

kevaburg SSCoach Points: 18131 More actions · Answer 4

GilaMonster (12/14/2016)
Austin_123 (12/13/2016)
In our day to day operation If I restore a database from PROD to QA with same SQL edition and version then should we not required to run update stats and rebuild on restored database on QA?
No, you won't need to. The restored DB will have indexes and stats identical to the production DB at the time of backup.

Does the same apply if I Detach / Attach the database?

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

kevaburg (12/14/2016)
GilaMonster (12/14/2016)
Austin_123 (12/13/2016)
In our day to day operation If I restore a database from PROD to QA with same SQL edition and version then should we not required to run update stats and rebuild on restored database on QA?
No, you won't need to. The restored DB will have indexes and stats identical to the production DB at the time of backup.
Does the same apply if I Detach / Attach the database?

Yes.

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

HanShi (12/14/2016)
First: with a REBUILD of the indexes the stats are updated also. Thus it is not necessary te run a separate UPDATE STATS again.

Just an addition to this comment, with an index rebuild, only the statistics for the index being rebuilt are updated. Column statistics and statistics for other indexes are not updated.

If you're rebuilding all your indexes then that will still leave column statistics untouched; for them you would still need to do an UPDATE STATISTICS.

It's relatively uncommon for those to be as critical as index statistics, but for the cases where they are this is important to know.

Cheers!