Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Plan Metrics Without the Plan: Trace Flag 7412

DZone's Guide to

Plan Metrics Without the Plan: Trace Flag 7412

We discuss a lightweight option that can help optimize SQL queries in a Microsoft database, allowing you to analyze better and faster at runtime.

· Big Data Zone ·
Free Resource

Hortonworks Sandbox for HDP and HDF is your chance to get started on learning, developing, testing and trying out new features. Each download comes preconfigured with interactive tutorials, sample data and developments from the Apache community.

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,
       deqp.node_id,
       deqp.physical_operator_name,
       deqp.estimate_row_count,
       deqp.actual_read_row_count
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.

Conclusion

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, we might be willing to put up with this load for the reward of on-demand live execution plans.

Hortonworks Community Connection (HCC) is an online collaboration destination for developers, DevOps, customers and partners to get answers to questions, collaborate on technical articles and share code examples from GitHub.  Join the discussion.

Topics:
big data ,query tuning ,data capture ,runtime metrics

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}