January 19, 2011 at 3:00 am
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
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply