July 25, 2017 at 7:50 am
Hi Guys
I have the below query which is part of a view. This query takes > 5 minutes to complete.
SELECT
td.technical_details_id,
spt.[Account Name] AS [Customer],
td.mozart_ref_no AS [FeasibilityRef],
td.spt_ref_no,
fr.expected_completion_dttm AS [ECD],
fr.actual_completion_dttm AS [CompletionDate],
fs.name AS [Status],
ft.name AS [FeasibilityTeam],
CASE dbo.ufn_IsWithSLA(ft.feasibility_team_id, fr.expected_completion_dttm, fr.actual_completion_dttm)
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END is_within_sla,
CASE ISNULL(fr.is_escalation, 0)
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END is_escalation,
fc.name AS [Complexity],
dbo.[ufn_GetPlannersName](fr.feasibility_request_id) AS [Planner],
fr.created_dttm AS [RaisedDate]
FROM dbo.tbTechnical_Details td
INNER JOIN dbo.tbFeasibility_Request fr
ON td.technical_details_id = fr.technical_details_id
INNER JOIN dbo.tbFeasibility_Status fs
ON fr.feasibility_status_id = fs.feasibility_status_id
INNER JOIN dbo.tbFeasibility_Complexity fc
ON fr.feasibility_complexity_id = fc.feasibility_complexity_id
INNER JOIN dbo.tbFeasibility_Team ft
ON fr.feasibility_team_id = ft.feasibility_team_id
INNER JOIN dbo.tbSPT spt
ON td.spt_ref_no = spt.[Opportunity Reference]
There are no where clause on this query which is one part of the issue and there are index scans, and the expensive sort is on the Clustered key - technical_details_id which i am struggling to get rid of.
Is there anything that can be done to tune this query?
Please find below the execution plan.
Many thanks in advance.
July 25, 2017 at 8:09 am
Try adding a covering index in the sort order to eliminate that expensive sort, almost half the cost. Also I would suggest reviewing the indices, when ever I see anything called _dta_xx, I'll step back and do index usage analysis etc.
😎
July 25, 2017 at 8:32 am
Eirikur Eiriksson - Tuesday, July 25, 2017 8:09 AMTry adding a covering index in the sort order to eliminate that expensive sort, almost half the cost. Also I would suggest reviewing the indices, when ever I see anything called _dta_xx, I'll step back and do index usage analysis etc.
😎
I have tried with these covering indexes:
CREATE NONCLUSTERED INDEX [IX_tbTechnical_Details_sp_ref_no_incl] ON [dbo].[tbTechnical_Details]
(
[spt_ref_no] ASC
)
INCLUDE ( [mozart_ref_no]
)
CREATE
NONCLUSTERED INDEX [idx_feasibility_request_id_incl] ON [dbo].[tbFeasibility_Request_Response]
(
[feasibility_request_id] ASC
)
INCLUDE ( [planner_user_id_site_a])
CREATE NONCLUSTERED INDEX [Ix_opportunityReference_Accountname] ON [dbo].[tbSPT]
(
[Opportunity Reference] ASC,
[Account Name] ASC
)
I do understand about the index created by the DTA ( It was already present in this table, created by the previous DBA/Devloper).. I created the following index to satify the query.. However, it hasn't made a huge difference.
CREATE NONCLUSTERED INDEX [Ix_technical_details_id_feasibility_status_id_feasibility_complexity_id_feasibility_team_id_incl] ON [dbo].[tbFeasibility_Request]
(
[technical_details_id] ASC,
[feasibility_status_id] ASC,
[feasibility_complexity_id] ASC,
[feasibility_team_id] ASC
)
INCLUDE ( [expected_completion_dttm],
[actual_completion_dttm],
[created_dttm],
[is_escalation]) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = on, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
July 25, 2017 at 10:52 am
the execution plan attached. The sort is still there. It is on the clustering key - technical_details_id.
July 25, 2017 at 11:07 am
You could try adding technical_details_id to this index.
CREATE NONCLUSTERED INDEX [IX_tbTechnical_Details_sp_ref_no_incl] ON [dbo].[tbTechnical_Details]
(
technical_details_id,
[spt_ref_no] ASC
)
INCLUDE ( [mozart_ref_no]
)
July 25, 2017 at 11:30 am
ZZartin - Tuesday, July 25, 2017 11:07 AMYou could try adding technical_details_id to this index.CREATE NONCLUSTERED INDEX [IX_tbTechnical_Details_sp_ref_no_incl] ON [dbo].[tbTechnical_Details]
(
technical_details_id,
[spt_ref_no] ASC)
INCLUDE ( [mozart_ref_no]
)
technical_details_id is clustered index column, so it's part of the non-clustered index anyway. Is it really required to add this column?
Anyway, I added the technical_details_id, but the sort is still there. 🙁
July 25, 2017 at 12:33 pm
That's not surprising.
The sort is for the results of the merge join between tbSPT and tbTechnical_Details, which is on spt_ref_no=[Opportunity Reference]. That output will be ordered by the value of those columns, so to do a subsequent merge join on technical_details_id will require re-sorting the output by that, no matter whether there's an index on techincal_details_id or not.
If the join on technical_details_id were performed first, then such an index could be used, and THAT sort would be avoided, but then any subsequent merge joins on a different column would require a sort.
If you really just want to get rid of the sort, you could force a hash join, since it doesn't require sorted inputs. Of course, you have to be careful for a couple reasons.
First, SQL Server likely considered such a plan, and estimated that it would be more expensive. It might be wrong, but it might also be right, and the plan with the sort+merge join might be the best option.
Second, using a join hint will force the exact join order you've written, so SQL Server can't rearrange the inputs to a more efficient order if one is available.
Also, keep in mind that the costs are just estimates; it's likely the sort is indeed the most expensive part of the plan, but that should not just be blindly assumed based on the estimated cost.
Finally, >5 minutes seems quite high for the number and size of rows being processed.
What are the specs of this server? Any chance you could collect wait stats for this query while it's running?
Cheers!
July 25, 2017 at 2:14 pm
In addition to what other's have said, the inclusion of a scalar valued function isn't doing you any favors either...dbo.[ufn_GetPlannersName](fr.feasibility_request_id) AS [Planner],
You should see a nice performance boost if you were to recreate that function as an inline table valued function.
July 25, 2017 at 2:35 pm
Ah, nice catch, Jason.
Yeah, there are a couple different functions in that query (in addition to the one you pointed out, there's a reference to dbo.ufn_IsWithSLA). Since the query plan indicates they're not inlined, if they're data accessing then that could explain why the reported duration seems so excessive for processing so few rows of those sizes.
It would also illustrate what I was mentioning about not blindly trusting the estimated costs. If those ARE data accessing (and the one Jason pointed out almost certainly is, from the name) then they're almost certainly the biggest problem with the query; the sort is just a poor red herring 🙂
@ss-457805: Could you post the definitions of those two functions?
Cheers!
July 25, 2017 at 2:48 pm
Jacob Wilkins - Tuesday, July 25, 2017 2:35 PMAh, nice catch, Jason.Yeah, there are a couple different functions in that query (in addition to the one you pointed out, there's a reference to dbo.ufn_IsWithSLA). Since the query plan indicates they're not inlined, if they're data accessing then that could explain why the reported duration seems so excessive for processing so few rows of those sizes.
It would also illustrate what I was mentioning about not blindly trusting the estimated costs. If those ARE data accessing (and the one Jason pointed out almost certainly is, from the name) then they're almost certainly the biggest problem with the query; the sort is just a poor red herring 🙂
@ss-457805: Could you post the definition of those two functions?
Cheers!
Yea... It's tough to see what's going on when the code markup isn't used... I looked at it twice and completely missed the scalar function in the case expression.
July 25, 2017 at 3:38 pm
Jason A. Long - Tuesday, July 25, 2017 2:14 PMIn addition to what other's have said, the inclusion of a scalar valued function isn't doing you any favors either...dbo.[ufn_GetPlannersName](fr.feasibility_request_id) AS [Planner],
You should see a nice performance boost if you were to recreate that function as an inline table valued function.
Thanks Jason for pointing this out. I changed the Scalar function to table value function. It now returns in 15 secs instead ~ 5minutes. But I do not understand why? This scalar function is simple and direct function and it's not that the optimiser had to unpack a lot of stuff. Please see below definitions:
CREATE FUNCTION [dbo].[ufn_GetPlannersName](@intFeasibilityRequestId BigInt)
RETURNS NVarChar(1000)
AS
BEGIN
DECLARE @Ret NVarChar(1000)
SELECT TOP 1 @Ret = u.full_name
FROM tbFeasibility_Request_Response frr
INNER JOIN tbUser u
ON frr.planner_user_id_site_a = u.[user_id]
WHERE frr.feasibility_request_id = @intFeasibilityRequestId
RETURN @Ret
END
CREATE FUNCTION [dbo].[ufn_IsWithSLA](@teamId Int, @datExpectedDate DateTime, @datActualCompletionDate DateTime)
RETURNS Bit
AS
BEGIN
DECLARE @bitRet Int
-- Currently this function is not using team id but in future
-- if different teams have got different requirement then we could
-- use this parameter
IF (DATEDIFF(dd, @datExpectedDate, @datActualCompletionDate) > 0)
SET @bitRet = 0
ELSE
SET @bitRet = 1
RETURN @bitRet
END
GO
I only changed this - dbo.[ufn_GetPlannersName](fr.feasibility_request_id) to inline table valued function.
Please see the execution plan below
July 25, 2017 at 3:50 pm
CREATE FUNCTION [dbo].[ufn_GetPlannersName]
(
@intFeasibilityRequestId BIGINT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT TOP 1
Ret = u.full_name
FROM
tbFeasibility_Request_Response frr
JOIN tbUser u
ON frr.planner_user_id_site_a = u.[user_id]
WHERE
frr.feasibility_request_id = @intFeasibilityRequestId;
GO
--============================================================================
CREATE FUNCTION [dbo].[ufn_IsWithSLA]
(
@teamId Int,
@datExpectedDate DateTime,
@datActualCompletionDate DATETIME
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
-- Currently this function is not using team id but in future
-- if different teams have got different requirement then we could
-- use this parameter
SELECT bitRet = CASE WHEN DATEDIFF(dd, @datExpectedDate, @datActualCompletionDate) > 0 THEN 0 ELSE 1 END;
GO
July 25, 2017 at 3:51 pm
Next question... Are you familiar with the syntax differences between scalar & table functions, with regard to referencing them in your code?
July 26, 2017 at 1:44 am
Jason A. Long - Tuesday, July 25, 2017 3:51 PMNext question... Are you familiar with the syntax differences between scalar & table functions, with regard to referencing them in your code?
Personally, I do not like the use of functions.. If I can, I will avoid. Yes, I am familiar with the syntax. This is what I have chnaged now on the script.
SELECT
td.technical_details_id,
spt.[Account Name] AS [Customer],
td.mozart_ref_no AS [FeasibilityRef],
td.spt_ref_no,
fr.expected_completion_dttm AS [ECD],
fr.actual_completion_dttm AS [CompletionDate],
fs.name AS [Status],
ft.name AS [FeasibilityTeam],
CASE (select * from dbo.ufn_IsWithSLA(ft.feasibility_team_id, fr.expected_completion_dttm, fr.actual_completion_dttm))
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END is_within_sla,
CASE ISNULL(fr.is_escalation, 0)
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END is_escalation,
fc.name AS [Complexity],
(select * from [dbo].[ufn_GetPlannersName](fr.feasibility_request_id)) AS [Planner],
fr.created_dttm AS [RaisedDate]
FROM dbo.tbTechnical_Details td
INNER JOIN dbo.tbFeasibility_Request fr
ON td.technical_details_id = fr.technical_details_id
INNER JOIN dbo.tbFeasibility_Status fs
ON fr.feasibility_status_id = fs.feasibility_status_id
INNER JOIN dbo.tbFeasibility_Complexity fc
ON fr.feasibility_complexity_id = fc.feasibility_complexity_id
INNER JOIN dbo.tbFeasibility_Team ft
ON fr.feasibility_team_id = ft.feasibility_team_id
INNER JOIN dbo.tbSPT spt
ON td.spt_ref_no = spt.[Opportunity Reference]
Changing both both the functions to TVF brings the result back in 8 secs.
July 26, 2017 at 7:59 am
ss-457805 - Wednesday, July 26, 2017 1:44 AMJason A. Long - Tuesday, July 25, 2017 3:51 PMNext question... Are you familiar with the syntax differences between scalar & table functions, with regard to referencing them in your code?Changing both both the functions to TVF brings the result back in 8 secs.
From 5 mins + to 8 secs... I'd call that a win! 🙂
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply