Environment

-> Application team advised that Read-Only queries are executing indefinitely on secondary database that is part of Always On Availability group.

-> I tried below query to check for blocking and could see that a system session is blocking the select queries,

select * from sysprocesses where blocked<>0

-> I tried a simple select query as below on the secondary server and had the same symptoms where in the select query was running indefinitely,

select top 1 * from [Table] 

-> My session was 73 and I tried below query specifically to check what it is waiting on,

select spid,blocked,waittype, waittime,lastwaittype,dbid,waitresource from sysprocesses where blocked<>0
go
select spid,blocked,waittype, waittime,lastwaittype,dbid,waitresource from sysprocesses where spid=73

-> Select queries are blocked by Session 84. Checked what is session id 84,

-> Seems like a system session. Tried below query,

select session_id, wait_type,db_name(database_id),command from sys.dm_exec_requests where session_id = 84

-> Session ID 84 is a system thread that is leveraged for REDO operation on secondary also known as “DB STARTUP”.

-> Checked on the Primary replica to see if there are any huge write operations on object [Table].

-> I could see an alter index command on the same object [Table] on primary replica.

-> The redo thread on Secondary replica acquires schema modification (Sch-M) locks on the secondary database to make any DDL changes that are performed on primary replica. On the secondary replica, the read-only queries acquire schema stability (Sch-S) locks to process the query. In our case the select queries were not able to acquire schema stability (Sch-S) locks as the REDO thread has already acquired schema modification (Sch-M) locks and this is the reason why we were experiencing blocking.

-> In the meantime, Alter Index command completed on the primary replica and we were able to execute select queries without any issues after that.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

-> When trying to open a new query window for a secondary database that is part of Availability group using SQL Server Management Studio, I got below error,

TITLE: Connect to Database Engine
ADDITIONAL INFORMATION:
The target database (‘JBDB’) is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online. (Microsoft SQL Server, Error: 978)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=978&LinkId=20476

-> I just configured Read-Routing list on the Primary database that is part of Always On availability group. More details on Read-Routing list can be found here.

-> Below are options that can be leveraged when configuring Read-Only Access on an Availability group,

No
No user connections are allowed to secondary databases of this replica. They are not available for read access. This is the default setting.

Read-intent only
Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.

Yes
All connections are allowed to secondary databases of this replica, but only for read access. The secondary database(s) are all available for read access.

-> More details of these option are explained in this blog post with more clarity.

-> The error that we are experiencing is due to the fact that the Availability group is configured with “Read-intent only” option.

-> “Read-intent only” means that the Secondary Replica only accepts connections that are explicitly configured for that purpose and the problem occurs when you are trying to connect to an AG Secondary Replica database configured for Read-Intent only without explicitly using the proper parameter.

-> It is pretty clear that we are not able to make a connection to database JBDB on Server JBSAG2 directly. Let us try a connection using ApplicationIntent=ReadOnly and it should connect just fine,

-> If you don’t want to use this switch and want to directly connect to the secondary database to execute Read-Only Queries using SSMS or other application, then configuring Readabale Secondary to “Yes” instead of “Read-intent only” on Availability group properties as below will help,

Hope this helps!!!

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.