Query performance drops due to version_ghost_record_count in AlwaysOn

Published May 25 2022 01:36 AM 1,105 Views
Microsoft

The title is actually a little bit misleading, because the issue will not only happen on AlwaysOn, but let's start with this. The symptom is that, you may have alwayson environment and you have readable secondary, when the secondary failed synchronization or secondary sql server service stopped, you may find the query performance on primary degraded also.

 

In this situation, the first suggestion is always try to collect the ACTUAL execution plan, compare the plan before secondary stopped and after. If the plan are totally the same, then you can refer to this article.

 

Check the IO information of the query by set statistics io on, or profile trace, or xevent, the ways you can get the IO information of a query

set statistics io on

select * from tb1

set statistics io off

 

Then compare the I/O under AG healthy and unhealthy status. Now let me use a demo to explain, you can see the difference.

 

CREATE TABLE [dbo].[tb1](

[id] [int] NULL

)

create clustered index ID on tb1(ID)

insert into tb1 values(1,'delete')

go 20100

delete from tb1   -->this will generate ghost rows

select * from tb1

 

In this situation, the I/O will be small, since we already deleted the data and the pages are expected to be released

Table 'tb1'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

Now insert back the rows

insert into tb1 values(1,'delete')

go 20100

 

Stop readable secondary replica, delete on primary again

delete from tb1   -->this will generate version ghost rows

select * from tb1

In this situation, the I/O will still be large, because the pages have ghost rows and cannot be released

Table 'tb1'. Scan count 1, logical reads 109, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

In this case,  then you can use below way to check the version ghost rows of table:

use yourdbname

SELECT ghost_record_count,version_ghost_record_count,*

FROM sys.dm_db_index_physical_stats (DB_ID(N'yourdbname'), OBJECT_ID(N'yourtablename'), NULL, NULL, 'SAMPLED')

 

In my case, it is 20100 rows, and it never get reduced unless the secondary come back sync again.

 

YueJ_7_0-1653467391773.png

 

It's not caused by a lazy ghost cleanup thread actually, it is that the ghost cleanup thread unable to clean the version_ghost_record_count. Only when the version_ghost_record_count 'CONVERTED' to ghost_record_count, the ghost cleanup can actually clean the data.

 

'converted' to ghost_record_count after I started secondary:

YueJ_7_1-1653467391774.png

 

We actually have BOL described such issue in AlwaysOn

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-...

 

"Because read operations are mapped to snapshot isolation transaction level, the cleanup of ghost records on the primary replica can be blocked by transactions on one or more secondary replicas. The ghost record cleanup task will automatically clean up the ghost records for disk-based tables on the primary replica when they are no longer needed by any secondary replica. This is similar to what is done when you run transaction(s) on the primary replica. In the extreme case on the secondary database, you will need to kill a long running read-query that is blocking the ghost cleanup. Note, the ghost clean can be blocked if the secondary replica gets disconnected or when data movement is suspended on the secondary database. "

 

When you have active RCSI/SI transactions that need the deleted versioned rows, you can also meet this issue.

 

Then how to resolve this issue?

  • Improve query plan efficiency, with less scan on tables
  • In AlwaysOn environment, if the AG is not healthy already due to secondary disconnected, you can temporarily set the 'Readable' to 'NO', or simply remove the secondary from AlwaysOn, and check the value again.
  • In healthy AG environment, check if there is long active transactions on secondary, and active RCSI/SI transaction on primary
  • In standalone environment, check if you have active SI/RCSI transactions

select text, a.*,b.kpid,b.blocked,b.lastwaittype,b.waitresource,b.dbid,b.cpu,b.physical_io,b.memusage,b.login_time,b.last_batch,b.open_tran,b.status,b.hostname,b.program_name,b.cmd,b.loginame,request_id

from sys.dm_tran_active_snapshot_database_transactions a

inner join sys.sysprocesses b

cross apply sys.dm_exec_sql_text(sql_handle)

on a.session_id = b.spid

%3CLINGO-SUB%20id%3D%22lingo-sub-3425900%22%20slang%3D%22en-US%22%3EQuery%20performance%20drops%20due%20to%20version_ghost_record_count%20in%20AlwaysOn%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3425900%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20title%20is%20actually%20a%20little%20bit%20misleading%2C%20because%20the%20issue%20will%20not%20only%20happen%20on%20AlwaysOn%2C%20but%20let's%20start%20with%20this.%20The%20symptom%20is%20that%2C%20you%20may%20have%20alwayson%20environment%20and%20you%20have%20readable%20secondary%2C%20when%20the%20secondary%20failed%20synchronization%20or%20secondary%20sql%20server%20service%20stopped%2C%20you%20may%20find%20the%20query%20performance%20on%20primary%20degraded%20also.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20situation%2C%20the%20first%20suggestion%20is%20always%20try%20to%20collect%20the%20ACTUAL%20execution%20plan%2C%20compare%20the%20plan%20before%20secondary%20stopped%20and%20after.%20If%20the%20plan%20are%20totally%20the%20same%2C%20then%20you%20can%20refer%20to%20this%20article.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECheck%20the%20IO%20information%20of%20the%20query%20by%20set%20statistics%20io%20on%2C%20or%20profile%20trace%2C%20or%20xevent%2C%20the%20ways%20you%20can%20get%20the%20IO%20information%20of%20a%20query%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eset%20statistics%20io%20on%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eselect%20*%20from%20tb1%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eset%20statistics%20io%20off%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20compare%20the%20I%2FO%20under%20AG%20healthy%20and%20unhealthy%20status.%20Now%20let%20me%20use%20a%20demo%20to%20explain%2C%20you%20can%20see%20the%20difference.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3ECREATE%20TABLE%20%5Bdbo%5D.%5Btb1%5D(%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%5Bid%5D%20%5Bint%5D%20NULL%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E)%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ecreate%20clustered%20index%20ID%20on%20tb1(ID)%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Einsert%20into%20tb1%20values(1%2C'delete')%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ego%2020100%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Edelete%20from%20tb1%26nbsp%3B%3C%2FFONT%3E%26nbsp%3B%20--%26gt%3Bthis%20will%20generate%20ghost%20rows%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eselect%20*%20from%20tb1%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20situation%2C%20the%20I%2FO%20will%20be%20small%2C%20since%20we%20already%20deleted%20the%20data%20and%20the%20pages%20are%20expected%20to%20be%20released%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3ETable%20'tb1'.%20Scan%20count%201%2C%20logical%20reads%202%2C%20physical%20reads%200%2C%20read-ahead%20reads%200%2C%20lob%20logical%20reads%200%2C%20lob%20physical%20reads%200%2C%20lob%20read-ahead%20reads%200.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20insert%20back%20the%20rows%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Einsert%20into%20tb1%20values(1%2C'delete')%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Ego%2020100%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStop%20readable%20secondary%20replica%2C%20delete%20on%20primary%20again%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Edelete%20from%20tb1%26nbsp%3B%26nbsp%3B%3C%2FFONT%3E%20--%26gt%3Bthis%20will%20generate%20version%20ghost%20rows%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eselect%20*%20from%20tb1%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3EIn%20this%20situation%2C%20the%20I%2FO%20will%20still%20be%20large%2C%20because%20the%20pages%20have%20ghost%20rows%20and%20cannot%20be%20released%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3ETable%20'tb1'.%20Scan%20count%201%2C%20logical%20reads%20109%2C%20physical%20reads%200%2C%20read-ahead%20reads%200%2C%20lob%20logical%20reads%200%2C%20lob%20physical%20reads%200%2C%20lob%20read-ahead%20reads%200.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20case%2C%26nbsp%3B%20then%20you%20can%20use%20below%20way%20to%20check%20the%20version%20ghost%20rows%20of%20table%3A%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Euse%20yourdbname%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3ESELECT%20ghost_record_count%2Cversion_ghost_record_count%2C*%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3EFROM%20sys.dm_db_index_physical_stats%20(DB_ID(N'yourdbname')%2C%20OBJECT_ID(N'yourtablename')%2C%20NULL%2C%20NULL%2C%20'SAMPLED')%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20my%20case%2C%20it%20is%2020100%20rows%2C%20and%20it%20never%20get%20reduced%20unless%20the%20secondary%20come%20back%20sync%20again.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22YueJ_7_0-1653467391773.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22YueJ_7_0-1653467391773.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F374884i222362BDA30FF383%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22YueJ_7_0-1653467391773.png%22%20alt%3D%22YueJ_7_0-1653467391773.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20not%20caused%20by%20a%20lazy%20ghost%20cleanup%20thread%20actually%2C%20it%20is%20that%20the%20ghost%20cleanup%20thread%20unable%20to%20clean%20the%20version_ghost_record_count.%20Only%20when%20the%20version_ghost_record_count%20'CONVERTED'%20to%20ghost_record_count%2C%20the%20ghost%20cleanup%20can%20actually%20clean%20the%20data.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E'converted'%20to%20ghost_record_count%20after%20I%20started%20secondary%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22YueJ_7_1-1653467391774.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22YueJ_7_1-1653467391774.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F374885iBC3976B9B8BD5B71%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22YueJ_7_1-1653467391774.png%22%20alt%3D%22YueJ_7_1-1653467391774.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20actually%20have%20BOL%20described%20such%20issue%20in%20AlwaysOn%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fdatabase-engine%2Favailability-groups%2Fwindows%2Factive-secondaries-readable-secondary-replicas-always-on-availability-groups%3Fredirectedfrom%3DMSDN%26amp%3Bview%3Dsql-server-ver15%23bkmk_LimitationsRestrictions%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fdatabase-engine%2Favailability-groups%2Fwindows%2Factive-secondaries-readable-secondary-replicas-always-on-availability-groups%3Fredirectedfrom%3DMSDN%26amp%3Bview%3Dsql-server-ver15%23bkmk_LimitationsRestrictions%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%22Because%20read%20operations%20are%20mapped%20to%20snapshot%20isolation%20transaction%20level%2C%20the%20cleanup%20of%20ghost%20records%20on%20the%20primary%20replica%20can%20be%20blocked%20by%20transactions%20on%20one%20or%20more%20secondary%20replicas.%20The%20ghost%20record%20cleanup%20task%20will%20automatically%20clean%20up%20the%20ghost%20records%20for%20disk-based%20tables%20on%20the%20primary%20replica%20when%20they%20are%20no%20longer%20needed%20by%20any%20secondary%20replica.%20This%20is%20similar%20to%20what%20is%20done%20when%20you%20run%20transaction(s)%20on%20the%20primary%20replica.%20In%20the%20extreme%20case%20on%20the%20secondary%20database%2C%20you%20will%20need%20to%20kill%20a%20long%20running%20read-query%20that%20is%20blocking%20the%20ghost%20cleanup.%20Note%2C%20the%20ghost%20clean%20can%20be%20blocked%20if%20the%20secondary%20replica%20gets%20disconnected%20or%20when%20data%20movement%20is%20suspended%20on%20the%20secondary%20database.%26nbsp%3B%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20you%20have%20active%20RCSI%2FSI%20transactions%20that%20need%20the%20deleted%20versioned%20rows%2C%20you%20can%20also%20meet%20this%20issue.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20how%20to%20resolve%20this%20issue%3F%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EImprove%20query%20plan%20efficiency%2C%20with%20less%20scan%20on%20tables%3C%2FLI%3E%0A%3CLI%3EIn%20AlwaysOn%20environment%2C%20if%20the%20AG%20is%20not%20healthy%20already%20due%20to%20secondary%20disconnected%2C%20you%20can%20temporarily%20set%20the%20'Readable'%20to%20'NO'%2C%20or%20simply%20remove%20the%20secondary%20from%20AlwaysOn%2C%20and%20check%20the%20value%20again.%3C%2FLI%3E%0A%3CLI%3EIn%20healthy%20AG%20environment%2C%20check%20if%20there%20is%20long%20active%20transactions%20on%20secondary%2C%20and%20active%20RCSI%2FSI%20transaction%20on%20primary%3C%2FLI%3E%0A%3CLI%3EIn%20standalone%20environment%2C%20check%20if%20you%20have%20active%20SI%2FRCSI%20transactions%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CFONT%20color%3D%22%230000FF%22%3Eselect%20text%2C%20a.*%2Cb.kpid%2Cb.blocked%2Cb.lastwaittype%2Cb.waitresource%2Cb.dbid%2Cb.cpu%2Cb.physical_io%2Cb.memusage%2Cb.login_time%2Cb.last_batch%2Cb.open_tran%2Cb.status%2Cb.hostname%2Cb.program_name%2Cb.cmd%2Cb.loginame%2Crequest_id%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CFONT%20color%3D%22%230000FF%22%3Efrom%20sys.dm_tran_active_snapshot_database_transactions%20a%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CFONT%20color%3D%22%230000FF%22%3Einner%20join%20sys.sysprocesses%20b%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CFONT%20color%3D%22%230000FF%22%3Ecross%20apply%20sys.dm_exec_sql_text(sql_handle)%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CFONT%20color%3D%22%230000FF%22%3Eon%20a.session_id%20%3D%20b.spid%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Co-Authors
Version history
Last update:
‎May 25 2022 02:07 AM
Updated by: