[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

Interpret a DMV to fimd Performance Bottleneck

I have a Performance issue and ran a DMV: I obtaned the following DMV in a SQL 2005 Administration book "The Bible"

select plan_handle, SUM(total_worker_time) as total_cpu_time, Sum(execution_count) as total_execution_count,
Count(*) as number_of_statmetns
From sys.dm_exec_query_stats as QueryStats
Group By plan_handle
Order By sum(total_worker_time) desc

I received output but I do not know how to interpret it. The column plan_handle looks to be a hexidecimal #, but how am I to find which query in my many databases is the culpret?
0
SeTech
Asked:
SeTech
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Brian ChanDBACommented:
This DMV, sys.dm_exec_query_stats , would give you the statistics on execution plan for query. If you have a look at the activity monitor, the bottom section list the most expensive query. those figure are sort of coming from this DMV.

You have four columns return from this query: plan_handle is just for identify the query itself, total_cpu_time is the cumulative cpu resource the query used so far ,  total_execution_count is how many times this query has been run, number_of_statmetns is the number of query which use this same execution plan.

Once you have the highest CPU time plan_handle, you should get the sql_handle sys.dm_exec_requests by

Select sql_handle from sys.dm_exec_requests where plan_hande=<<copy the plan_handle here>>>


sql_handle will  then allow you to find out the statement by

Select text from sys.dm_exec_sql_text(sql_handle)

0
 
SeTechAuthor Commented:
I obtined the plan_handle with the highest CPU time and input it as follows:

Select sql_handle from sys.dm_exec_requests where plan_handle = '0x03000A00E551726077C2A500C69E00000100000000000000'

Unfortunatley I received output with sql_handle as a column header but nothing else?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about:
with data as ( select plan_handle, SUM(total_worker_time) as total_cpu_time, Sum(execution_count) as total_execution_count,
Count(*) as number_of_statmetns
From sys.dm_exec_query_stats as QueryStats
Group By plan_handle
)
select data.*, f.*
  from data
  cross apply sys.dm_exec_query_plan(data.plan_handle) f
order by total_cpu_time desc

Open in new window

0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now