The great thing about new versions of SQL Server is the fact that they are packed full of new features but there some that don’t really get talked about much. I found out about a concept called lightweight query execution statistics profiling infrastructure – think of this as a way of capturing your “in-flight” query execution information, I did however encounter an issue which you will see later on.

Microsoft seems to now have 2 different types of statistics profiling infrastructure, a legacy one (which I am very much used to) and the lightweight one.

Legacy statistics profiling infrastructure can be enabled by using the below – pretty common and well used.

• SET STATISTICS XML ON
• SET STATISTICS PROFILE ON
• Query_post_execution_showplan extended event ( I would be careful with this one)

Lightweight statistics profiling infrastructure is available in SQL Server 2014 SP2 and SQL Server 2016 and can be enabled by the below trace flag.

• Globally by using trace flag 7412.

I will activate lightweight profiling via the trace flag and use a system function that is available starting with SQL Server 2016 SP1 called sys.dm_exec_query_statistics_xml which retrieves showplan XML with transient statistics.

DBCC TRACEON (7412, -1);
GO

--Track your session ID
SELECT * FROM sys.dm_exec_query_statistics_xml(53);
GO

There is your query plan in XML form, soon as the query finishes the entry goes away.

Again you can build queries however you desire.

DECLARE @WHATSPID INT = 55

SELECT s.program_name,s.login_time,s.database_id,SXML.plan_handle,SXML.Query_plan
FROM sys.dm_exec_query_statistics_xml(@WHATSPID) AS SXML
JOIN sys.dm_exec_sessions AS S
ON SXML.session_id = s.session_id

Here is another query, this time joining onto sys.dm_exec_requests.

DECLARE @WHATSPID INT = 51

SELECT
RQ.session_id,
RQ.command,
RQ.wait_type,
RQ.status,
RQ.dop,
SXML.plan_handle,
SXML.Query_plan
FROM sys.dm_exec_query_statistics_xml(@WHATSPID) AS SXML
JOIN sys.dm_exec_requests AS RQ
ON SXML.session_id = RQ.session_id

However, when I tried doing multiple joins onto other DMVs I ended up being disconnected from the server with the message: A transport-level error has occurred when receiving results from the server. 

I had a huge dump file in my error log:

Snippet: A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. SQL Server Assertion: File: , line = 725 Failed Assertion = ‘0’ Should never happen. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

There is no corruption and I assume this is a bug. I probably need to log a call with Microsoft product support about this as it definitely is not normal.

Assuming you do not get the above error why would you want to go this route?

According to documentation online (https://msdn.microsoft.com/en-us/library/mt791503.aspx) it states that the new query execution statistics profiling infrastructure dramatically reduces performance overhead of collecting per-operator query execution statistics.

Personally, I might hold back until I get further clarity around my errors.

UPDATE May 2017: Assertion error has been fixed as per https://support.microsoft.com/en-us/help/4010984/fix-assertion-error-when-dm-exec-query-statistics-xml-is-used-in-a-que

Issue has nothing to do with lightwieght profiling more so with the DMF.