Read Scaleout- Performance

Question

Hi Team, We have enabled read scaleout on out Production work load

We see that performance is not improved in the read only intent for select queries .What could be the cause

Answer 1

Please verify what are the top waits on the replica. If you find that SOS_SCHEDULER_YIELD is on the top and using close to 100% then the whole issue may be caused by spin locks created by the replication. You can verify that using the following query:

SELECT * 
FROM sys.dm_os_wait_stats 
ORDER BY signal_wait_time_ms DESC

One suggestion is to examine the query plans of those queries with bad performance and see if they need optimization. You can use the queries shared on this article to identify those queries.