« Did you know: Aggregate functions on floats may be non-deterministic
How to treat your MDF and LDF files »

No automatically created or updated statistics for read-only databases

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:

  1. Reporting queries are highly unpredictable, and
  2. 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:

use master
go

-- Create test database
create database ssBase
go
use ssBase
go
-- create test table and insert 10000 rows
create table t1
(
c1 int not null primary key
,c2 int not null
)
go
go
insert t1(c1,c2)
select top 10000
row_number() over(order by current_timestamp)
,checksum(newid())
from sys.fulltext_system_stopwords
go

-- create snapshot of test database
-- NOTE: the filename has to be adjusted
-- for your own experimenting
create database ssBase_SS on
(
name = ssBase
,filename = 'e:\SqlTestData\ssBase_SS.ss'
)
as snapshot of ssBase;
go

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
-- for the following query
use ssBase_SS

select * from t1
where c2 = 10

Here's the actual execution plan:

image

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.

Part 1: Queries, Damned Queries and Statistics
Part 2: SQL Server Statistics: Problems and Solutions

It's free, so you might want to give it a try. Feel free to vote, if you like it!

SQL Server Performance
 Print article This entry was posted by Holger Schmeling on 01.11.10 at 14:37:42 . Follow any responses to this post through RSS 2.0.