is it possible to get the query plan out using jdbc on sql server?

Question

I am using the JTDS driver and I'd like to make sure my java client is receiving the same query plan as when I execute the SQL in Mgmt studio, is there a way to get the query plan (ideally in xml format)?

basically, I'd like the same format output as

set showplan_xml on

in management studio. Any ideas?

Cheers,

-James

Some code for getting the plan for a session_id

SELECT usecounts, cacheobjtype,
  objtype, [text], query_plan
FROM sys.dm_exec_requests req, sys.dm_exec_cached_plans P
  CROSS APPLY
    sys.dm_exec_sql_text(plan_handle)
  CROSS APPLY
    sys.dm_exec_query_plan(plan_handle)    
WHERE cacheobjtype = 'Compiled Plan'
    AND [text] NOT LIKE '%sys.dm_%'
    --and text like '%sp%reassign%'
    and p.plan_handle = req.plan_handle
    and req.session_id = 70 /** <-- your sesssion_id here **/

Answer 1

1. Identify your Java session id. Print @@SPID from java or use SSMS and look into sys.dm_exec_sessions and/or sys.dm_exec_connections for your Java client session (it can be identified by program_name, host_process_id, client_net_address etc).
2. Execute your statement. Look in sys.dm_exec_requests for the session_id found at 1.
3. Extract the plan using sys.dm_exec_query_plan from the plan_handle found at 2.
4. Save the plan as a .sqlplan file and open it in SSMS

Alternatively you can use the Profiler, attach the profiler to server and capture the Showplan XML event.