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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
Addition to what Remus has mentioned, I would suggest you read --
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.