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.
-> Check this article for more details on high Log Send Queue on Asynchronous Replica.
-> Log Send Queue shows the amount of log records in the log files of the primary database that has not yet been sent to the secondary replica. This value is sent to the secondary replica from the primary replica. Queue size does not include FILESTREAM files that are sent to a secondary.
-> I faced this issue recently on a Synchronous replica. We are aware of the reason why the log send queue increased, that was due to an Alter Index job as part of weekly maintenance. But we wanted to understand if we can do to speed up the process. It all depends on the network link and if we have reached the maximum limit, then there is not much we can do here other than waiting.
-> I tried below actions,
Restart Always on Endpoints on Secondary and then on Primary
ALTER ENDPOINT endpoint_name STATE = STOPPED
ALTER ENDPOINT endpoint_name STATE = STARTED
-> After above action, Log send queue gradually decreased and Always On database came to sync in sometime.
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.
create table Table1(
Col1 int primary key identity(1,1),
Col2 varchar(255),
Col3 datetime,
Col4 char(10))
create table Table2(
Col1 int primary key identity(1,1),
Col2 varchar(255),
Col3 datetime,
Col4 char(10))
create index ix_test on Table1(Col2) INCLUDE(Col4)
create index ix_test on Table2(Col2) INCLUDE(Col4)
set nocount on
insert into Table1 values ('test',getdate(),'test1')
go 100000
insert into Table2 values ('test',getdate(),'test1')
go 100000
-> Once above objects are created. Lets execute below query,
select Col1,Col2,Col3,Col4 from Table1 where Col2='Test'
-> We are getting a Clustered Index Scan. Lets check Table1 properties and see what indexes we have,
-> We have a clustered index on Col1 that is created as part of the clustered index and then a non-clustered index on column Col2.
-> Below query we executed has a predicate on Col2, so not sure why the optimizer selected a clustered index scan instead of an Index seek using Index ix_test. Let us try forcing this index now,
select Col1,Col2,Col3,Col4 from Table1 with (index(ix_test)) where Col2='Test'
-> We see an Index seek using Index ix_test and then a key lookup after we forced the Index Ix_test. It seems like optimizer went for a clustered index scan instead of an Index seek + Key lookup as the plan with Clustered Index scan is comparatively cheap than an Index seek + Key lookup.
-> Lets verify this by running the query with and without the hint. As expected, query without hint has overall cost of just 3% and the query with the index hint is close to 97%.
-> It is always good to go with the plans created by optimizer rather than adding hints. Adding hints can help when there is an optimizer timeout. Since this article is about forcing an index, lets look at another method using which we can force an index without worrying much on the performance.
-> Execute below query and get the index id of Ix_Test on Table1.
select object_name(object_id) TableName,name,index_id,type,type_desc from sys.indexes where object_id=OBJECT_ID('Table1')
-> In my case Index id 3 is Ix_test. We can use below query to force the index Ix_Test,
select Col1,Col2,Col3,Col4 from Table1 with (index(3)) where Col2='Test'
-> Lets see how can we force an index when join between tables are performed. Lets execute below query,
select a.Col1,a.Col2,a.Col3,b.col4
from Table1 a
INNER JOIN Table1 b on a.col1=b.col1
where a.Col2='Test'
-> We see an cluster Index scan for both tables. Now lets force index ix_test for both tables using below query,
select a.Col1,a.Col2,a.Col3,b.col4
from Table1 a with (index(ix_test))
INNER JOIN Table1 b with (index(ix_test)) on a.col1=b.col1
where a.Col2='Test'
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.