TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
C# Corner
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Get Cached SQL Plans That Consumed The Most Cumulative CPU
Bhavik Patel
Jul 31
2016
Code
536
0
1
facebook
twitter
linkedIn
Reddit
WhatsApp
Email
Bookmark
expand
--Run the following query to get the TOP 50 cached plans that consumed the most cumulative CPU
--All times are in microseconds
SELECT
TOP
50 qs.creation_time, qs.execution_count, qs.total_worker_time
as
total_cpu_time, qs.max_worker_time
as
max_cpu_time,
qs.total_elapsed_time, qs.max_elapsed_time, qs.total_logical_reads, qs.max_logical_reads, qs.total_physical_reads,
qs.max_physical_reads,t.[text], qp.query_plan, t.dbid, t.objectid, t.encrypted, qs.plan_handle, qs.plan_generation_num
FROM
sys.dm_exec_query_stats qs
CROSS
APPLY sys.dm_exec_sql_text(plan_handle)
AS
t
CROSS
APPLY sys.dm_exec_query_plan(plan_handle)
AS
qp
ORDER
BY
qs.total_worker_time
DESC
SQL Server
SQL Query