Post reply

storing estimation execution plan result in a table

  • January 19, 2011 at 3:00 am

    #245557

    Hi,

    I have a requirement to restrict a query before its execution.

    in other word, I want to check a query estimated execution statistics, give message to users on estimated execution time and execute the query once end user is ok with estimated exeuction time.

    i feel this is a kind of valid requirement as in this case we need to restrict heavy use of large tables and this logic is going to be implemented to small portion of db users.

    below block provides estimated execution statistics.

    SET NOCOUNT ON

    GO

    SET SHOWPLAN_ALL ON

    GO

    --Insert into #Statistics

    Select * from <table_name>

    GO

    SET SHOWPLAN_ALL OFF;

    GO

    can anyone suggest the way to store the statistics to a variable or a temporary table?

    I have below optional choices as these DMVs provide statistics only available in cache

    1. joining sys.procedures with sys.dm_exec_procedure_stats

    2. joining sys.dm_exec_query_stats with sys.dm_exec_sql_text(qs.sql_handle)

    thanks in advance

  • January 19, 2011 at 3:36 am

    #1275173

    There's no way to get estimated execution time from a query plan. Estimated cost != estimated query execution time

    I'm not sure there's a way to directly capture this into a table. If you set showplan_xml on and run the query, you should be able to fetch the plan out of sys.dm_exec_cached_plans, though I don't think it'll be in query_stats as it hasn't executed and hence has no execution stats

    SET SHOWPLAN_XML ON

    GO

    SELECT * FROM tbl1

    GO

    SET SHOWPLAN_XML OFF

    GO

    SELECT st.text, qp.query_plan FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

    WHERE st.text LIKE '%select * from tbl1%'

    AND st.text NOT LIKE '%sys.dm_exec_cached_plans%'

    The select can insert into a table or assign into a variable and you can use xquery to shred the xml.

    Can't put this into a procedure though, because the showplan settings have to be in separate batches.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply