New versions have bombastic and catchy features. But a lot of times, the little, hidden features are the ones that matter the most.
I already wrote in the past about the small changes that make a difference in SQL Server 2012, and yesterday about the maturity of SQL Server 2014. Here’s another classic example for a feature nobody talked about that can make a huge difference.
sys.dm_exec_query_profiles is a new DMV in SQL Server 2014. According to the documentation, this DMV:
Monitors real time query progress while the query is in execution. The counters are per operator per thread. The data collected is serialized into the SHOWPLAN XML when the query finishes. Use this DMV to monitor the progress of a query execution and determine where the query uses the most time.
I don’t see it in the first CTP, perhaps because compatibility level 120 has not been implemented yet, but If all of this is true, it can be a life changer. The execution plans we can find today in the Plan Cache present only the estimated numbers. Using this DMV we will be able to see which operator takes the most time and resources in real time (and afterwards in the SHOWPLAN XML). It can open up a totally different query tuning experience, and all out of the box.
I’m really excited to see it in action.
