Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,037,795 hits
  • Select GETDATE()

    April 2014
    M T W T F S S
     123456
    78910111213
    14151617181920
    21222324252627
    282930  

SQL 2014 Learning Series # 6 – New Feature – Incremental Statistics (Part 2)

Posted by blakhani on April 17, 2014


In part 1 of Incremental Statistics, we saw demo about “CREATE STATISTICS …INCREMENTAL = ON” and “UPDATE STATISTICS… RESAMPLE ON PARTITIONS(9, 10). As I mentioned in the closing note of last post, there are few more places where syntax has been enhanced. So, we would cover those in this post.

ALTER DATABASE in SQL 2014 provides an option for auto-stats to be created on incremental basis. Here is the command.

ALTER DATABASE [AdventureWorks2014]
 SET AUTO_CREATE_STATISTICS ON  (INCREMENTAL=ON)


UPDATE STATISTICS also has clause INCREMENTAL ON|OFF. Why? When we specify ON, per partition statistics are recreated whereas OFF would drop existing statistics and recompute the statistics. 

UPDATE STATISTICS Production.TransactionHistoryArchive_IncrementalDemo (IncrementalStatsDemo)
with INCREMENTAL= OFF


To identify whether a statistics is created as with incremental or not, we can use catalog view sys.stats. In SQL Server 2014 a new column is_incremental has been introduced.

UPDATE STATISTICS Production.TransactionHistoryArchive_IncrementalDemo(IncrementalStatsDemo)
with INCREMENTAL= ON
go
select * from sys.stats where is_incremental = 1
go
UPDATE STATISTICS Production.TransactionHistoryArchive_IncrementalDemo(IncrementalStatsDemo)
with INCREMENTAL= OFF
go
select * from sys.stats where is_incremental = 1
go

In first select query output, we should see IncrementalStatsDemo and since we turned it off later, the second select should not show the output.

image

There are some error messages which you should be aware of.

Msg 9111, Level 16, State 1, Line 1

UPDATE STATISTICS ON PARTITIONS syntax is not supported for non-incremental statistics.

Msg 9108, Level 16, State 1, Line 1

This type of statistics is not supported to be incremental.

First error is self-explanatory. If we attempt to update traditional statistics and provide partition clause, we would get first error. Second error is more generic and can be caused in various situation. Books online motioned those limitation here. I am pasting them below.

<Snip from books online>

Incremental stats are not supported for following statistics types:

  • Statistics created with indexes that are not partition-aligned with the base table.
  • Statistics created on AlwaysOn readable secondary databases.
  • Statistics created on read-only databases.
  • Statistics created on filtered indexes.
  • Statistics created on views.
  • Statistics created on internal tables.
  • Statistics created with spatial indexes or XML indexes.

</Snip from books online>

Hope this blog helped you in learning new feature.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisement

    4 Responses to “SQL 2014 Learning Series # 6 – New Feature – Incremental Statistics (Part 2)”

    1. karthik said

      Thanks sir..

    2. Jay said

      Be very careful when using this. sql server 2014 out of the box release has a bug that messes up partitioned tables with incremental stats. I learned that the hard way. There are CUs that fix the issue , CUs come with its own set of problems at times. Hopefully a stable version of SP1 will come out soon enough

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )

    Connecting to %s

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

     
    %d bloggers like this: