Are SQL Server statistics stored in database or buffer pool?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.

• Joy Walker: 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.