May 8, 2009 at 8:26 am
Last day of my convalescence and I want to actually accomplish something today, but I cannot figure out part of it. 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 would like to do is to capture the Execution Plan (actual preferred, but I'll take estimated) into an XML variable or column to be saved as part of the test header record.
Anyone (Gail, Grant, Paul or anyone else?) have any idea how I can do this from a stored procedure? Any help or thoughts appreciated.
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2009 at 9:02 am
Looks like I answered my own question. The following seems to work fine:
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 Test Query Here ...
select @qp
Of course its got this extra junk in there for the test-harness commadn them selves, but I guess that's liveable.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2009 at 9:27 am
Just note that you've got an estimated plan there (or more correctly, an execution plan without run time information).
I can't thing offhand of another way. If it was a client-side app, it would be easier.
SET STATISTICS XML ON, then the exec of the sql will return 2 resultsets, 1 from the SQL, one with the xml plan.
Insert into ... exec doesn't work. The xml plan disappears completely.
Hmmm....................
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 8, 2009 at 9:43 am
RBarryYoung (5/8/2009)
Looks like I answered my own question. The following seems to work fine:
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 Test Query Here ...
select @qp
Of course its got this extra junk in there for the test-harness commadn them selves, but I guess that's liveable.
for those of us little fish trying to follow along, i get an error on "plan_handle"---i'm running SQL2005 SP2 on my dev machine, I'll upgrade and see if the same code works after that. I'm not missing any other code from what you put in your snippet, right?
Lowell
May 8, 2009 at 9:50 am
Lowell (5/8/2009)
for those of us little fish trying to follow along, i get an error on "plan_handle"---i'm running SQL2005 SP2 on my dev machine, I'll upgrade and see if the same code works after that. I'm not missing any other code from what you put in your snippet, right?
Compat mode? It should work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 8, 2009 at 9:56 am
doh! that was it! thank you Gail, and thank you Barry for a handy snippet to add to my collection.
Lowell
May 8, 2009 at 10:07 am
Largely... What Gail said.
The only way you can get the actual execution plan on the fly is to use a trace.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 8, 2009 at 10:50 am
Grant Fritchey (5/8/2009)
Largely... What Gail said.The only way you can get the actual execution plan on the fly is to use a trace.
It would have been great if SQL Server stored that extra run-time info as well, the same way it stores the "estimated" data.
It could at least store actual values from the latest execution.
I wonder why the DMV storage has not been designed that way... Maybe because of the extra storage(memory) space requirements involved?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 8, 2009 at 11:21 am
Thanks folks. If anybody thinks of anything else, please let me know.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2009 at 1:47 pm
Marios Philippopoulos (5/8/2009)
It would have been great if SQL Server stored that extra run-time info as well, the same way it stores the "estimated" data.
It does with some, in a sense. The estimated information is part of the cached execution plans, used by future matching queries. The run time info (or at least some of it) is aggregated into sys.dm_exec_query_stats.
It is possibly a case of space. Consider a query run once a sec. Each operator in the query has an actual row count and a couple other runtime-only properties, each data flow has an actual row count. Let's say the query has 6 operators in it (small query). Using bigint for the row counts, that's minimum 11 x 8 bytes per sec for just one query. That's 316kb of memory per hour for one query's run-time stats. That's a fair bit of data to be storing.
Bear in mind that DMVs are in-memory structures, they're not persisted to disk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply