Hey guys, i'm new at SQL, and i need help with logging all execution plan into table. I'm using SQL server 2000. Could you guys give me some general direction on how this could be done? Thanks
Welcome to Forums, If you are seeking customized query execution plans rather than created by query analyzer itself , you might use the below DMV : select*fromsys.plan_guides However if you are seeking those created by query analyzer itself , you might use the below DMVs: SELECT [cp].[refcounts] , [cp].[usecounts] , [cp].[objtype] , [st].[dbid] , [st].[objectid] , [st].[text] , [qp].[query_plan] FROMsys.dm_exec_cached_plans cp CROSSAPPLYsys.dm_exec_sql_text( cp.plan_handle ) st CROSSAPPLYsys.dm_exec_query_plan( cp.plan_handle ) qp ; Thereby , you could create a buffer table to store the results of these above DMVs through a scheduled job by the required frequency basis
I tried that already, but it put the entire execution plan in one entry. If the query is large, the smt text is truncated.
But in the same Profiler you can get that full query in text data, including TSQ and SP in your trace. If that does not work in a table, should work storing in a file.
The text data is truncated, and you can not compute the execution time for a specific event. Is there any ways to log the duration, IO, CPU cost for each subtree of the execution plan?
