I am developing a T-SQL test harness for automated performance tests. The testing procedure will receive a string to execute as Dynamic SQL. I am going to add a bunch of standard initialization and measurement stuff to it, that's all easy. However, one additional thing that I want to do is to capture the Execution Plan (the actual plan is preferred) into an XML variable or column to be saved as part of the test header record.
I puzzled over how to do this for while this morning, until I came up with the following approach:
Declare @qp as XML
SELECT @qp = query_plan
From sys.dm_exec_requests
Cross Apply sys.dm_exec_query_plan(plan_handle)
Where session_id = @@spid
-- Put Code to Test Here
select @qp
True, this will include some extra/redundant stuff like the QP for the Test-Harness statments themselves, but I think that I can live with that.