October 29, 2010 at 9:14 pm
What is the benefit of updating stat after restoring the db to different server? Any particular reason to update the stat?
October 30, 2010 at 2:15 am
OK, now i know this is definitely related to your later question in this forum.
Firstly please do not double post, it clogs the forum and can prevent your questions from gaining well rounded answers!
Statistics are used by the Query optimiser to help ascertain the best route to retrieve data from a table(s) when a query is executed. They contain column information and data density in the pages. This link provides more info from Microsoft
For instance, a very large data load\delete could well benfefit from a stats update so that the QO is able to provide a solid query route through to the data!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 30, 2010 at 3:29 am
statistical values are held within the database and therefore within the backup so come across with the backup, therefore no need to rerun
EXCEPT
if you are restoring to a different server as part of an upgrade to a higher version of SQL, then the optimiser may use statistics differently so you should then update the stats
---------------------------------------------------------------------
October 30, 2010 at 10:07 am
Since the statistics are stored with the database (except in the case of an upgrade, as was mentioned), they will be moved with that database. This means, if the statistics are in good shape when the backup was taken, they'll be in good shape when the database is restored. If the statistics were out of date and problematic when the backup was taken, they'll be in that exact same circumstance when the database is restored. Same as with the data and the code and everything else in side the database. Restoring doesn't modify any of it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 1, 2011 at 12:35 pm
Recently ran into an issue where a database was restored to a different server and instance. Found that queries were returning differing results from the original. After running sp_updatestats found the query results then aligned. As this was between two sql 2000 instances using SQLLitespeed it would suggest that update stats would be a beneficial thing to do? Any downside to doing this (other than time)?
June 1, 2011 at 1:01 pm
Kevin Skinner (6/1/2011)
Recently ran into an issue where a database was restored to a different server and instance. Found that queries were returning differing results from the original. After running sp_updatestats found the query results then aligned. As this was between two sql 2000 instances using SQLLitespeed it would suggest that update stats would be a beneficial thing to do? Any downside to doing this (other than time)?
Just time, I/O, and CPU cycles. If you're running near the edge of CPUs on that instance you'll want to wait for a quieter time. It doesn't block or anything, though.
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
June 2, 2011 at 6:37 am
Kevin Skinner (6/1/2011)
Recently ran into an issue where a database was restored to a different server and instance. Found that queries were returning differing results from the original. After running sp_updatestats found the query results then aligned. As this was between two sql 2000 instances using SQLLitespeed it would suggest that update stats would be a beneficial thing to do? Any downside to doing this (other than time)?
same query, same data, returned different results?
sounds worrying. sub-optimal stats would not cause that problem.
---------------------------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply