August 7, 2009 at 4:07 am
Hi All
I am wondering if there is a quite to reading information from the SQL procedure cache table, I would like to know the top stored procedures which have thier query plans cached based on parameter values.
Thanks.
August 7, 2009 at 4:47 am
Edit: Sorry, noticed after posting it was SQL 2000
In SQL 2000, you can query the syscacheobjects table, but there's very little useful information in there. No where close to what the 2005 DMVs expose.
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
August 7, 2009 at 5:26 am
On a 2005, what DMVs can i use and what kind of information can I extract ?
August 7, 2009 at 5:34 am
This is from a presentation that I did earlier this week at TechEd
-- cumulative execution stats for the queries in cache
select * from sys.dm_exec_query_stats
-- the 10 longest running queries
SELECT TOP (10)
execution_count,
total_worker_time, max_worker_time,
total_elapsed_time, max_elapsed_time,
total_logical_reads, max_logical_reads,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY max_elapsed_time DESC
-- Getting the Execution plans of the 10 longest running queries
SELECT TOP (10)
execution_count, total_worker_time, max_worker_time,
total_elapsed_time, max_elapsed_time,
total_logical_reads, max_logical_reads,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY max_elapsed_time DESC
The 4 main DMVs for plan cache info are sys.dm_exec_cached_plans, sys.dm_exec_query_stats, sys.dm_exec_sql_text and sys.dm_exec_query_plan. There are a few others, but those 4 are the most frequently used.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply