Home > Performence Tuning > Step By Step to figure out the Inefficient query Plan:

Step By Step to figure out the Inefficient query Plan:


DBA can use profiler for the same but the below step is more convenient.

 

Step 1:  The query against sys.dm_exec_query_stats is an efficient way to determine which query is using the most cumulative CPU.

select

highest_cpu_queries.plan_handle,

highest_cpu_queries.total_worker_time,

q.dbid,

q.objectid,

q.number,

q.encrypted,

q.[text]

from

(select top 50

qs.plan_handle,

qs.total_worker_time

from

sys.dm_exec_query_stats qs

order by qs.total_worker_time desc) as highest_cpu_queries

cross apply sys.dm_exec_sql_text(plan_handle) as q

order by highest_cpu_queries.total_worker_time desc

 

Step 2: We can use the following query to find the top user sessions that are allocating internal objects, including currently active tasks.

 

SELECT

t1.session_id,

(t1.internal_objects_alloc_page_count + task_alloc) as allocated,

(t1.internal_objects_dealloc_page_count + task_dealloc) as

deallocated

from sys.dm_db_session_space_usage as t1,

(select session_id,

sum(internal_objects_alloc_page_count)

as task_alloc,

sum (internal_objects_dealloc_page_count) as

task_dealloc

from sys.dm_db_task_space_usage group by session_id) as t2

where t1.session_id = t2.session_id and t1.session_id >50

order by allocated DESC

 

Here is a sample output.

 

session_id allocated            deallocated

———- ——————– ——————–

52         5120                 5136

51         16                   0

 

Step 3: Once you have isolated the task or tasks that are generating a lot of internal object allocations, we  can find out which Transact-SQL statement it is and its query plan for a more detailed analysis.

 

select

t1.session_id,

t1.request_id,

t1.task_alloc,

t1.task_dealloc,

t2.sql_handle,

t2.statement_start_offset,

t2.statement_end_offset,

t2.plan_handle

from (Select session_id,

request_id,

sum(internal_objects_alloc_page_count) as task_alloc,

sum (internal_objects_dealloc_page_count) as task_dealloc

from sys.dm_db_task_space_usage

group by session_id, request_id) as t1,

sys.dm_exec_requests as t2

where t1.session_id = t2.session_id and

(t1.request_id = t2.request_id)

order by t1.task_alloc DESC

 

Here is a sample output.

 

session_id request_id  task_alloc           task_dealloc

———————————————————

52         0           1024                 1024

sql_handle                                                                   statement_start_offset

———————————————————————–

0x02000000D490961BDD2A8BE3B0FB81ED67655EFEEB360172   356

 

statement_end_offset  plan_handle

———————————

-1                    0x06000500D490961BA8C19503000000000000000000000000

 

You can use the sql_handle and plan_handle to get the SQL statement and the query plan as follows:

 

select text from sys.dm_exec_sql_text(@sql_handle)

select * from sys.dm_exec_query_plan(@plan_handle)

Advertisements
  1. February 14, 2013 at 9:58 pm

    Reblogged this on Rajib Kundu SQL DBA Blog.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: