Performance Improvements in SQL 2014(1/4)

Hi dears, it is always nice to know something new. Would like to start with a wisdom i like “Learning brains will stay younger”. It is always advised by many performance tuning expertise,

“Update Statistics before you take any other steps to tune performance”.

SQL server 2014 has new feature called incremental statistics. Really it was a nice a feature, if you are having your tables spread into many partitions. Tables on single partition won’t be benefited by this feature.  Before we know about this feature, will have a glance about Update statistics, if you did’t update statistics on the database. The query optimized will struggle to give you best execution plan, which will ruin the performance of your query for sure.

Sales Data Partitions January Partition1 February Partition2 March Partition3

Now we will see about the new feature, imagine you have a table exist over partitions. which will fill the data monthly on every partitions. For example, sales data will filled into the Sales table as given below.

Now the tables will populate the data as given above, in the middle of march you would like to make update statistics on this table.Given below is normal update statistics before SQL Server 2014.

UPDATE STATISTICS TableName(StatisticsName) WITH FULLSCAN

The above statement will update the statistics, on all partitions even though the change was happen in the last partition, it will cost lot of resources. Here below is the code view a number of partitions on table,  i will use table name sales to demonstrate the feature.

SELECT *

FROM sys.partitions

WHERE OBJECT_ID = OBJECT_ID('Sales')

GO

Lets us create a Statistics for this table

CREATE STATISTICS Stats_Sales ON Sales(SalesId)

Now here has the new feature come into action. Previously, if we have to update the statistics, we will have to FULLSCAN the entire table irrespective of which partition got the data.

However, in SQL Server 2014 we can just specify which partition we want to update in terms of Statistics. Here is the script for the same.

UPDATE STATISTICS Stats_Sales(Sales)

WITH RESAMPLE ON PARTITIONS(3)

Update Statistics

The new feature of Incremental Statistics is indeed a boon for the scenario where there are partitions and statistics needs to be updated frequently on the partitions.

Click here to read my next blog, regarding the enhancement on indexes