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

Plan Metrics Without the Plan: Trace Flag 7412

I place a lot of emphasis on capturing actual execution plans because of the runtime metrics, but with Trace Flag 7412, we don’t need the plan. This is great news, because capturing execution plans, even using extended events, is an expensive proposition. However, using either the query_thread_profile event, or, Trace Flag 7412, we can get the runtime metrics without the plan.

Trace Flag 7412

Here’s how it works.You can either be running the extended event, query_thread_profile (a debug event, but one documented and supported by Microsoft) or, enable the Trace Flag 7412. I like to use the extended event in a targeted fashion to easily see behaviors on a query without having to capture the plan. You can even capture the information and then combine it with an estimated plan from the Query Store to coordinate it with the operators, because, as you can see below, the NodeID is captured along with all the great information:

But, what about long running queries? Do you have to wait until the end of execution to see the data? Nope. With the Trace Flag enabled, we can query sys.dm_exec_query_profiles. This returns basically the same information. Here’s an example query that we could use with an execution plan to see estimated versus actual, as the query runs:

SELECT deqp.session_id,
FROM sys.dm_exec_query_profiles AS deqp;

Now that’s VERY exciting stuff. Runtime metrics like we were capturing an execution plan, but without capturing an execution plan. It gets better.

Live Execution Plans

Starting in SQL Server 2014, we had the ability to look at live execution plans through the data supplied by sys.dm_exec_query_statistics_xml. However, it required that we capture the plans to see the information. It’s nice, but it’s not great. Let’s say you’re in the situation where you suddenly get a phone call, “My query is slow.” You can’t go turn this on unless you’re already capturing plans. However, capturing plans is bloody expensive, so you can’t just capture every plan all the time.

Enter Trace Flag 7412.

If you have Trace Flag 7412 enabled, or, you’re capturing the query_thread_profile extended event, you also will ALWAYS have access to live execution plans. That’s right. When the phone call comes in, you just open up a tool, say Activity Monitor, and you can immediately see the live execution plan showing the processing metrics as the query is running. No additional execution plan capture set up ahead of time needed.


Now, don’t immediately go and enable Trace Flag 7412 on all your servers. While capturing these statistics are lightweight, especially when compared to capturing execution plans, they are not, no weight. Capturing this information adds load to the system. However, it might load that we’re willing to put up for the reward of on-demand live execution plans.

If you like learning about ways to make query tuning easier, then you don’t want to miss my all day seminar on the topic. I’m taking this show on the road, around the world. Please find something near by and register:

For SQLSaturday Indianapolis (and my first time speaking in Indiana) on August 10, 2018. Please go here to sign up.

I’ll be at SQLSaturday Sioux Falls (and my first time speaking in South Dakota) on August 18, 2018. Please sign up here.

For SQLSaturday Oslo on August 31, 2018. Click here right now to register.

Newly announced, SQLSaturday Boston on September 21st. You can go here to register.

The post Plan Metrics Without the Plan: Trace Flag 7412 appeared first on Grant Fritchey.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


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

Loading comments...