Blog Post

Statistics Enhancements in SQL Server 2012

,

Today I want to talk about Statistics Enhancements in SQL Server 2012. As you already

know SQL Server uses Statistic Objects in a database to estimate the cardinality of

queries. The cardinality estimation is the input for the generation of the physical

Execution Plan of a given T-SQL query. Data changes over time, data distribution changes

over time, so SQL Server is also able to change Statistics Objects in the background

automatically for us. This feature is called Auto Update Statistics.

SQL Server updates Statistics automatically under the following conditions (when the

table is not a temp table):

  • A table with no rows gets a row
  • A table has fewer than 500 rows and is increased by 500 or more rows
  • A table has more than 500 rows and is increased by 500 rows + 20 percent of the number

    of rows

Whenever SQL Server is updating the statistics automatically, SQL Server Profiler

reports the Auto Stats event. On the other hand, SQL Server is also

able to create Statistic Objects on the fly, when SQL Server needs to estimate the

cardinality for a given query and no index (and therefore no Statistics Object) is

available, like in the following one:

SELECT * FROM Table1

WHERE Column1 > 1

GO

When Column1 is not indexed, SQL Server will create a new Statistics

Object for you.

All these things are great to improve performance for queries, but what happens when

SQL Server can't change the underlying Statistics Object or isn't able to create a

new one for you? Just think of databases that are marked as read only, or when you

are using Database Snapshots. Data in Database Snapshots can't change, but you can

issue completely new queries that leads to the demand of creating new Statistics Objects.

In that case, SQL Server can't create/update the Statistics Object, which means SQL

Server can't re-evaluate the cardinality estimation and proceeds with the cached execution

plan, which can have a huge performance drawback for your read only workload. Let's

demonstrate this problem with a simple example. I'm creating a new database with a

simple table in it:

USE master

GO

CREATE DATABASE StatisticsDatabase

GO

USE StatisticsDatabase

GO

CREATE TABLE Table1

(

Column1 INT IDENTITY,

Column2 INT

)

GO

In the next step I'm populating the table with 1500 records:

SELECT TOP 1500 IDENTITY(INT, 1, 1) AS n INTO #Nums

FROM

master.dbo.syscolumns sc1

INSERT INTO Table1 (Column2)

SELECT n FROM #nums

DROP TABLE #nums

GO

When we select everything from the table, SQL Server uses the Table Scan operator,

because we have not defined a Clustered Index on our table:

SELECT * FROM Table1

GO

To demonstrate the problem with errors in the cardinality estimation I'm creating

a Non-Clustered Index on Column2.

CREATE NONCLUSTERED INDEX idxTable1_Column2 ON Table1(Column2)

GO

The following query uses the previous created Non-Clustered Index:

SELECT * FROM Table1

WHERE Column2 = 2

GO

SQL Server creates an Execution Plan with a Non-Clustered Index Seek and RID Lookup

(Heap) operator – the traditional Bookmark Lookup operator. By now we have a table

with 1500 records, which means we need 20 percent + 500 rows of data changes, until

SQL Server will update the underlying Statistics Object of the Non-Clustered Index.

In our case these are 800 data changes in our table (300 + 500). So let's insert 800

additional records into the table:

SELECT TOP 800 IDENTITY(INT, 1, 1) AS n INTO #Nums

FROM

master.dbo.syscolumns sc1

INSERT INTO Table1 (Column2)

SELECT 2 FROM #nums

DROP TABLE #nums

GO

SQL Server will update the Statistics Object as soon as a new SELECT query will reference

it. Prior to that we will now mark our database as read only, so that SQL Server can't

change anything anymore:

USE master

GO

-- Mark the database

as readonly

ALTER DATABASE StatisticsDatabase

SET READ_ONLY WITH NO_WAIT

GO

USE StatisticsDatabase

GO

Let's now run the query which will trigger an update of the Statistics Object:

SELECT * FROM Table1

WHERE Column2 = 2

GO

Under normal conditions, SQL Server will now re-evaluate the cardinality estimation

and create an Execution Plan with a Table Scan operator. But in our case SQL Server

can't change anything in the database, therefore SQL Server will reuse the inefficient

cached Execution Plan with the Bookmark Lookup! When you look at the Execution Plan

you will also see a huge difference between the Estimated and Actual Number of Rows:

That's the behavior of SQL Server 2005/2008 (R2), and you can't change anything about

it. The same problem will occur when you are using Database Snapshots.

But with SQL Server 2012 Microsoft has addressed that problem. SQL Server now supports

so-called Temporary Statistic Objects which are created in TempDb.

Every time when SQL Server wants to create or update an Statistics Object on a read

only database or on a Database Snapshot, SQL Server will create the necessary Statistics

Object in TempDb. For this functionality SQL Server needs 1 data page per each Statistics

Object in TempDb.

So when you run the previous code on SQL Server 2012, SQL Server will create a new

temporary Statistics Object in TempDb, and will create an Execution Plan with a Table

Scan operator, which makes much more sense, because our query is not selective enough

anymore.

Temporary Statistics Objects are also used by Readable Secondaries in AlwaysOn in

SQL Server 2012, because Readable Secondaries also have the same problem: they are

used for read only workloads, and SQL Server can't change anything in the Secondary

Replica, because all Replicas must have the same physical structure in the Availability

Group. So every time when you are working with readable Secondaries in SQL Server

2012, you will also have an overhead in TempDb, because of temporary Statistics. And

Readable Secondaries are also using transparently Snapshot Isolation, which also impacts

the performance of TempDb. But that's a topic for another weblog posting J.

Thanks for reading!

-Klaus

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating