I have inherited a system and have been looking into a SQL Agent job that was running in around 15-20 mins that is now failing to complete. The SQL Server was moved from a Physical to Virtual environment. On the virtual environment the job was running about the same time as on the Physical environment. That was until a few days ago when it wasn't completing. Looking at the job one of the steps is executing a stored proc. Within that Proc it's then Inserting everything from a view into a temp table as well as other tables from the other database on the server. Looking at the view is making me ill, as it looks like it's not fit for purpose. I did a SELECT TOP 10 * FROM the view and it took 54 mins to run. I noticed that the TempDB Drive was flatlining when we ran this query and that the latency was up in the hundreds for Read/Write. I noticed that the estimate and actual rows was way out and updated the stats on all tables in the view. Funny enough I have noticed now the latency has dropped down since doing this. I have ran Crystal Disk Mark on the old physical (Left) and this new virtual server (right). [![enter image description here][1]][1] There seems to be a significant difference in the Sequential Read/Writes and differences in the Random Read/Writes between the H:\ Drive on the old Phsical and the T:\ Drive on the new virtual. The Random Reads seem to be performing better on the new server but the writes are slower. I’ve highlighted these drives as this is where the TempDB is situated on both servers. I'm in a fight with the SAN guys at the moment, as they are saying everything is fine at their end, even though I've graphed the perfmon files and can show that disk latency wen't through the roof last week on a specific day and not sure if it's coincidence or not but the jobs that are not completing started to fail after this spike in the SAN. Disk Latency is averaging now about 80-100 ms, so way over the 20ms acceptable level. I've uploaded the plan to paste the plan https://www.brentozar.com/pastetheplan/?id=ryUlA0pjz Does anyone have any tips on how to tune this query please? It makes me feel faint just looking at this and knowing it's probably in other stored procs across the estate :( *UPDATE. Sorry not sure if paste the plan shows the rows. Yes it's Selecting 100 million rows to bring back 10 :( [![enter image description here][2]][2] *Added in the Definition for the View `/* Removed comments to protect the guilty */ CREATE VIEW [dbo].[vw_PPM_CompletionSummary_Resource_ID_Added] AS SELECT SD.fld_str_Name AS 'Division', DM.fld_str_Name AS 'DivManager', SC.fld_str_Name AS 'Cluster', OM.fld_str_Name AS 'OpsManager', S.fld_int_ID AS 'StoreID', S.fld_str_Name AS 'Store', ST.fld_str_Description AS 'StoreType', PT.fld_str_Name AS 'PPMType', PT.fld_int_ID AS 'PPMTypeID', MT.fld_str_Name AS 'Discipline', MT.fld_int_ID AS 'DisciplineID', SL.fld_int_ID AS 'PPMRef', SL.fld_dat_WeekEnding AS 'WeekEnding', WN.WeekNumber AS 'SchedWeekNo', YEAR(SL.fld_dat_WeekEnding) AS 'SchedYear', 1 AS 'PPMDue', -- case when SL.fld_int_StatusID = 2 then 1 else 0 end as 'PPMComplete', CASE WHEN SL.fld_int_StatusID = 2 THEN 1 WHEN ProjComp.ProjComp IS NOT NULL THEN 1 ELSE 0 END AS 'PPMComplete', CASE WHEN PT.fld_int_ID IN (4,5,7,6,99,97,407,408) THEN 1 ELSE 0 END AS 'IncludeInKPI', --Bakery,Catering,HVAC Service,Refrigeration,PPM-Call Point Testing, PPM-Sprinkler Valve Weekly Test, Catering Minor, Bakery Minor CASE WHEN PT.fld_int_ResourceTypeID IN (1,2,3) THEN 1 ELSE 0 END AS 'CityConfiguredPPMType', --IST,Engineer,Alliance CASE WHEN PT.fld_int_ResourceID IN (SELECT fld_int_ID FROM Prod.dbo.tblHelpDeskResource (NOLOCK) WHERE fld_str_Name LIKE 'jungh%') THEN 1 ELSE 0 END AS 'JungheinrichConfiguredPPM', RD.CalloutEngineerID, RD.CalloutEngineer AS 'CalloutEngineer', RD.CalloutDate AS 'CalloutDate', RD.CompletedBy, CASE WHEN RD.CompleteDate IS NOT NULL THEN CASE WHEN ProjComp.ProjComp < RD.CompleteDate THEN ProjComp.ProjComp ELSE RD.CompleteDate END ELSE ProjComp.ProjComp END AS 'CompleteDate', CASE WHEN RD.CompleteDate IS NOT NULL THEN CASE WHEN ProjComp.ProjComp < RD.CompleteDate THEN DATEPART(wk, ProjComp.ProjComp) ELSE RD.CompletedWeek END ELSE DATEPART(wk, ProjComp.ProjComp) END AS 'CompletedWeek', PT.fld_int_Frequency AS 'PPMFrequency', CalloutEngineerType, CalloutEngineerSubType FROM Prod.dbo.tblPPMScheduleLine SL (NOLOCK) JOIN Prod.dbo.tblProjectHeader PH (NOLOCK) ON PH.fld_int_PPMScheduleRef = SL.fld_int_ID JOIN dbo.tblPPMReportWeekNumbers WN (NOLOCK) ON SL.fld_dat_WeekEnding = WN.WeekEnding JOIN Prod.dbo.tblStore S (NOLOCK) ON SL.fld_int_StoreID = S.fld_int_ID JOIN Prod.dbo.tblStoreType ST (NOLOCK) ON S.fld_int_StoreTypeID = ST.fld_int_ID JOIN Prod.dbo.tblPPMType PT (NOLOCK) ON SL.fld_int_PPMTypeID = PT.fld_int_ID JOIN Prod.dbo.vw_tblStoreCluster_MI SC (NOLOCK) ON S.fld_int_StoreClusterID = SC.fld_int_ID JOIN Prod.dbo.tblStoreDivision SD (NOLOCK) ON SC.fld_int_StoreDivisionID = SD.fld_int_ID LEFT JOIN Prod.dbo.tblHelpDeskResource OM (NOLOCK) ON SC.fld_int_AreaManagerID = OM.fld_int_ID LEFT JOIN Prod.dbo.tblHelpDeskResource DM (NOLOCK) ON SD.fld_int_DivisionalManagerID = DM.fld_int_ID JOIN Prod.dbo.tblAssetMainType MT (NOLOCK) ON PT.fld_int_MainTypeID = MT.fld_int_ID LEFT JOIN ( SELECT DISTINCT SL.fld_int_ID AS 'PPMRef', ISNULL(lastcompletedcallout.CalloutEngineerID,callout.CalloutEngineerID) AS 'CalloutEngineerID', ISNULL(lastcompletedcallout.CalloutEngineer,callout.CalloutEngineer) AS 'CalloutEngineer', ISNULL(lastcompletedcallout.CalloutDate,callout.CalloutDate) AS 'CalloutDate', ISNULL(lastcompletedcallout.CompletedBy,callout.CompletedBy) AS 'CompletedBy', ISNULL(lastcompletedcallout.CompleteDate,callout.CompleteDate) AS 'CompleteDate', ISNULL(lastcompletedcallout.CompletedWeek,callout.CompletedWeek) AS 'CompletedWeek', ISNULL(lastcompletedcallout.CalloutEngineerType,callout.CalloutEngineerType) AS 'CalloutEngineerType', ISNULL(lastcompletedcallout.CalloutEngineerSubType,callout.CalloutEngineerSubType) AS 'CalloutEngineerSubType' FROM Prod.dbo.tblPPMScheduleLine SL (NOLOCK) JOIN Prod.dbo.tblProjectHeader PH (NOLOCK) ON SL.fld_int_ID = PH.fld_int_PPMScheduleRef LEFT JOIN ( SELECT PH.fld_int_ID AS 'ProjectHeaderID', R.fld_int_ID AS 'CalloutEngineerID', R.fld_str_Name AS 'CalloutEngineer', RT.fld_str_Name AS 'CalloutEngineerType', RST.fld_str_Name AS 'CalloutEngineerSubType', CE.fld_dat_CreatedDate AS 'CalloutDate', --amended to only show data when it is completed event status SW 2017-02-21 ----------------------------------- CASE WHEN ce.fld_int_StatusID = 3 THEN CE.fld_str_LastEditedBy END AS 'CompletedBy', CASE WHEN ce.fld_int_StatusID = 3 THEN CE.fld_dat_LastEdited END AS 'CompleteDate', CASE WHEN ce.fld_int_StatusID = 3 THEN DATEPART(wk,CE.fld_dat_LastEdited) END AS 'CompletedWeek' FROM ( SELECT CP.fld_int_ProjectHeaderID, MIN(CE.fld_int_ID) AS 'FirstCallout' FROM Prod.dbo.tblChildProject CP (NOLOCK) JOIN Prod.dbo.tblPPMResourceCalloutEvent CE (NOLOCK) ON CP.fld_int_ID = CE.fld_int_ChildProjectID WHERE fld_int_StatusID IN (1,3) --called out, complete GROUP BY CP.fld_int_ProjectHeaderID )FC JOIN Prod.dbo.tblPPMResourceCalloutEvent CE (NOLOCK) ON FC.FirstCallout = CE.fld_int_ID JOIN Prod.dbo.tblChildProject CP (NOLOCK) ON CE.fld_int_ChildProjectID = CP.fld_int_ID JOIN Prod.dbo.tblProjectHeader PH (NOLOCK) ON CP.fld_int_ProjectHeaderID = PH.fld_int_ID LEFT JOIN Prod.dbo.tblHelpDeskResource R (NOLOCK) ON CE.fld_int_ResourceID = R.fld_int_ID LEFT JOIN Prod.dbo.tblHelpDeskResourceType RT (NOLOCK) ON R.fld_int_HelpDeskResourceTypeID=RT.fld_int_ID LEFT JOIN Prod.dbo.tblHelpDeskResourceType RST (NOLOCK) ON R.fld_int_HelpdeskResourceSubTypeID=RST.fld_int_ID ) callout ON PH.fld_int_ID = callout.ProjectHeaderID LEFT JOIN --first callout for any not completed ( SELECT PH.fld_int_ID AS 'ProjectHeaderID', R.fld_int_ID AS 'CalloutEngineerID', R.fld_str_Name AS 'CalloutEngineer', RT.fld_str_Name AS 'CalloutEngineerType', RST.fld_str_Name AS 'CalloutEngineerSubType', CE.fld_dat_CreatedDate AS 'CalloutDate', CE.fld_str_LastEditedBy AS 'CompletedBy', CE.fld_dat_LastEdited AS 'CompleteDate', DATEPART(wk,CE.fld_dat_LastEdited) AS 'CompletedWeek' FROM ( SELECT a.fld_int_ProjectHeaderID, a.LastEdited, MAX(b.CalloutEventID) AS 'LastCompletedCallout' --if there are potentially > 1 completed callout events with the same Last Edited Date, this picks the max one. FROM ( SELECT CP.fld_int_ProjectHeaderID, MAX(ce.fld_dat_LastEdited) AS LastEdited --datetime of the last edited completed callout event FROM Prod.dbo.tblChildProject CP (NOLOCK) JOIN Prod.dbo.tblPPMResourceCalloutEvent CE (NOLOCK) ON CP.fld_int_ID = CE.fld_int_ChildProjectID WHERE fld_int_StatusID = 3 AND -- complete ce.fld_dat_DateCancelled IS NULL -- can be complete and cancelled at once... GROUP BY CP.fld_int_ProjectHeaderID ) a LEFT JOIN ( SELECT CP.fld_int_ProjectHeaderID, ce.fld_int_ID AS CalloutEventID, ce.fld_dat_LastEdited AS OtherLastEdited FROM Prod.dbo.tblChildProject CP (NOLOCK) JOIN Prod.dbo.tblPPMResourceCalloutEvent CE (NOLOCK) ON CP.fld_int_ID = CE.fld_int_ChildProjectID WHERE fld_int_StatusID = 3 AND -- complete ce.fld_dat_DateCancelled IS NULL -- can be complete and cancelled at once... ) b ON a.fld_int_ProjectHeaderID = b.fld_int_ProjectHeaderID AND a.LastEdited = b.OtherLastEdited GROUP BY a.fld_int_ProjectHeaderID, a.LastEdited --SELECT --CP.fld_int_ProjectHeaderID, --MAX(CE.fld_dat_LastEdited) AS 'LastCompletedCalloutDate' --FROM --Prod.dbo.tblChildProject CP (NOLOCK) JOIN --Prod.dbo.tblPPMResourceCalloutEvent CE (NOLOCK) ON CP.fld_int_ID = CE.fld_int_ChildProjectID --WHERE --fld_int_StatusID = 3 -- complete --GROUP BY --CP.fld_int_ProjectHeaderID )FC JOIN Prod.dbo.tblPPMResourceCalloutEvent CE (NOLOCK) ON FC.LastCompletedCallout = CE.fld_int_ID JOIN Prod.dbo.tblChildProject CP (NOLOCK) ON CE.fld_int_ChildProjectID = CP.fld_int_ID JOIN Prod.dbo.tblProjectHeader PH (NOLOCK) ON CP.fld_int_ProjectHeaderID = PH.fld_int_ID LEFT JOIN Prod.dbo.tblHelpDeskResource R (NOLOCK) ON CE.fld_int_ResourceID = R.fld_int_ID LEFT JOIN Prod.dbo.tblHelpDeskResourceType RT (NOLOCK) ON R.fld_int_HelpDeskResourceTypeID=RT.fld_int_ID LEFT JOIN Prod.dbo.tblHelpDeskResourceType RST (NOLOCK) ON R.fld_int_HelpdeskResourceSubTypeID=RST.fld_int_ID )lastcompletedcallout ON PH.fld_int_ID = lastcompletedcallout.ProjectHeaderID WHERE SL.fld_int_StatusID <> 3 AND --ppm not cancelled callout.CalloutEngineer IS NOT NULL ) RD ON SL.fld_int_ID = RD.PPMRef LEFT JOIN ( SELECT ph.fld_int_PPMScheduleRef AS 'PPM_Ref', MIN(ph.fld_dat_CompleteDate) AS 'ProjComp' FROM Prod.dbo.tblProjectHeader ph (NOLOCK) WHERE ph.fld_dat_CompleteDate IS NOT NULL GROUP BY ph.fld_int_PPMScheduleRef ) ProjComp ON SL.fld_int_ID = ProjComp.PPM_Ref WHERE SL.fld_int_StatusID <> 3 AND --ppm not cancelled fld_int_ProjectStatusID NOT IN (6, 7) AND --project header not cancelled too SL.fld_dat_WeekEnding <= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) --less than today [1]: https://i.sstatic.net/aLXm4.png [2]: https://i.sstatic.net/4HFHd.png