2

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.

Improve this question
1
  • For the past few days, the job has been running fine, under 3 minutes like it did before. I really don't know when this will creep up again or will it work fine from now on, there's just no red flags anywhere. I appreciate your time in helping me with this but I don't know if I'll ever find a root cause for this. Commented Nov 6, 2015 at 21:30

1 Answer 1

Reset to default
1

Given all the detail disclosed…

  1. 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.

  2. 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

  3. 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

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.