SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

This Can Be the Best Thing in SQL Server 2014

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.

Matan Yungman

Matan Yungman is a Technical Evangelist and senior consultant at Madeira SQL Server Services. His job is to get the word out about SQL Server. He speaks, lectures, writes, teaches and consults about SQL Server, focusing mainly on performance tuning, database development, high availability and database design. He’s passionate about SQL Server, technology and the SQL Server community.


Leave a comment on the original post [www.dbnewsfeed.com, opens in a new window]

Loading comments...