It’s my passion to open mind  and continue  to learn in Technology.

Recently, I was working a stored procedure that was written in MS SQL Server 2008 R2 and migrated into MS SQL Server 2017.

It has been noticed that stored procedure execution time was increased since migrating to SQL Server 2017.

When I executed the stored procedure, it took about 2 minutes and 45 seconds.  And, The Execution Plans showed  several Remote Query.  My further research, I found this stored procedure used several linked server.

Then, switching the database compatibility mode to SQL Server 2008 (100), then re-executed the SP.

The execution time was down to 1 minutes and 35 seconds.

I did some researches and found a hint that could improve the execute time in this particular stored procedure.

The hints was :

OPTION (USE HINT ( ‘FORCE_LEGACY_CARDINALITY_ESTIMATION’ ))‌

by including this hint at the end of the SELECT statement.

Then, I re-tested this stored procedure in SQL Server 2017.  Amazingly, the execution time only tool 34 seconds.

Availability databases in unhealthy data synchronization state

The AlwaysOn user database showed not healthy data synchronization state

Database on AG group showed “Not Synchronizing”

And the warnings message “Data synchronization state of some availability database is not healthy”

In the SQL Server Management Studio for Secondary availability group replicas showed below:

I did some research and found MSDN article posted:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/data-synchronization-state-of-some-availability-database-is-not-healthy?view=sql-server-2017

• The availability replica might be disconnected
• The data movement might be suspended
• The database might not be accessible
• There might be a temporary delay issue due to network latency or the load on the primary or secondary replica

Solution

Execute the following  T-SQL statement on primary and all secondary replica for the databases that showed “Not Synchronizing” status.

ALTER DATABASE  [DB_Test] SET HADR RESUME

How To fix the suspended data movement in MS SQL Server 2016 AlwaysOn Availability Group (AG)

• Right click the user database
• Select Resume Data Movement