Sql-server – SQL Server Job is running slow after a few days

performancequery-performancesql serversql-server-2012

I have a simple SQL Server job that runs a stored procedure that doesn't take any parameters. The job runs once a day and it normally takes less than 3 minutes to complete. What's odd is that this job runs fine for a few days and then all of a sudden it takes over 21 hours to complete.

We are using SQL Server 2012 SP2 Enterprise Edition on Windows 2012 R2.

Any ideas why that might be the case? Nothing has changed with the code in the stored procedure and everything else seems normal. The load on the server seems to be fine as well.

Here is the stored procedure definition:

CREATE PROCEDURE [dbo].[np_PopulateConsolidatedIBTable]    
AS    
BEGIN    
    SET NOCOUNT ON;    

    TRUNCATE TABLE ConsolidatedIBTable;    

    -- Try to map zones and zip codes    
    SELECT DISTINCT CustomerZipCode, CustomerCountry 
    INTO #temp 
    FROM [dbo].SAP_ONE_SAP;    

    SELECT CustomerZipcode AS ZZ_MappedZipCode, ZoneName, ZipCode 
    INTO #TEMP1    
    FROM #TEMP sap 
    JOIN [Lookup_Zip_Zone] ZZ ON dbo.np_GetProcessedSAPZip(SAP.CustomerZipcode, SAP.CustomerCountry) COLLATE DATABASE_DEFAULT = ZZ.ZipCode COLLATE DATABASE_DEFAULT  ;    

    SELECT * 
    INTO #vwcountryzonemap     
    FROM RSN_CORE..vwcountryzonemap     
    WHERE SSR NOT IN ('Philips' , 'INTL')    
      AND Zone NOT LIKE ('%Mobile')    
    ORDER BY ZoneGUID;    

    DELETE #vwcountryzonemap  WHERE zone <> 'DMS' AND twodigitcode =  'US';    
    DELETE #vwcountryzonemap  WHERE twodigitcode = 'AS' and Zone = 'West';    
    DELETE #vwcountryzonemap  WHERE twodigitcode = 'ES' and Zone <> 'Iberia';    
    DELETE #vwcountryzonemap  WHERE twodigitcode = 'GR' and Zone <> 'Adria';    
    -- End zip code changes    

    INSERT [dbo].[ConsolidatedIBTable] ([EquipmentNumber], [MaterialNumber], [SerialNumber], [SystemIdentifier], [Region], [KeyMarket], [Modality], [ShipToCustomerName], [ShipToCustomerNumber], [ShipToCustomerCountryCode], [ShipToCustomerCountryName], [CustomerTimezone], [ShipToCustomerCity], [ShipToCustomerAddress], [ShipToCustomerProvinceState], [ShipToCustomerPostalCode], [SystemDisplayName], [Category0EquipmentNumber], [EquipmentTechID], [RsnEnabled], [RemoteMonitoringEnabled], [DataSource], [DataSourceIdentifier])    
        SELECT DISTINCT      
            RTRIM(LTRIM(ISNULL(SAP.[SAPSiteNumber], ''))),  -- SAP Site number    
            RTRIM(LTRIM(ISNULL(SAP.[MaterialNumber], ''))), -- Material number     
            RTRIM(LTRIM(ISNULL(SAP.[SerialNumber], ''))),   -- Serial number    
            CASE -- System Identifier      
               WHEN (UPPER(RTRIM(LTRIM(MT.ModalityType))) = 'MR' 
                    AND Wiskey_MR.PRODUCT_FAMILY_NAME = 'MR SYSTEM')     
                  THEN RTRIM(LTRIM(ISNULL(SAP.[SerialNumber], '')))   --  If MR only add SysId = Serial Number field for 'MR SYSTEM' family     
   --WHEN UPPER(RTRIM(LTRIM(SAP.ModalityType))) = 'IM'      
   -- THEN  RTRIM(LTRIM(ISNULL(Wiskey_IM.FINGERPRINT, REPLACE(ISNULL(SAP.[HostID], ''), '?', ''))))       
               WHEN UPPER(RTRIM(LTRIM(MT.ModalityType))) = 'CV'  
                    OR UPPER(RTRIM(LTRIM(MT.ModalityType))) = 'XR'    
                  THEN RTRIM(LTRIM(ISNULL(Wiskey_CV.FINGERPRINT, REPLACE(ISNULL(SAP.[HostID], ''), '?', ''))))      
               ELSE      
                  RTRIM(LTRIM(REPLACE(ISNULL(SAP.[HostID], ''), '?', '') ))  --  For MR EWS, if SAP Host ID is specified use it as SystemIdentifier     
            END, -- End of system identifier    
            RTRIM(LTRIM(ISNULL(CZ.[Ssr], ''))),  -- Ssr is the Region      
            CASE -- Zone (Subregion)    
               WHEN SAP.[CustomerCountry] = 'US'     
                  THEN RTRIM(LTRIM(ISNULL(ZZ.[ZoneName], '')))      
                  ELSE RTRIM(LTRIM(ISNULL(CZ.[Zone], '')))      
            END,  -- End of Subregion    
            RTRIM(LTRIM(ISNULL(MT.[ModalityType], ''))),       
            RTRIM(LTRIM(ISNULL(NULLIF(SAP.[CustomerName],''), ISNULL(SAP.SoldToCustomerName, '')))),      
            RTRIM(LTRIM(ISNULL(NULLIF(SAP.[CustomerNumber],''), ISNULL(SAP.SoldToCustomerNumber, '')))),    
            RTRIM(LTRIM(ISNULL(NULLIF(SAP.[CustomerCountry],''), ISNULL(SAP.SoldToCountry, '')))),      
            RTRIM(LTRIM(ISNULL(CZ.[CountryName], ''))),     
            RTRIM(LTRIM(ISNULL(NULLIF(SAP.[CustomerTimezone],''), ISNULL(SAP.SoldToTimeZone, '')))),      
            RTRIM(LTRIM(ISNULL(NULLIF(SAP.[CustomerCity],''), ISNULL(SAP.SoldToCity, '')))),     
            RTRIM(LTRIM(ISNULL(NULLIF(SAP.[CustomerAddress],''), ISNULL(SAP.SoldToAddress, '')))),      
            RTRIM(LTRIM(ISNULL(NULLIF(SAP.[CustomerState],''), ISNULL(SAP.SoldToState, '')))),    
            RTRIM(LTRIM(ISNULL(NULLIF(SAP.[CustomerZipcode],''), ISNULL(SAP.SoldToZip, '')))),      
            [dbo].[np_GetSystemDisplayName] (ISNULL(MT.[ModalityType], ''), ISNULL(SAP.[SAPSiteNumber], ''), ISNULL(SAP.[SerialNumber], '')),       
            RTRIM(LTRIM(ISNULL(SAP.[Category0EquipmentNumber], ''))),      
            RTRIM(LTRIM(ISNULL(SAP.[EquipmentTechID], ''))),      
            RTRIM(LTRIM(ISNULL(SAP.[RsnEnabled], ''))),      
            RTRIM(LTRIM(ISNULL(SAP.[RsnRemoteMonitoring], ''))),      
            'SAP',     -- Data Source Name      
            RTRIM(LTRIM(ISNULL(SAP.[SAPSiteNumber], '')))    --  Data Source Identifier value      
        FROM
            [dbo].[SAP_One_SAP] SAP
        LEFT OUTER JOIN
            (SELECT
                 T1.SAPSiteNumber, FINGERPRINT   
             FROM
                 [dbo].[SAP_One_SAP] T1
             JOIN
                 [dbo].[Wiskey_Processed] T2 ON T1.[SerialNumber] = T2.[SAP_Serial_No]
                                             AND T1.[MaterialNumber] = T2.ZMAT
             WHERE
                 (UPPER(RTRIM(LTRIM(T2.SST))) LIKE 'XPER_PBL30%' OR 
                  UPPER(RTRIM(LTRIM(T2.SST))) = 'ALLURA CENTRON' OR
                  UPPER(RTRIM(LTRIM(T2.SST))) = 'XPER' OR
                  UPPER(RTRIM(LTRIM(T2.SST))) LIKE 'XTRAVIS%')
                 AND ([SERVER_FINGERPRINT] IS NULL  OR 
                      [SERVER_FINGERPRINT] = '')

        UNION

        SELECT
            T3.SAPSiteNumber,              
            T2.FINGERPRINT  
        FROM
            [dbo].[SAP_One_SAP] T1
        JOIN
        (
            SELECT                 
                TT1.FINGERPRINT,    
                TT2.[SAP_Serial_No],
                TT2.ZMAT AS ServerMaterialNumber,
                SubsystemMaterialNumber = 
                CASE WHEN UPPER(RTRIM(LTRIM(TT1.SST))) LIKE 'XTRAVIS%'
                THEN
                    'PB001295'
                WHEN UPPER(RTRIM(LTRIM(TT1.SST))) =  'ECHONAVIGATOR'
                     OR
                     UPPER(RTRIM(LTRIM(TT1.SST))) =  'ECHONAVIGATOR FLEXLM 9.5'
                THEN
                    'PB001412'
                WHEN UPPER(RTRIM(LTRIM(TT1.SST)))  = 'EP_NAVIGATOR'  
                     OR
                     UPPER(RTRIM(LTRIM(TT1.SST)))  = 'EP-NAV FLEXLM 9.5'
                     OR
                     UPPER(RTRIM(LTRIM(TT1.SST)))  = 'EP_3D'
                THEN
                    'PB001340'
                ELSE
                    TT1.ZMAT
                END
            FROM 
                [dbo].[Wiskey_Processed] TT1
            JOIN
                [dbo].[Wiskey_Processed] TT2
            ON
                TT1.[SERVER_FINGERPRINT] = TT2.[FINGERPRINT]
            WHERE
                TT1.[SERVER_FINGERPRINT] IS NOT NULL
            AND
                TT1.[SERVER_FINGERPRINT] <> ''
            AND
            (
                UPPER(RTRIM(LTRIM(TT1.SST))) LIKE 'XTRAVIS%'    
                OR  
                UPPER(RTRIM(LTRIM(TT1.SST))) =  'ECHONAVIGATOR'
                OR
                UPPER(RTRIM(LTRIM(TT1.SST))) =  'ECHONAVIGATOR FLEXLM 9.5'    
                OR 
                UPPER(RTRIM(LTRIM(TT1.SST))) = 'EP_NAVIGATOR'
                OR
                UPPER(RTRIM(LTRIM(TT1.SST)))  = 'EP-NAV FLEXLM 9.5'
                OR
                UPPER(RTRIM(LTRIM(TT1.SST)))  = 'EP_3D'
            )
        ) AS T2
        ON
            T1.[SerialNumber] = T2.[SAP_Serial_No]
        AND
            T1.[MaterialNumber] = T2.[ServerMaterialNumber]
        JOIN
            [dbo].[SAP_ONE_SAP] T3
        ON
            T3.[Category0EquipmentNumber] = T1.[SAPSiteNumber]
        AND
            T3.[MaterialNumber] = T2.[SubsystemMaterialNumber]   
    ) AS Wiskey_CV
  ON
    SAP.[SAPSiteNumber] = Wiskey_CV.[SAPSiteNumber]
  LEFT OUTER JOIN [dbo].[Wiskey_Processed] Wiskey_MR    
   ON SAP.SerialNumber COLLATE DATABASE_DEFAULT = Wiskey_MR.FINGERPRINT COLLATE DATABASE_DEFAULT     
   AND Wiskey_MR.PRODUCT_FAMILY_NAME = 'MR SYSTEM'     

     LEFT OUTER JOIN #vwCountryZoneMap CZ        
   ON RTRIM(LTRIM(ISNULL(NULLIF(SAP.CustomerCountry,''), SAP.SoldToCountry))) COLLATE DATABASE_DEFAULT = RTRIM(LTRIM(CZ.TwoDigitCode)) COLLATE DATABASE_DEFAULT       
   AND UPPER(RTRIM(LTRIM(CZ.Ssr))) <> 'PHILIPS'  -- Philips training systems will come in the UNION      
        LEFT OUTER JOIN #TEMP1 ZZ        
   ON ISNULL(NULLIF(SAP.CustomerZipcode,''), SAP.SoldToZip) COLLATE DATABASE_DEFAULT = ZZ.ZZ_MappedZipCode COLLATE DATABASE_DEFAULT      
  LEFT OUTER JOIN RSN_CORE.dbo.tblSystemType ST       
   ON RTRIM(LTRIM(SAP.MaterialNumber)) COLLATE DATABASE_DEFAULT = RTRIM(LTRIM(ST.CatalogNum))  COLLATE DATABASE_DEFAULT       
   AND ST.ModalityTypeGUID IS NOT NULL      
  LEFT OUTER JOIN RSN_CORE.dbo.tblModalityType MT       
   ON RTRIM(LTRIM(ST.ModalityTypeGUID)) COLLATE DATABASE_DEFAULT = RTRIM(LTRIM(MT.ModalityTypeGUID)) COLLATE DATABASE_DEFAULT      

 WHERE [dbo].[np_IsValidEquipmentUserStatus](SAP.EquipmentUserStatus) = 1      
      AND [dbo].[np_IsValidEquipmentSystemStatus] (SAP.EquipmentSystemStatus) = 1;    

 IF @@Error <> 0    
 BEGIN     
  RETURN @@Error;    
 END     


 IF object_id('tempdb..#ChildParentTbl') IS NOT NULL    
  DROP TABLE #ChildParentTbl    

 CREATE TABLE #ChildParentTbl     
 (    
 [EquipmentNumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    
 [Region] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    
 [KeyMarket] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    
 [ShipToCustomerName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    
 [ShipToCustomerNumber] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    
 [ShipToCustomerCountryCode] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    
 [ShipToCustomerCountryName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    
 [CustomerTimezone] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    
 [ShipToCustomerCity] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    
 [ShipToCustomerAddress] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    
 [ShipToCustomerProvinceState] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    
 [ShipToCustomerPostalCode] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    

 )    

 -- Update PB records with the location information from their master records    
 INSERT INTO #ChildParentTbl    
 select child.[EquipmentNumber]     
   ,parent.[Region]     
   ,parent.[keymarket]    
   ,parent.[ShipToCustomerName]    
   ,parent.[ShipToCustomerNumber]    
   ,parent.[ShipToCustomerCountryCode],parent.[ShipToCustomerCountryName]    
   ,parent.[CustomerTimezone],parent.[ShipToCustomerCity]    
   ,parent.[ShipToCustomerAddress],parent.[ShipToCustomerProvinceState]    
   ,parent.[ShipToCustomerPostalCode]        
   from     
   [dbo].[consolidatedIBTable] child    
   INNER JOIN [dbo].[consolidatedIBTable] parent    
    ON parent.EquipmentNumber=child.[Category0EquipmentNumber]    
   WHERE child.[Category0EquipmentNumber] <> ''    


 UPDATE [dbo].[ConsolidatedIBTable]    
 SET  [KeyMarket] = childTable.KeyMarket       
     ,[Region] = childTable.Region    
  ,[ShipToCustomerName]=childTable.[ShipToCustomerName]    
  ,[ShipToCustomerNumber]=childTable.[ShipToCustomerNumber]    
  ,[ShipToCustomerCountryCode]=childTable.[ShipToCustomerCountryCode]    
  ,[ShipToCustomerCountryName]=childTable.[ShipToCustomerCountryName]    
  ,[CustomerTimezone]=childTable.[CustomerTimezone]    
  ,[ShipToCustomerCity]=childTable.[ShipToCustomerCity]    
  ,[ShipToCustomerAddress]=childTable.[ShipToCustomerAddress]    
  ,[ShipToCustomerProvinceState]=childTable.[ShipToCustomerProvinceState]    
  ,[ShipToCustomerPostalCode]=childTable.[ShipToCustomerPostalCode]    
 FROM [dbo].[ConsolidatedIBTable]    
    INNER JOIN #ChildParentTbl as childTable    
    ON [dbo].[ConsolidatedIBTable].[EquipmentNumber] = childTable.EquipmentNumber;    

 DROP TABLE #ChildParentTbl;    

 IF @@Error <> 0    
 BEGIN     
  RETURN @@Error;    
 END    

   -- Insert the RSN Training Systems data    
        INSERT [dbo].[ConsolidatedIBTable] (    
    [EquipmentNumber],    
    [MaterialNumber],    
    [SerialNumber],    
    [SystemIdentifier],    
    [Region],    
    [KeyMarket],    
    [Modality],    
    [ShipToCustomerName],    
    [ShipToCustomerNumber],    
    [ShipToCustomerCountryCode],    
    [ShipToCustomerCountryName],    
    [CustomerTimezone],    
    [ShipToCustomerCity],    
    [ShipToCustomerAddress],    
    [ShipToCustomerProvinceState],    
    [ShipToCustomerPostalCode],    
    [SystemDisplayName],    
    [Category0EquipmentNumber],    
    [EquipmentTechID],    
    [RsnEnabled],    
    [RemoteMonitoringEnabled],    
    [DataSource],          
    [DataSourceIdentifier]     
          )    
  SELECT DISTINCT    
   ISNULL(RTS.[EquipmentNumber], ''),    
   ISNULL(RTS.[MaterialNumber], ''),    
   ISNULL(RTS.[SerialNumber], ''),    
   ISNULL(RTS.[SystemIdentifier], ''),    
   ISNULL(RTS.[Region], ''),    
   ISNULL(RTS.[KeyMarket], ''),    
   ISNULL(RTS.[Modality], ''),    
   ISNULL(RTS.[ShipToCustomerName], ''),    
   ISNULL(RTS.[ShipToCustomerNumber], ''),    
   ISNULL(RTS.[ShipToCustomerCountryCode], ''),    
   ISNULL(RTS.[ShipToCustomerCountryName], ''),    
   ISNULL(RTS.[CustomerTimezone], ''),    
   ISNULL(RTS.[ShipToCustomerCity], ''),    
   ISNULL(RTS.[ShipToCustomerAddress], ''),    
   ISNULL(RTS.[ShipToCustomerProvinceState], ''),    
   ISNULL(RTS.[ShipToCustomerPostalCode], ''),    
   ISNULL(RTS.[SystemDisplayName], ''),    
   ISNULL(RTS.[Category0EquipmentNumber], ''),    
   ISNULL(RTS.[EquipmentTechID], ''),    
   ISNULL(RTS.[RsnEnabled], ''),    
   ISNULL(RTS.[RemoteMonitoringEnabled], ''),    
            'RSN',                                             -- Data Source    
   CAST(ISNULL(RTS.[SystemGUID], '') AS VARCHAR(50))  --  Data Source Identifier value    
        FROM RSN_CORE.dbo.vwPIB_TrainingSystems RTS  ;      

 IF @@Error <> 0    
 BEGIN     
  RETURN @@Error;    
 END    

  -- Insert the non-OneSAP systems    
  INSERT
    [dbo].[ConsolidatedIBTable] 
    (    
        [EquipmentNumber],    
        [MaterialNumber],    
        [SerialNumber],    
        [SystemIdentifier],    
        [Region],    
        [KeyMarket],    
        [Modality],    
        [ShipToCustomerName],    
        [ShipToCustomerNumber],    
        [ShipToCustomerCountryCode],    
        [ShipToCustomerCountryName],    
        [CustomerTimezone],    
        [ShipToCustomerCity],    
        [ShipToCustomerAddress],    
        [ShipToCustomerProvinceState],    
        [ShipToCustomerPostalCode],    
        [SystemDisplayName],    
        [Category0EquipmentNumber],    
        [EquipmentTechID],    
        [RsnEnabled],    
        [RemoteMonitoringEnabled],    
        [DataSource],          
        [DataSourceIdentifier]     
    )    
  SELECT DISTINCT    
    ISNULL(RTS.[EquipmentNumber], ''),    
    ISNULL(RTS.[MaterialNumber], ''),    
    ISNULL(RTS.[SerialNumber], ''),    
    CASE -- Follow similar rules for RSN systems for MR and CV    
        WHEN (RTS.[Modality] = 'MR' AND Wiskey_MR.PRODUCT_FAMILY_NAME = 'MR SYSTEM') THEN    
        ISNULL(RTS.[SerialNumber], ISNULL(RTS.[SystemIdentifier], ''))    
        WHEN RTS.[Modality] = 'CV' OR RTS.[Modality] = 'XR' THEN    
        RTRIM(LTRIM(ISNULL(Wiskey_CV.FINGERPRINT, ISNULL(RTS.[SystemIdentifier], ''))))      
        ELSE    
        ISNULL(RTS.[SystemIdentifier], '')    
    END,    
    ISNULL(RTS.[Region], ''),    
    ISNULL(RTS.[KeyMarket], ''),    
    ISNULL(RTS.[Modality], ''),    
    ISNULL(RTS.[ShipToCustomerName], ''),    
    ISNULL(RTS.[ShipToCustomerNumber], ''),    
    ISNULL(RTS.[ShipToCustomerCountryCode], ''),    
    ISNULL(RTS.[ShipToCustomerCountryName], ''),    
    ISNULL(RTS.[CustomerTimezone], ''),    
    ISNULL(RTS.[ShipToCustomerCity], ''),    
    ISNULL(RTS.[ShipToCustomerAddress], ''),    
    ISNULL(RTS.[ShipToCustomerProvinceState], ''),    
    ISNULL(RTS.[ShipToCustomerPostalCode], ''),    
    ISNULL(RTS.[SystemDisplayName], ''),    
    ISNULL(RTS.[Category0EquipmentNumber], ''),    
    ISNULL(RTS.[EquipmentTechID], ''),    
    ISNULL(RTS.[RsnEnabled], ''),    
    ISNULL(RTS.[RemoteMonitoringEnabled], ''),    
            'RSN',                                             -- Data Source    
    CAST(ISNULL(RTS.[SystemGUID], '') AS VARCHAR(50))  --  Data Source Identifier value    
  FROM vwSystems_nonOneSAP RTS      
  LEFT OUTER JOIN     
  (
    SELECT  
        SST,    
        FINGERPRINT,    
        ZMAT,    
        SAP_SERIAL_NO    
    FROM 
        [dbo].[Wiskey_Processed]  
  ) AS Wiskey_CV       
  ON 
    RTS.SerialNumber COLLATE DATABASE_DEFAULT = Wiskey_CV.SAP_SERIAL_NO COLLATE DATABASE_DEFAULT       
  AND 
    RTS.MaterialNumber COLLATE DATABASE_DEFAULT = Wiskey_CV.ZMAT COLLATE DATABASE_DEFAULT     
  AND 
  ( 
    UPPER(RTRIM(LTRIM(Wiskey_CV.SST)))  LIKE 'XPER_PBL30%'
    OR
    UPPER(RTRIM(LTRIM(Wiskey_CV.SST)))  = 'ALLURA CENTRON'
    OR
    UPPER(RTRIM(LTRIM(Wiskey_CV.SST)))  = 'XPER'     
    OR 
    UPPER(RTRIM(LTRIM(Wiskey_CV.SST))) LIKE 'XTRAVIS%'  
  )    
  LEFT OUTER JOIN 
    [dbo].[Wiskey_Processed] Wiskey_MR    
  ON 
    RTS.SerialNumber COLLATE DATABASE_DEFAULT = Wiskey_MR.FINGERPRINT COLLATE DATABASE_DEFAULT     
  AND 
    Wiskey_MR.PRODUCT_FAMILY_NAME = 'MR SYSTEM'       
 RETURN @@Error;    

DROP TABLE #TEMP;    
DROP TABLE #TEMP1;    
DROP TABLE #vwcountryzonemap;    

END

The main table that this stored procedure queries from, SAP_ONE_SAP has about 880K records. The table is updated daily but doesn't grow too much. There are updates to this table though.

The SQL job that calls it is very simple, it is just Exec np_PopulateConsolidatedIB and no parameters are used.

I have ran sp_WhoIsActive and I don't see anything else running that would be causing issues.

During the last couple of days, it seems like the job will run fine once (took only 3 minutes) but at the next run time, it runs long again. I have checked the cached plan and it's hitting the cache and re-using the plan. I have also updated stats, rebuild indexes, did a sp_recompile and none of that seems to help. Only a restart of the SQL Server would make it run fast again. Also, I checked memory and CPU usage and they are all fine, there doesn't seem to be any memory pressure.

I had ran the stored procedure piece by piece and it's the first Select Distinct statement that is taking up the time. What I don't understand is why would it run fine the first few times but then start to degrade dramatically. I have checked tempdb and it's not heavily used either, no blocking process and it's using the same query plan in the cache that was used previously when it ran fine.

We are using AAG with two nodes in synchronous mode. I don't see any latency issues with the AAG on the secondary. No network or any issues like that.

Best Answer

Given all the detail disclosed…

From the comments for what you’ve tried, etc.
- confirmed no blocking
- no issues with SELECT statements from VIEWS
- updated stats
- rebuilt indexes
- executed proc with runtime WITH RECOMPILE
- no other parts of the query being an issue when you tested (other than 2. below)
- no issues with server level resources such as disk IO, CPU, memory, etc.
you specifically narrowing this down to just the one statement in the SP with the logic of:
```
SELECT DISTINCT CustomerZipCode, CustomerCountry INTO #temp FROM [dbo].SAP_ONE_SAP
```
you confirming that you’ve taken a look at the query plan and see no issues at this level

So for your question. . .

Any ideas why that might be the case? Nothing has changed with the code in the sproc and everything else seems normal. The load on the server seems to be fine as well.

Due to the fact that you have a configuration of AlwaysOn Availability Groups in an Availability Mode of Synchronous-commit mode, I can only assume this is the ROOT cause of the issue (see notes and resource link below).

There could be latency issues with committed transactions in this Availability Mode configuration due to the primary replica waiting for acknowledgement from the secondary replica that it's hardened its transaction logs before it commits its transaction on the primary.

So, not knowing all the business and infrastructure detail on your side, you may want to consider or perhaps test changing the Availability Mode to Asynchronous-commit mode since it commits its transactions without waiting for acknowledgment from the secondary replica that it has hardened its transaction logs.

You may want to check and confirm if there are issues with transaction log hardening on the secondary replica server when this occurs. If you're going over a slower WAN or MAN link perhaps, confirm no issues at network level hops, or any general server issues, etc.

If there is an issue found at one of these other levels, then fixing that should fix your original issue I would think since the primary could acknowledge quicker that the secondary replica hardened its logs and then it'd commit its transaction.

Synchronous-commit mode

An availability replica that uses this availability mode is known as a synchronous-commit replica. Under synchronous-commit mode, before committing transactions, a synchronous-commit primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log. Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.

Asynchronous-commit mode

An availability replica that uses this availability mode is known as an asynchronous-commit replica. Under asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.

RESOURCE: https://msdn.microsoft.com/en-us/library/ff877884(v=sql.110).aspx

Best Answer

Related Solutions

Sql-server – Slow Performance Inserting Few Rows Into Huge Table

Sql-server – SQL Server backup job last 5 days

Related Question