CODEWITHSUNDEEP
sql-server-2012azure-sql-databasesql-server-2014
Vikrant More
Vikrant More

Reputation: 5442

What happen when SQL Server AG failover?

While analyzing Dynamic Management Views captured against fail-over server, observed that DMV is getting flush or SQL Engine going to reset the statistics.
In the production environment, it is not allowed to flush/clear the DMV, based on this I am identifying the delta between them. While calculating the delta I come to know that many times previous value is greater than current value.
My question is, let say if database A is configured in AG1 with 2 server like primary-secondary, while switching from primary to secondary will it be going to reset the primary server stats and what are the different reasons that could cause for DMV is getting reset?
Also what happens in the recompilation case for that particular procedure is it going to reset the DMV stats ?

Upvotes: 0

Views: 534

Answers (1)

Grant Fritchey
Grant Fritchey

Reputation: 2805

When the failover occurs, you're moving from one server to another server. Sys.dm_exec_procedure_stats is providing information about procedures that are currently in cache. Since you changed servers, there is nothing in cache for that database after the failover. Therefore, you're going to see radical differences from one server to another after a failover.

It's not a reset of the information. It's simply that the information in the procedure cache of one server is not the same as the information in the procedure cache of another server.

Upvotes: 3

Related Questions