October 29, 2009 at 3:53 am
Hi,
When restoring a SQL 2005 database to another database server, the statistiscs of the source database server are not restored. Is there a way to also restore the statistics of the database?
Regards,
Meridium.
October 29, 2009 at 4:20 am
What type of restore are you using? Are you restoring parts of the database only? Also how did you determined that the statistics are not being restored?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 29, 2009 at 4:46 am
Hi,
I did a full backup and restore.
After the restore I did a exec sp_updatestats. Then I opened the SQL2005 report ‘Index Usage Statistics’ and did not see the statistics as on the database on the original location.
Regards,
Erik
October 29, 2009 at 5:41 am
At first I thought that you are talking about the tables’ and index’s statistics that are part of the database and therefore are being restored with the database. Now I think that you are talking about the statistics that the server keeps on the usage of indexes (the statistics that we can see with sys.dm_db_index* dynamic management views). If this is the case, then this is by design and there is no configuration option that you can use to modify this behavior.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 29, 2009 at 5:47 am
A restore recreates the database in exactly the same state it was at time of backup.
As for the index usage stats report, that sources off sys.dm_db_index_usage_stats. That DMV is memory only, is not persisted anywhere and is cleared when the DB is closed (detached, taken offline, restored over) and by a restart of the server. If you want to persist that info, you'll have to do it yourself.
p.s. the stats updates by sp_updatestats and the info show by the index usage DMV are completely unrelated.
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
November 3, 2009 at 5:05 am
Thanks for your replies.
Erik
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply