Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Just wondering are statistics kept in the database but not in the memory? If I backup/restore the database from a prod server to a development server, would it keep the same statistics so that execution plans won't be much different if I do it on the development server?

share|improve this question

3 Answers 3

The buffer pool is a cache of the database. There is never an 'or', things that are in the buffer pool are also in the database, always. And anything read from the database must be, even temporarily, present in the buffer pool.

As for the question: statistics are in the database so a backup/restore will preserve the statistics.

Note though that preserving statistics is not a guarantee to plan reproducibility. Other factors influence plan generation, like number of CPUs and amount of RAM.

share|improve this answer
    
ok, make sense. thanks for the clarification! –  Joy Walker May 8 '13 at 18:58

Addition to what Remus has mentioned, I would suggest you read --

  1. SQL Server Statistics Questions We Were Too Shy to Ask
  2. Aarons answer to - Where are Statistics physically stored in SQL Server?
  3. UNDERSTANDING SQL SERVER STATISTICS
share|improve this answer

As Remus said, table statistics are stored in the database similar to other objects like tables and indexes. They play a big role in selecting the execution plan, but there are other factors.

That being said, SQL Server knows another type of statistics, statistics that give us information about recent behavior. For example the DMVs sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats return statistics about how the index is used. Those statistics are stored in memory only. The get lost on server restart and do not travel with the backup. However, the optimizer does not use these to generate a plan either.

share|improve this answer
    
Yeah, this is why I had the question/confusion in the first place. I was thinking statistics are similar to DMVs. They could be gone when server restarts or db restored to another server. Thanks for making the point. –  Joy Walker May 10 '13 at 13:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.