1

This question already has an answer here:

I have two identical servers (same hardware, cpu, memory etc) in an AlwaysOn cluster (sync commit and read-intent enabled), both servers running with SQL Server 2016 SP1 (13.0.4001.0). For some of the queries with identical query plans and statistics, the queries are taking over 10secs to run on the secondary replica where as they finish under 2 secs on the primary replica. I have verified the statistics and query plans are exactly the same and tested executing the queries with no other load on these servers. Not able to find the reason why queries are slow on secondary replica.

Query and Plan from primary replica
https://www.brentozar.com/pastetheplan/?id=S1O5Yxscb

Query and Plan from secondary replica
https://www.brentozar.com/pastetheplan/?id=SJdz5ljq-

However, when I give a maxdop = 8 hint in the query on the secondary server, the n it finishes in 2secs.

Also, when I switch roles upon failover, the query executes in 2 secs on the new primary server and on the secondary server it again takes around 10secs. So doesn't seem to be a hardware problem as the query executes fast on the server which ever is designated as primary on the AG cluster and it runs slow on the secondary node.

marked as duplicate by LowlyDBA, RDFozz, hot2use, Mr.Brownstone, Paul White Dec 15 '18 at 5:11

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Use CAST(FIELD_VALUE AS NVARCHAR(20)) = 'True' for both queries not 'true' (see your second query). Also, update stats on primary so it gets updated on sec. The plan is identical, but waits are not. Make sure that maxdop and max memory are same on both the servers. – Kin Shah Sep 17 '17 at 19:31
  • I set it to 'True' for both the queries, updated stats for the tables with fullscan and verified maxdop and max memory are identical....still no luck. – miledba Sep 18 '17 at 17:42
2

1) Please check the performance metrics (CPU%, Memory, buffers) on the secondary node. Is it a reporting server. AlwaysOn has an option to send all read-queries to the secondary node.

2) AlwaysOn utilizes snapshot isolation. Can you validate if the tempdb is being heavily used? Please validate the write/read latency on tempdb and other databases. Here is one way to check: Query from Database Journal

DECLARE @Sample TABLE (
  DBName varchar(128) 
 ,NumberOfReads bigint
 ,NumberOfWrites bigint)

INSERT INTO @Sample 
SELECT name AS 'DBName'
      ,SUM(num_of_reads) AS 'NumberOfRead'
      ,SUM(num_of_writes) AS 'NumberOfWrites' 
FROM sys.dm_io_virtual_file_stats(NULL, NULL) I
  INNER JOIN sys.databases D  
      ON I.database_id = d.database_id
GROUP BY name 

WAITFOR DELAY '00:05:00.000';

SELECT FirstSample.DBName
      ,(SecondSample.NumberOfReads - FirstSample.NumberOfReads) AS 'Number of Reads'
      ,(SecondSample.NumberOfWrites - FirstSample.NumberOfWrites) AS 'Number of Writes'
FROM 
(SELECT * FROM @Sample) FirstSample
INNER JOIN
(SELECT name AS 'DBName'
      ,SUM(num_of_reads) AS 'NumberOfReads'
      ,SUM(num_of_writes) AS 'NumberOfWrites' 
FROM sys.dm_io_virtual_file_stats(NULL, NULL) I
  INNER JOIN sys.databases D  
      ON I.database_id = d.database_id
GROUP BY name) AS SecondSample
ON FirstSample.DBName = SecondSample.DBName
ORDER BY 'Number of Reads' DESC;

3) Did you try just doing a simple backup/restore onto the secondary node, removing AlwaysOn, and validating if the query is still slow?

4) I know you don't have NOLOCK hints in query. Can you try rebuilding the indexes on primary replica, See what happens? Query execution takes significantly longer on SQL Server Always On secondary replicas.

"When Snapshot Isolation is enforced, NOLOCK hints are ignored. The discrepancy in execution duration between the primary and secondary replicas occurs because the NOLOCK hint is ignored on the read-only secondary replica where Snapshot Isolation is enforced but not on the primary replica where Snapshot Isolation is not enforced by default. This causes the scan of the clustered index to have key order enforced on the secondary replica. On the primary replica, the NOLOCK hint takes precedence and does affect behavior. When the clustered index is highly fragmented, the enforcement of key order for the scan on the read-only secondary replica causes SQL Server to issue single-page reads. But on the primary replica, SQL Server does an allocation unit scan to read multiple pages per IO request."

5) Can you validate what the settings are? Queries Maxdop and Memory Allocation as suggested from Kin above, another good idea

-- Find MaxDop
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'max degree of parallelism'
GO 

-- Change MaxDop settings replace to 8
EXEC dbo.sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE;
GO  
 -- Find Memory allocation on instance
 SELECT [name] AS [Name]
 ,[configuration_id] AS [Number]
 ,[minimum] AS [Minimum]
 ,[maximum] AS [Maximum]
 ,[is_dynamic] AS [Dynamic]
 ,[is_advanced] AS [Advanced]
 ,[value] AS [ConfigValue]
 ,[value_in_use] AS [RunValue]
 ,[description] AS [Description]
 FROM [master].[sys].[configurations]
 WHERE NAME IN ('Min server memory (MB)', 'Max server memory (MB)')
  • 1. Primary and secondary have exactly identical configuration (cpu, memory, io) and there is no load when I m doing these tests. 3. After removing alwayson, the query performs as expected. – miledba Sep 16 '17 at 22:02
  • When I switch roles upon failover, the query executes in 2 secs on the new primary server and on the secondary server it now takes around 10secs. So doesn't seem to be a hardware problem as the query executes fast on the server which ever is designated as primary on the AG cluster and it runs slow on the secondary node. – miledba Sep 16 '17 at 22:08
  • i ran the 2. scripts for 5 mins and there is no tempdb issue on either of the servers. – miledba Sep 17 '17 at 19:49
  • I rebuilt the indexes on all tables in the query and frag on tables is approx 0 and still the query is slow on the secondary node. And as i said earlier above, upon failover and switching of primary and secondary nodes, the query performance issue still lingers on the new secondary server. – miledba Sep 17 '17 at 19:52
  • did this work, answer 5)? if so, please accept answer, and we can close question, Thanks, – user129291 Oct 2 '17 at 1:20
2

Have you checked the disks? If the block sizes differ the number of IO required will differ. I.e. if one side has 64kb (an extent) block sizes and the other has 4kb (default) block sizes, it will take 16x as many IO to read the same number of pages.

Also if the fragmentation of the disks or indexes differ that will cause differences in performance for the same index, stats, and plans. Check that both the server and databases have the same maintenance configurations.

Not the answer you're looking for? Browse other questions tagged or ask your own question.