Performance Improvements in SQL 2014(3/4)

Hi dears, so for we have seen about the incremental update statistics and index enhancement on our previous blog post.

Now we will see about, how SQL server 2014 can cache temp tables.  Before we move further into the subject, will see about temp tables in crisp. These are the tables created at the run time and  these tables are created within the “tempdb” database.  To identify a table as a Temporary Table within the SQL statement, prefix the table name with the “#” character. And also we should know one more fact the caching is not per procedure , the caching is per objects.

SQL Server 2014 has given us some room, by which if we make our temp table in a proper way the temp table can be cached. We will see how this is possible.

Performance Improvements in SQL 2014(2/4)

Haja Batcha

Hi dears, on my previous blog we saw about a enhancement regarding a update statistics on SQL 2014. Will see yet another enhancement on SQL Server 2014 which is non clustered indexes on temporary table, and which can also be called inline indexes.

“Non clustered indexes on table variable”

First we will see ‘Which is not possible in previous edition, which is now possible in 2014′ secondly we will see ‘Which is possible in previous edition, which can be coded very easily’  

The table variables are one the wonderful feature which was introduced on SQL Server 2000. Because with table variable excessive recompilation will be avoided, it doesn’t need statistics when you create them. And the one big disadvantage was, you cannot create non clustered index on them, that too resolved from SQL Server 2014 CTP1 .

With SQL Server 2014 CTP1 , now it is possible to create Non Clustered Index on table variable.

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.