Filter not working on Showplan XML in SQL Server trace

Question

I have setup a SQL Server trace to capture long running queries on my SQL Server 2014 box and added Showplan XML event to capture the execution plan of the queries. The trace was created with duration filter. During the testing, I found that Showplan XML didn't take duration filter into account and was showing execution plan for all queries. I was expecting only the queries with my duration filter and their execution plans in the trace. However, this was not the case. It was showing duration filter queries correctly but the execution plan was getting logged for all the queries.

Does anyone know how to get around this problem in SQL Server trace?

Answer 1

SHOWPLAN XML event does not have a column with cpu duration so you cannot filter by duration. You can only filter by columns mentioned in this article.

https://learn.microsoft.com/en-us/sql/relational-databases/event-classes/showplan-xml-event-class

Few alternatives:

• use filter by other allowed columns.

• capture everything for as minimum time as practical, load in table and filter. Be careful about load generated by this process, especially on production server.

• Trace for long running queries. Use that list to filter SHOWPLAN XML event.

Answer 2

The old-school workaround for this was to have your SQL Trace log to a table, then once the trace started, create an AFTER INSERT trigger on that table. The trigger would then write out the Execution plan to a different table.

Insert into QueryPlansFromTrace (query_plan)
SELECT query_plan FROM sys.dm_exec_query_plan
(PLAN_HANDLE_FROM_INSERTED)

However, since you are on SQL2014, you should really be using Extended Events. There are several ways to do this with XE that are all superior.

Here's an example of exactly what you are trying to accomplish.