Thanks everyone for the explanation.But one thing bothering me,

Considering the below example, I am in read committed isolation level and assuming that my table has 100 rows inside it.

now, comes...

if the page inserts happens at end of the file, why it has block the SELECT statements?

Many thanks Sir. Will try the blocked process report.

Thanks everyone.

Sorry I didn't knew that there is a good way of posting sql code and query plan.

Thank you Grant Sir & Jeff.

No trace flags has been enabled on the instance. Also, we are rebuilding the indexes every 30 mins. still we are seeing this issue of...

If there are 50k reads, there's some data somewhere. Without seeing the query, your structures, the execution plan, nothing but the version of SQL Server, which, sorry, communicates just...

Hi,

I have provided the version details in my description.

Microsoft SQL Server 2017 (RTM-CU23) (KB5000685) - 14.0.3381.3 (X64) Feb 9 2021 12:08:50 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing...

-- now I am seeing version_ghost_record_count as well. How to clean up these ? why they exist? and how do they affect the SELECT performance?

SELECT

ghost_record_count,

version_ghost_record_count,

record_count,

*

FROM sys.dm_db_index_physical_stats (DB_ID(N'db1'), OBJECT_ID(N'tname'), NULL, NULL...

thanks a lot.

We are on sql 2017.

Thank a ton for quick response. Based on what you said, the application must have 2 connection strings.

-> wherever WRITES are there , they have to connect to the listener...

Got it. Thanks everyone for all your inputs.

The choice to try and solve this problem by failing over just increased the issues.  In this type of situation - the better option would have been to remove...

Thanks for the inputs. Have couple of questions.

Q1) In worst case scenario, if the secondary db is unavailable or the secondary replica is overloaded, in that case does all read...

• This reply was modified 3 years, 3 months ago by vsamantha35.

Thanks Jeff. Gone through the article and changed the percentages.

https://www.brentozar.com/archive/2015/06/how-to-configure-alwayson-ag-backups-with-ola-hallengrens-scripts/