March 4, 2009 at 9:38 pm
After you restore a prod database to a dev environment (sql 2005 servers) what do you consider mandatory to do next? For example, is it, in your opinion, absolutely necessary to rebuild all indexes on the new database? Mandatory to updates all statistics (FullScan) on all tables?
TIA,
Barkingdog
P.S. I'm asking this because I did just what I described and found that a simple query on the Dev box drove the CPU up to 95% -- plateaued -- for hours. Creating some new indexes and updating stats seemed to solve the problem. But this problem never surfaced on our production box which has the same database and similar hardware.
March 4, 2009 at 10:09 pm
The only thing that's truly mandatory when loading the database onto a different server is re-mapping and otherwise fixing up the logins/users.
I'm a bit lost as to why something would run for hours on a similarly-speced server but in a reasonable time on the source server. The only thing I can think of is different execution plans because the source server has data and/or an execution plan already in cache but the dev server has come up with a different execution plan, or has got really bogged down trying to compile one. So to start with I'd compare the execution plans for the query between the two servers (estimated execution plan is OK, but the acutal plan could be different so be patient and try to get that off the slow server if you can).
Also check the SQL Server parameters (sp_configure), version, etc. on the two to make sure they're the same.
March 4, 2009 at 11:05 pm
I do agree with Glenn only users need to be mapped elase do nothing.
When you will do reindexing and update statistics the environment will change compare to production and what development you are doing may give faster result in development due to reindexing and update statiscs but when you execute same on production may take longer time.
HTH,
Cheers!!!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 4, 2009 at 11:24 pm
HTH,
Your response hit on my confusion. If I restore a prod DB in a Dev environent and don't rebuild indexes or update staisticis in Dev shouldn't the DB's offer similiar performance (assuming the hardware is comparable)?
Barkingdog
March 5, 2009 at 9:33 am
after you rebuild index and update statistics(with full scan) go with DBCC FREEPROCCACHE,this will clear the cache plan.
March 5, 2009 at 11:04 am
I've seen that a couple of times after a restore to a new Dev box, but only when there are config differences between the dev and prod boxes.. I don't generally reindex or update stats after a dev restore. I would start with things like maxdop (because I know it's an issue for us and the settings that work are not default), memory settings, temp db setup, etc.
Meredith
March 5, 2009 at 11:55 am
Good replies but let me change the scenario a bit: suppose I am now upgrading a database from say sql 2005 to sql 2008. Other than using the upgrade wizard to look for issues and changing compatibility level, would you then automatically rebuild indexes\update statistics or something else to the newly upgraded database?
Bill
March 5, 2009 at 1:05 pm
Barkingdog (3/5/2009)
Good replies but let me change the scenario a bit: suppose I am now upgrading a database from say sql 2005 to sql 2008. Other than using the upgrade wizard to look for issues and changing compatibility level, would you then automatically rebuild indexes\update statistics or something else to the newly upgraded database?Bill
Honestly no (though it never hurts if you have the cycles to do it).
The indexes will not be any more fragmented and the statistics will not be further out of date just by restoring the database, even to the newer version. So it really should not be necessary. It never hurts if you have the time to do it but it should not help any more than if you were to reindex/update stats on the original system prior to backup.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply