0

I am facing the following problem . I need to get in my vb.net program SQL's query plan in xml for a query programatically.Then i will parse it and get some info but thats not the case.

Via Management studio when i use the following set of commands i get the result i want

set showplan_xml on 
GO 
select * from testtable
GO
set showplan_xml off
GO

The problem is that from the program i can send sql commands to execute or sql commands to fetch data but only one at a time and that is because i send commands with a specific custom class that connects with an application server that connects with the database. I dont have direct access to the database in order to use all the methods that i found when googling or at least i dont know well all the methods of this custom made class.

I am trying to find a workaround using the query plan caching . I was thinking if the was any way using an option at the end of the query in order to force sql to cache the query_plan and then get the xml using the following method

SELECT cplan.usecounts, cplan.objtype, qtext.text, qplan.query_plan
FROM sys.dm_exec_cached_plans AS cplan
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
ORDER BY cplan.usecounts DESC
Ted Kon
  • 87
  • 1
  • 6
  • You need to be able to control connection (`SqlConnection`) which executes commands. Then just do `set showplan_xml on` on that connection first, then do `select ..` , then `set showplan_xml off` . So it could be 3 commands but on the same connection. – Renat May 20 '21 at 19:49
  • Unfortunatelly this doenst worked (i have already tried it) because after each command the class sends an exec sp_reset_connection – Ted Kon May 20 '21 at 20:00
  • Can you call a stored procedure instead? – Hursey May 20 '21 at 21:08
  • Unfortunally set showplan_xml is supported only on native sql procedures ! I have tried this too! Very Nice thought although !! – Ted Kon May 20 '21 at 21:13

0 Answers0