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