SQL Server Performance

Log SQL execution plan into table

Discussion in 'ALL SQL SERVER QUESTIONS' started by dommy, May 4, 2012.

  1. dommy New Member

    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
  2. Shehap MVP, MCTS, MCITP SQL Server

    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
  3. dommy New Member

    SQL Server 2000 does not have DMV views.
  4. Luis Martin Moderator

    What about using Profiler and store in a table?
  5. dommy New Member

    I tried that already, but it put the entire execution plan in one entry. If the query is large, the smt text is truncated.
  6. Luis Martin Moderator

    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.
  7. dommy New Member

    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?

Share This Page