March 18, 2016 at 2:12 pm
OK, this is driving me nuts. I have a query that runs slowly once a day (2 minutes) and then runs fast every time (2-3 seconds).
It's a report that queries 20 tables, it has 2 derived tables to get last row of a child table and it's run as dynamic SQL because it's a catch-all-query.
I'm not sure if there's room for improvement on code (maybe pre-calculate the derived tables) or if it's mostly a configuration issue.
I'm attaching the anonymized execution plan and query with table names changed.
WITH cteAllTasks AS(
SELECT DISTINCT
v.PatientVisitID AS PatientVisitId
,at.AccountTaskID AS AccountTaskId
,t.TaskName
,v.PatientVisitAccount
,(originator.FirstName + ' ' + originator.LastName) AS Originator
,CAST(at.CreationDate AS DATE) AS CreationDate
,CAST(at.AssignedDate AS DATE) AS AssignedDate
,CASE
WHEN at.IsGroupTask = 1
AND ts.TaskStatusCode IN ('T','ET','RT','CT')
--DE17585
THEN ISNULL(g.GroupName, (assignee.FirstName + ' ' + assignee.LastName))
ELSE (assignee.FirstName + ' ' + assignee.LastName)
END Assignee
,TaskStatusCode = vwts.TaskStatusCode
,TaskStatusCodeDesc = CASE
WHEN ts.TaskStatusCode = 'RT'
AND at.IsAssingeeCompleted = 0
THEN ISNULL('RT - Unable to Complete', '')
ELSE vwts.TaskStatusCodeDesc
END
,DurationEndDay = CASE
WHEN ts.TaskStatusCode IN ('T','ET')
THEN DATEADD(d, t.Duration, at.AssignedDate)
ELSE NULL
END
,IsTaskCompleted = CASE
WHEN ts.TaskStatusCode = 'RT'
AND at.IsAssingeeCompleted = 1
THEN 'Yes'
WHEN ts.TaskStatusCode = 'CLD'
THEN 'Yes'
ELSE 'No'
END
,CompletionDate = CASE
WHEN at.IsAssingeeCompleted = 1
THEN CAST(at.AssignedDate AS DATE)
ELSE NULL
END
,CASE
WHEN at.ClaimExtID = 0
THEN 'Account'
ELSE 'Claim'
END TaskType
,tg.TaskGroupName AS TaskGroup
,CASE
WHEN at.ClaimExtID <> 0
THEN c.ClaimNumber
ELSE NULL
END ClaimNumber
,CASE
WHEN at.ClaimExtID = 0
THEN ft.CurrentAccountBalance
ELSE cd.CurBal
END AS ItemBalance
,o.OrgLevel1ID AS Organization
,o.OrgLevel1Name AS OrgName
---US15005, Added ClaimsExtId,WorkListId,TaskId
,at.ClaimExtID AS ClaimsExtId
,A.SecurityObjectID AS WorkListId
,at.TaskID
--US17322 ,DE17731
,ISNULL(pvm.MemoText, '') AS TaskNotes
--US17360, ActiveHealthPlan
,ISNULL(hp.HealthPlanName, '') ActiveHealthPlan
FROM Object1 at
JOIN Object2 t ON at.TaskID = t.TaskID
JOIN Object3 tg ON t.TaskGroupID = tg.TaskGroupID
JOIN Object6 ts ON ts.TaskStatusID = at.TaskStatusID
JOIN Object4 v ON v.PatientVisitID = at.PatientVisitID
JOIN Object10 ft ON at.PatientVisitID = ft.PatientVisitID
JOIN [dbo].[vw_TaskStatus] vwTS ON at.TaskStatusID = vwTS.TaskStatusID
LEFT JOIN (
SELECT WQ.PatientVisitID
,MAX(WQ.SecurityObjectID) AS SecurityObjectID
FROM Object18 wq(NOLOCK)
GROUP BY WQ.PatientVisitID
) AS A ON A.PatientVisitID = v.PatientVisitID
LEFT JOIN Object17 ce ON at.ClaimExtID = ce.ClaimsExtID
LEFT JOIN Object9 c ON ce.ClaimID = c.ClaimID
LEFT JOIN Object11 cd ON c.ClaimID = cd.ClaimID
LEFT JOIN Object5 o ON v.OrganizationID = o.OrgLevel1ID
LEFT JOIN Object8 originator ON originator.UserID = at.AssignerUserID
LEFT JOIN Object8 assignee ON assignee.UserID = at.AssigneeUserID
LEFT JOIN Object7 g ON g.GroupID = at.AssigneeUserID
--US17360, Added Below tblClaimOfPatientVisit and vw_ActiveHealthPlans
LEFT JOIN Object16 copv ON at.PatientVisitID = copv.PatientVisitID
AND copv.IsMostCurrent = 1
LEFT JOIN Object9 c1 ON copv.ClaimID = c1.ClaimID
AND copv.COBCode = c1.COBCode
AND copv.PatientVisitID = c1.PatientVisitID
LEFT JOIN Object15 hp ON c1.HealthPlanID = hp.HealthPlanID
AND hp.Active = 1
--DE17731
LEFT JOIN (
SELECT at1.AccountTaskID
,pvah.PatientVisitID
,MAX(pvah.MemoID) AS MemoID
FROM Object1 at1(NOLOCK)
JOIN Object14 ath(NOLOCK) ON ath.AccountTaskID = at1.AccountTaskID
JOIN Object13 pvah(NOLOCK) ON at1.PatientVisitID = pvah.PatientVisitID
AND pvah.ActionFocusID = ath.AccountTaskHistoryID
GROUP BY at1.AccountTaskID
,pvah.PatientVisitID
) AS M ON M.AccountTaskID = at.AccountTaskID
LEFT JOIN Object12 pvm ON M.MemoID = PVM.MemoID
WHERE at.TaskTypeId = 3
)
SELECT ROW_NUMBER() OVER( ORDER BY CASE WHEN TaskStatusCode = 'CLD' THEN 2 ELSE 1 END, CreationDate DESC) AS TempTaskId
,PatientVisitId
,AccountTaskId
,TaskType
,TaskName
,TaskGroup
,PatientVisitAccount
,Originator
,CreationDate
,AssignedDate
,Assignee
,TaskStatusCode
,TaskStatusCodeDesc
,DurationEndDay
,IsTaskCompleted
,CompletionDate
,ClaimNumber
,ItemBalance
,OrgName AS Organization
,ClaimsExtId
,ISNULL(WorkListId,0) AS WorkListId
,TaskId
,TaskNotes
,ActiveHealthPlan
FROM cteAllTasks
WHERE Organization IN (19,14,33,12,9,31,0,8,13,10,3,2,1,6,5,4,17,18,21,27,32,23,34,15,26,16,22,7,20,30,28,25,24,29)
ORDER BY TempTaskId;
I know that DDL for tables and indexes might be required, but I hope I could get some guidance just with this.
March 18, 2016 at 2:35 pm
Luis,
you might be chasing Red Herrings here, does this happen just after the full backups?
😎
March 18, 2016 at 2:43 pm
Eirikur Eiriksson (3/18/2016)
Luis,you might be chasing Red Herrings here, does this happen just after the full backups?
😎
Define "just after the full backups". It happens when the query is first run during the day, it could be at noon. The full backup occurs at 1am.
March 18, 2016 at 2:57 pm
Luis Cazares (3/18/2016)
Eirikur Eiriksson (3/18/2016)
Luis,you might be chasing Red Herrings here, does this happen just after the full backups?
😎
Define "just after the full backups". It happens when the query is first run during the day, it could be at noon. The full backup occurs at 1am.
What I often see is the flush of the buffer pool during backups and obviously the first execution of such a query means reading the pages from disk again. This could happen i.e. when a backup agent is encrypting the backups etc.
😎
March 18, 2016 at 3:02 pm
Eirikur Eiriksson (3/18/2016)
Luis Cazares (3/18/2016)
Eirikur Eiriksson (3/18/2016)
Luis,you might be chasing Red Herrings here, does this happen just after the full backups?
😎
Define "just after the full backups". It happens when the query is first run during the day, it could be at noon. The full backup occurs at 1am.
What I often see is the flush of the buffer pool during backups and obviously the first execution of such a query means reading the pages from disk again. This could happen i.e. when a backup agent is encrypting the backups etc.
😎
Should I "warm-up the cache"? Seems like a dirty fix.
March 18, 2016 at 3:42 pm
Luis Cazares (3/18/2016)
Eirikur Eiriksson (3/18/2016)
Luis Cazares (3/18/2016)
Eirikur Eiriksson (3/18/2016)
Luis,you might be chasing Red Herrings here, does this happen just after the full backups?
😎
Define "just after the full backups". It happens when the query is first run during the day, it could be at noon. The full backup occurs at 1am.
What I often see is the flush of the buffer pool during backups and obviously the first execution of such a query means reading the pages from disk again. This could happen i.e. when a backup agent is encrypting the backups etc.
😎
Should I "warm-up the cache"? Seems like a dirty fix.
Down right "Bad to the bone" but it well may work in these cases, probably the equivalent of having a bottle of Guzano Rojo Mezcal before going to the bar:-D
😎
March 18, 2016 at 3:42 pm
Run your query first thing in the morning with these options enabled, then you can tell if it's cache or not.
set statistics io on
set statistics time on
March 19, 2016 at 4:51 am
Since it is repeatably the first-run-of-the-day perf problem then fine AND it is dynamic SQL it REALLY sounds like a disk-IO-slowness issue. As someone said, track the IOs yourself early on to see how many (and slow) the physical IO is.
If it weren't dynamic SQL it could be a slow-compilation issue, but that's not the case here.
BTW, do you have anything that is flushing the buffer pool by any chance? Just curious. I would be really looking at that if this happened very early in the morning after batch jobs ran (checkdb is NOTORIOUS for some MASSIVE memory grants that can wipe your buffer pool), but again not the case here since the first slow execution can be mid-day.
Good on you for doing dynamic SQL for catch-all query!! Are you making sure to not even join to any tables you don't need to hit because they aren't filtering and you don't need any other columns from them? I have seen that missed in this scenario.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 21, 2016 at 1:03 pm
The major difference from the 2 runs seems to be the physical reads from a table. When it's slow, it's reading 12-13 thousand pages from disk. When it's fast, it only has logical reads. This table is the one "renamed" as Object12 which has a horrible join that comes from a bad design.
I'll have to figure out something to make this work efficiently.
Thank you for your help.
Are you making sure to not even join to any tables you don't need to hit because they aren't filtering and you don't need any other columns from them? I have seen that missed in this scenario.
That was one of my first checks. All tables are used either in the column list or used to join other tables that are used in the column list.
March 21, 2016 at 1:42 pm
Luis Cazares (3/21/2016)
The major difference from the 2 runs seems to be the physical reads from a table. When it's slow, it's reading 12-13 thousand pages from disk. When it's fast, it only has logical reads. This table is the one "renamed" as Object12 which has a horrible join that comes from a bad design.I'll have to figure out something to make this work efficiently.
Thank you for your help.
Are you making sure to not even join to any tables you don't need to hit because they aren't filtering and you don't need any other columns from them? I have seen that missed in this scenario.
That was one of my first checks. All tables are used either in the column list or used to join other tables that are used in the column list.
Luis, have you checked any memory consuming jobs on the server such as SSIS packages, DBCC Checkdb or encrypting data backup agents? Is it an option to add memory to the server?
😎
March 22, 2016 at 2:44 am
Luis Cazares (3/18/2016)
OK, this is driving me nuts. I have a query that runs slowly once a day (2 minutes) and then runs fast every time (2-3 seconds).It's a report that queries 20 tables, it has 2 derived tables to get last row of a child table and it's run as dynamic SQL because it's a catch-all-query.
I'm not sure if there's room for improvement on code (maybe pre-calculate the derived tables) or if it's mostly a configuration issue.
I'm attaching the anonymized execution plan and query with table names changed.
WITH cteAllTasks AS(
SELECT DISTINCT
v.PatientVisitID AS PatientVisitId
,at.AccountTaskID AS AccountTaskId
,t.TaskName
,v.PatientVisitAccount
,(originator.FirstName + ' ' + originator.LastName) AS Originator
,CAST(at.CreationDate AS DATE) AS CreationDate
,CAST(at.AssignedDate AS DATE) AS AssignedDate
,CASE
WHEN at.IsGroupTask = 1
AND ts.TaskStatusCode IN ('T','ET','RT','CT')
--DE17585
THEN ISNULL(g.GroupName, (assignee.FirstName + ' ' + assignee.LastName))
ELSE (assignee.FirstName + ' ' + assignee.LastName)
END Assignee
,TaskStatusCode = vwts.TaskStatusCode
,TaskStatusCodeDesc = CASE
WHEN ts.TaskStatusCode = 'RT'
AND at.IsAssingeeCompleted = 0
THEN ISNULL('RT - Unable to Complete', '')
ELSE vwts.TaskStatusCodeDesc
END
,DurationEndDay = CASE
WHEN ts.TaskStatusCode IN ('T','ET')
THEN DATEADD(d, t.Duration, at.AssignedDate)
ELSE NULL
END
,IsTaskCompleted = CASE
WHEN ts.TaskStatusCode = 'RT'
AND at.IsAssingeeCompleted = 1
THEN 'Yes'
WHEN ts.TaskStatusCode = 'CLD'
THEN 'Yes'
ELSE 'No'
END
,CompletionDate = CASE
WHEN at.IsAssingeeCompleted = 1
THEN CAST(at.AssignedDate AS DATE)
ELSE NULL
END
,CASE
WHEN at.ClaimExtID = 0
THEN 'Account'
ELSE 'Claim'
END TaskType
,tg.TaskGroupName AS TaskGroup
,CASE
WHEN at.ClaimExtID <> 0
THEN c.ClaimNumber
ELSE NULL
END ClaimNumber
,CASE
WHEN at.ClaimExtID = 0
THEN ft.CurrentAccountBalance
ELSE cd.CurBal
END AS ItemBalance
,o.OrgLevel1ID AS Organization
,o.OrgLevel1Name AS OrgName
---US15005, Added ClaimsExtId,WorkListId,TaskId
,at.ClaimExtID AS ClaimsExtId
,A.SecurityObjectID AS WorkListId
,at.TaskID
--US17322 ,DE17731
,ISNULL(pvm.MemoText, '') AS TaskNotes
--US17360, ActiveHealthPlan
,ISNULL(hp.HealthPlanName, '') ActiveHealthPlan
FROM Object1 at
JOIN Object2 t ON at.TaskID = t.TaskID
JOIN Object3 tg ON t.TaskGroupID = tg.TaskGroupID
JOIN Object6 ts ON ts.TaskStatusID = at.TaskStatusID
JOIN Object4 v ON v.PatientVisitID = at.PatientVisitID
JOIN Object10 ft ON at.PatientVisitID = ft.PatientVisitID
JOIN [dbo].[vw_TaskStatus] vwTS ON at.TaskStatusID = vwTS.TaskStatusID
LEFT JOIN (
SELECT WQ.PatientVisitID
,MAX(WQ.SecurityObjectID) AS SecurityObjectID
FROM Object18 wq(NOLOCK)
GROUP BY WQ.PatientVisitID
) AS A ON A.PatientVisitID = v.PatientVisitID
LEFT JOIN Object17 ce ON at.ClaimExtID = ce.ClaimsExtID
LEFT JOIN Object9 c ON ce.ClaimID = c.ClaimID
LEFT JOIN Object11 cd ON c.ClaimID = cd.ClaimID
LEFT JOIN Object5 o ON v.OrganizationID = o.OrgLevel1ID
LEFT JOIN Object8 originator ON originator.UserID = at.AssignerUserID
LEFT JOIN Object8 assignee ON assignee.UserID = at.AssigneeUserID
LEFT JOIN Object7 g ON g.GroupID = at.AssigneeUserID
--US17360, Added Below tblClaimOfPatientVisit and vw_ActiveHealthPlans
LEFT JOIN Object16 copv ON at.PatientVisitID = copv.PatientVisitID
AND copv.IsMostCurrent = 1
LEFT JOIN Object9 c1 ON copv.ClaimID = c1.ClaimID
AND copv.COBCode = c1.COBCode
AND copv.PatientVisitID = c1.PatientVisitID
LEFT JOIN Object15 hp ON c1.HealthPlanID = hp.HealthPlanID
AND hp.Active = 1
--DE17731
LEFT JOIN (
SELECT at1.AccountTaskID
,pvah.PatientVisitID
,MAX(pvah.MemoID) AS MemoID
FROM Object1 at1(NOLOCK)
JOIN Object14 ath(NOLOCK) ON ath.AccountTaskID = at1.AccountTaskID
JOIN Object13 pvah(NOLOCK) ON at1.PatientVisitID = pvah.PatientVisitID
AND pvah.ActionFocusID = ath.AccountTaskHistoryID
GROUP BY at1.AccountTaskID
,pvah.PatientVisitID
) AS M ON M.AccountTaskID = at.AccountTaskID
LEFT JOIN Object12 pvm ON M.MemoID = PVM.MemoID
WHERE at.TaskTypeId = 3
)
SELECT ROW_NUMBER() OVER( ORDER BY CASE WHEN TaskStatusCode = 'CLD' THEN 2 ELSE 1 END, CreationDate DESC) AS TempTaskId
,PatientVisitId
,AccountTaskId
,TaskType
,TaskName
,TaskGroup
,PatientVisitAccount
,Originator
,CreationDate
,AssignedDate
,Assignee
,TaskStatusCode
,TaskStatusCodeDesc
,DurationEndDay
,IsTaskCompleted
,CompletionDate
,ClaimNumber
,ItemBalance
,OrgName AS Organization
,ClaimsExtId
,ISNULL(WorkListId,0) AS WorkListId
,TaskId
,TaskNotes
,ActiveHealthPlan
FROM cteAllTasks
WHERE Organization IN (19,14,33,12,9,31,0,8,13,10,3,2,1,6,5,4,17,18,21,27,32,23,34,15,26,16,22,7,20,30,28,25,24,29)
ORDER BY TempTaskId;
I know that DDL for tables and indexes might be required, but I hope I could get some guidance just with this.
This version of the query show an optimiser timeout, so you can't be sure that the plan you get is the best that the optimiser can generate. You need to simplify the query and I'd begin by extracting out those derived tables as #temp tables.
There are several key lookups - I counted at least three. That's needless extra reads. Is your indexing strategy falling a little behind data changes? You may well find other indexing gains if it is, and these will reduce the data footprint of the query. A smaller data footprint reduces the likelihood of data falling out of the cache.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply