Hi Friends,

In my previous blog we have seen the new feature of SQL Server 2014- Incremental Statistics as well as how we can specify this at the time of index creation. If you have not gone through that then below is the link for that blog.

We all know that statistics in SQL Server can also be manually updated. In this blog I will show you the improvements for SQL Server manual statistics update in SQL Server 2014 over previous version of SQL Server.

Environment: I have a table xtstatsdemo with columns id, name and balance. Table is having 3 partitions with id as Key. First partition is having rows with key range 1-9999; Second partition is having rows with key range 10000-19999; Third partition is having rows with key range 20000-30000. Here I have one clustered index on id column. Here Statistics also created automatically due to index creation.

Now for demo purpose let’s consider that first two partitions are static or you can say read only, while third partition will be used for all DML operations. Before SQL Server 2014 – Incremental Statistics, if we will do the manual statistics update then update stats operation will scan the complete table i.e. all partitions of that table. Statistics update will take too much time if static or read only partitions are having too much amount of data.

In SQL Server 2014- Incremental statistics, we can manually update the statistics only for specific partition(s). Then internally SQL Server will merge this update statistics with other partition statistics to generate the complete table statistics. That means now statistics update will take less resources. This is really a great improvement in SQL Server 2014. You can specify a single partition or multiple partitions as per your requirement while updating the statistics manually.

Now let me show you this practically. First I am going to setup the environment.

Now let me show you the status of statistics auto created by index.


Now I will delete 1800 rows from third partition. 1800 rows are below threshold for stats auto update. So there will be no auto update happen.


From the above output it is clear that there is no Auto update happens here. Now let me do it manually. Here I know that DML operation happen on partition 3rd only. So I will update the stats for partition 3rd only rather than the complete table.


Here stats update operation will update the statistics for complete table while it will scan only 3rd partition rather than complete table. Consider the scenario where partition 1 and 2 both are having large amount of data (like millions of rows) then this operation will be fast here because it will scan partition 3 only rather than complete table. If you want then you can check this behaviour by using event trace for scan_started extended event where you will found that update stats will scan only third partition.


Prince Rastogi

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook