Execution Plan XML for the Query
I am completely new for query tuning in SQL Server. I have gone through many online resources like how to interpret Execution Plan of a query etc to start working on and get familiar to query tuning process, but when I am trying to hands-on on a simple query in my environment, I am getting stuck and confused. I am sharing the query which I am trying to make fast. Currently It is taking around 10 mins to run but not fetching any record.
Also wanted to know how to identify when is the actual need of tuning a query as sometimes it can be complete time waste. Are there parameters to decide on that?
Any suggestions/help regarding the approach/steps I should follow, or how should i look into the query to make it faster is highly appreciated.
Please let me know for any clarification or any info needed. Thanks in advance!
Declare @Date1 datetime
Declare @Date2 datetime
select @Date1 =cast(replace(convert(varchar,getdate()-7,102),'.','-') + '00:00:00.000' as datetime)
select @Date2 =cast(replace(convert(varchar,getdate()-1,102),'.','-') + ' 23:59:59.000' as datetime)
select a.ticket_number,a.type,a.parent,a.summary,a.open_date,
CASE CAST((CONVERT(VARCHAR(10),(DATEDIFF(DAY,GETDATE()-21,a.OPEN_DATE)/7)+1)) AS INT)
WHEN 3 THEN 'Current'
ELSE 'Previous'
END as 'Open in Current / Previous week',
a.last_mod_date,a.resolve_date,a.close_date,a.ass_group,
a.opened_by,a.assignee,a.end_user,a.requestor,a.priority,a.ticket_status,a.urgency,a.category,a.SLA_violation,a.CI,
a.others,a.rootcause,e.[Apps / Infra],e.[BU / Tower],e.[L1 / L2 / L3],e.Owner,e.[SDP Group Name],e.Service,e.[Sub BU / Tower],
(dateadd(ss,(c.time_stamp),'1970-01-01')) as Child_ticket_tag_date,
'week'+convert(varchar(10),(DATEDIFF(day,GETDATE()-21,DATEADD(ss,((c.time_stamp)),'1970-01-01'))/7)+1) as weeknum,
c.action_desc
into #main
from current_ticket_details (nolock) as a join SDP_Master_apps (nolock) e on a.ass_group=e.[SDP Group Name]
left join Call_Req (nolock) as b on a.ticket_number = b.ref_num
left join act_log c (nolock) on b.persid = c.call_req_id
where DATEADD(ss, ((c.time_stamp)),'1970-01-01') between @Date1 and @Date2
and (c.action_desc LIKE '%PARENT%' )
and a.assignee not in (select email_id from mail_details where frequency ='ALL' and tower = 'APPS' and parm_name = 'exclusion_list')
and a.type in ('I','R','CO')
and a.ticket_status not in ('test','cancelled')
and a.parent is not NULL
order by c.time_stamp
Also Sharing the set statistics IO and time output
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 4 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'act_log'. Scan count 28882, logical reads 2631931, physical reads 21343, read-ahead reads 78790, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'call_req'. Scan count 0, logical reads 220863, physical reads 14239, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'mail_details'. Scan count 2, logical reads 357920, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'current_ticket_details'. Scan count 1, logical reads 140156, physical reads 1093, read-ahead reads 94182, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SDP_master_Apps'. Scan count 1, logical reads 8, physical reads 8, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(0 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 19579 ms, elapsed time = 800395 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
I created a non clustered index on current_ticket_details table on columns mentioned in WHERE clause along with including those columns which are present in SELECT Clause. Hence the optimizer considered Index Scan(Non clustered) instead of clustered scan. It reduced the estimated operator cost a bit but execution time is still the same.
create nonclustered index ind_ctd_type_stat_ass_parent_include
on current_ticket_details(type,ticket_status,assignee,parent)
include(summary,open_date,last_mod_date,resolve_date,close_date,ass_group,
opened_by,end_user,requestor,priority,urgency,category,SLA_violation,CI,
others,rootcause)
ORDER BY
clause doesn't always block output until all the data is available (though in less than simple cases like this it usually does). If the query planner can arrange to scan the main source inORDER BY
order and there is nothing else that causes sort operations (window functions, some join operations) along the way, then there will be no need to resort so it can start presenting rows early.