April 7, 2016 at 2:08 pm
Is it possible to capture the text of an RPC SELECT statement from within the view which it calls? I have tried using dm_exec_cached_plans, dm_exec_query_stats and dm_exec_sql_text but query plans don't seem to be cached until after they are executed. Even dm_exec_query_memory_grants is unavailable until processing is completed.
I need to somehow get the entire query that is currently being executed. I believe dm_exec_requests only returns the current statement, not the full request. Is there some way to tap into sp_prepexec? I am also looking into Extended Events as a possible solution. Is there anything else I can do?
This is SQL Server 2014 -- I posted more details at http://stackoverflow.com/questions/36437573/how-to-get-query-plan-during-execution
Thanks!
Sphera
April 7, 2016 at 2:56 pm
Nothing you can do, AFAIK, until it's done. There are live query statistics in SQL Server 2016, but I don't think anything is back ported to 2014.
April 7, 2016 at 3:02 pm
The plan is stored before execution completes, but it's stored just before execution starts. You're trying to capture the plan before execution. There's no way to do that without running a debugger to interrupt the process itself. That's not possible. Even the 2016 behavior is relying on the fact that the plan gets created, and stored, before execution starts, and then, during a long running query, you can actually watch the processing of the data through the operations in the plan.
That functionality is there in 2014, there's just not a pretty GUI for it. You can use sys.dm_exec_query_profiles to see the same information as the Live Query Plan shows in 2016.
None of that is going to help you do what you want. The way the process works is that the query goes through the optimization process and a plan gets created. That plan gets stored into cache and then the query executes, but the whole query executes, even though you actually have two plans, one for the function, and one for the calling query, it's all created at once and executed at once. There's no way to preemptively stop that process part way through (again, sans using the debugger, which is a really bad idea).
"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
April 29, 2016 at 11:36 am
Thank you for the responses! I was trying to capture the entire query plan during its execution and you say "it's stored just before execution starts"... That's a little confusing. It seems like it ought to be there somewhere! Even so, I can accept that this is an untenable idea and I have devised a less elegant work-around by restructuring the view itself. There is a performance penalty but the downstream error is avoided. My fundamental problem is the way the client application generates its SQL statements and there is nothing I do about that -- so, users will just have to accept whatever limitations may result.
Sphera
April 29, 2016 at 11:48 am
fred.newcomer (4/29/2016)
Thank you for the responses! I was trying to capture the entire query plan during its execution and you say "it's stored just before execution starts"... That's a little confusing. It seems like it ought to be there somewhere! Even so, I can accept that this is an untenable idea and I have devised a less elegant work-around by restructuring the view itself. There is a performance penalty but the downstream error is avoided. My fundamental problem is the way the client application generates its SQL statements and there is nothing I do about that -- so, users will just have to accept whatever limitations may result.
When I say "just before execution starts" I mean, you must pass the query to the server, it goes through the optimization process to create the plan and upon plan creation, it starts the query. There isn't a place for you in between plan creation and query execution for you to check the plan. There is no pause or gap. Effectively, although not literally, it's of a piece.
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply