Edit (4/23/2018): due to a possible random AV running your favorite monitoring stored procedure, please make sure you install KB 4078596 in your SQL Server 2016 and SQL Server 2017.
Edit (9/24/2018): this feature is now enabled by default in SQL Server 2019. Trace flag not needed anymore! Also, new query hint query_plan_profile to enable lightweight profiling at the query level, for any session, in SQL Server 2019, 2017 CU11 and 2016 SP2 CU3.
One of the most important actions when a performance issue hits, is to get precise understanding on the workload that’s executing and how resource usage is being driven. The actual execution plan is an invaluable tool for this purpose.
Query completion is a prerequisite for the availability of an actual query plan, but with LQS (Live Query Statistics), you can already get information about in-flight query executions (see this blog post), which is especially useful for long running queries, and queries that run indefinitely and never finish.
Up to SQL Server 2016 RTM
To look at in-flight query executions, the query execution statistics profile infrastructure must be enabled on demand (later in this post referred to as Legacy).
The query execution statistics profile infrastructure can be enabled for a target session:
- Clicking on Include Live Query Statistics in SSMS, starting with SQL Server 2014.
- SET STATISTICS XML ON
- SET STATISTICS PROFILE ON
Or globally, to view LQS for all sessions (such as from Activity Monitor), by enabling the query_post_execution_showplan (starting with SQL Server 2014).
But enabling this event can take a big performance toll in your production server, and so probably you are not running with it continuously, even if it gives you the ability to tap into any running executions, and query plans with execution statistics.
With SQL Server 2016 and SQL Server 2014 SP2, we managed to considerably bring down the performance toll of running this xEvent (see table below, middle row), and also introduced the query_thread_profile extended event. This also enables LQS for all sessions, using Activity Monitor or directly querying sys.dm_exec_query_profiles.
Enters SQL Server 2016 SP1
Now with SQL Server 2016 SP1, we are introducing a real lightweight query execution statistics profiling infrastructure, to dramatically reduce performance overhead of collecting per-operator query execution statistics, such as actual number of rows. This feature can be enabled either using global startup trace flag 7412, or is automatically turned on when query_thread_profile xEvent is enabled. Again, this also enables LQS for all sessions, using Activity Monitor or directly querying sys.dm_exec_query_profiles.
How much lightweight you ask?
We ran a TPC-C like workload in both the legacy and lightweight profiling infrastructures, and this is what we found:
So you can see above that enabling the new lightweight profiling infrastructure (with the trace flag 7412) has a max overhead of 2 percent, as opposed to 75 percent before SQL Server 2014 SP2/2016 RTM. This means that now, for any server that is not already resource bound, you can run the lightweight profiling infrastructure continuously, and tap into any running execution at any time using for example Activity Monitor or directly querying sys.dm_exec_query_profiles, and get the query plan with execution statistics.
The possibilities this unlocks for performance troubleshooting in production environments are tremendous, and if your servers are not already resource bound, we encourage you to enable this trace flag for the potential it unlocks. As usual, we recommend you test this in a pre-production environment to estimate your specific impact beforehand.
When the lightweight profiling is enabled, the information in sys.dm_exec_query_profiles (and so LQS) is available via the new profiling infrastructure instead of the legacy profiling infrastructure.
Here's a few usage examples:
- Clicking on Include Live Query Statistics in SSMS uses the legacy profiling infrastructure starting with SQL Server 2014 RTM, but with SQL Server 2014 SP2 and SQL Server 2016, doing the same uses an earlier version of the lightweight profiling infrastructure. In SQL Server 2016 SP1, and only by setting TF 7412 or using query_thread_profile xEvent, you can then click on Include Live Query Statistics and use the new lightweight profiling infrastructure. Starting with SQL Server 2016 SP2CU3 and SQL Server 2017 CU11, the new query hint query_plan_profile can be used to enable lightweight profiling at the query level, for any session.
- SET STATISTICS XML ON, SET STATISTICS PROFILE ON and query_post_execution_showplan xEvent always use the legacy profiling infrastructure.
There’s also the new DMF sys.dm_exec_query_statistics_xml, which returns the query execution plan for in-flight requests. For example, you can now use this DMF to get the plan for a query while it is running, and works under both legacy and lightweight profiling infrastructure.
Use this DMV to retrieve the showplan XML with transient statistics, as seen below:
And you can join with other DMVs like sys.dm_exec_requests. The Running-Blocked-Processes script in the TigerToolbox Github is already using this to access the “live_query_plan_snapshot”, among other useful data.
If your SQL Server is not already CPU bound and a 1.5% to 2% overhead is negligible for your workloads, we recommend you enable TF 7412 as a startup trace flag, and unleash the power of live query troubleshooting in production environments.
Pedro Lopes (@sqlpto) – Senior Program Manager