sys.dm_exec_query_profiles - SQL 2014 , new way to troubleshot queries

SQ: server 2014 has many silent features which are very unique and can help a lot to developers
one of it is sys.dm_exec_query_profiles

This DMV gives real time query execution details while query is running.
This is very helpful for troubleshooting queries.
The counters are per operator per thread. The data is serialized into the showplan xml when query finishes.

SELECT INTO with Parallelism

Lets see one more feature of SQL Server 2014

Its SELECT INTO with parallelism

Select into supports parallelism and due to this time for inserting data into table is reduced

System tables for columnstore indexes - sys.column_store_row_groups

System tables for columnstore indexes.

Columnstore index is a new feature and will be used heavily once 2014 is live.
We also have to be familiar with lots of stuffs related to it.

Columnstore Indexes and sp_spaceused

SQL Server 2014 supports clustered columnstore index.
Storage structure and updating/deleting data is quite different compare to normal tables.
It has concepts of deltastore which we will see in future articles.
Here I want to notice you behavior of clustered columnstore index while reporting table size.
Lets see an example for it

Archive data compression using Columnstore_Archive

As we all are aware about clustered columnstore index and compression that we can achive by it
In reporting system we generally query recent data more compared to older data.
So it can be acceptable for us in many cases if we can get more compression at the cost of slower query.

Columnstore Indexes- Identification and space usage

How to find tables with columnstore indexes

As we know that SQL 2012 has added feature of Nonclustered Columnstore Index. SQL 2014 onwards it will support Clustered Columnstore Indexes also.

As databases are growing we also need to track which tables has clustered and nonclustered columnstore indexes.