Ok, that’s not really the latest news and also well documented. But keep in mind that database snapshots also fall into the category of read-only databases, so you won’t see automatically maintained statistics for these as well.
I’ve seen the advice of taking a snapshot from a mirrored database for reporting purposes many times. Even books online contains a chapter Database Mirroring and Database Snapshots, where the scenario is explained.
The intention is to decouple your resource intensive reporting queries from the OLTP system which is generally a good idea. But keep in mind that:
- Reporting queries are highly unpredictable, and
- Reporting queries differ from OLTP queries.
So there’s more than only a slightly a chance that query performance will suffer from missing statistics in your reporting snapshot, since the underlying OLTP database simply does not contain all statistics (or indexes) that your reporting applications could take advantage of. These missing statistics can’t be added in the snapshot, because it’s a read-only database. Additionally, you won’t experience any automatic updates of stale statistics in the snapshot. And moreover, any added or updated statistics in the source database are not transferred into the snapshot, of course. This can affect your query performance significantly!
Have a look at the following example:
The script above creates a test database with one table and adds some rows to the table before creating a snapshot of this database. As we have never used column c2 in any query (besides the INSERT), there won’t be any statistics for column t1.c2.
Now let’s query the snapshot and have a look at the actual execution plan. Here’s the query:
-- Be sure to show the actual execution plan
Here’s the actual execution plan:
Clearly evident that the optimizer detects a missing statistics, although the options AUTO CREATE STATISTICS and AUTO UPDATE STATISTICS have both been set to ON. The plan reveals a noticeable difference between the actual and estimated number of rows and also a warning regarding the missing statistics.
So, keep that in mind when using snapshots for reporting applications.
If you’d like to find out more about SQL Server statistics, you may want to check out my series of two articles, published recently on the Simple-Talk platform.
It’s free, so you might want to give it a try. Feel free to vote, if you like it!