Rebuild if high number of Version Ghost Records

[patrickjolliffe]

So we're hitting issue with version ghost records on some of our tables. I know we need to fix root cause, but it's tough, if you search online it just seems a known issue. The problem is when this is in combination with the rebuild scripts, because generally the rebuild scripts are doing only a re-org rather than a rebuild, which is taking a long time, and not fixing the version ghost records. For example looking at one table, it has 76,000 rows, and 26,550,039 version ghost records. Manual rebuild will work-around the issue and clear out these VGRs.

We're trying to follow up with Microsoft but accepting we're having to do manual rebuilds to fix. I wonder would you consider this as an possible enhancement request for your index rebuild scripts? Would you consider accepting a pull request for such a feature if I was to implement it? If so obviously would be an optional parameter to control, threshold to rebuild if number of version ghost records was > 1000 and > number of rows in table maybe?

[BIGabor]

Hi Pjolliffe!

Do you know the root cause for your problem? Is it an Always-On? SQL Version?

Regards, Gabor

[patrickjolliffe]

Yes, we are using Always-On, and I strongly suspect this is a factor.
SQL Server version is 2014, which we are looking to upgrade to 2016 (latest supported version our application supports).

[BIGabor]

And I think you have readable secondaries too. Could you please check the status with this query in SSMS with SQLCMD mode? Just change {Primary_Instance} and {Secondary_Instance} to your instance names, and if you have more Secondaries, replicate the secondary part query for each. If you see high numbers in Diff_Low_water_mark_for_ghosts column without an old transaction for one of your secondaries, then you should restart that secondary instance, or if you want just clear version ghost records, set readable to off for a while and back to on (this option is not a fix, it's like index rebuild, as soon as you changing back to readable, number of version ghost records will increase). It's not an official fix, and works on SQL 2016. Could you please update this threadwith results?

:CONNECT {Primary_Instance} SELECT ar.replica_server_name, DB_NAME(hdrs.database_id) as DatabaseName, MIN(hdrs.low_water_mark_for_ghosts) OVER (PARTITION BY hdrs.database_id) AS DB_MIN_Low_water_mark_for_ghosts, hdrs.low_water_mark_for_ghosts, hdrs.low_water_mark_for_ghosts - MIN(hdrs.low_water_mark_for_ghosts) OVER (PARTITION BY hdrs.database_id) AS Diff_Low_water_mark_for_ghosts, hdrs.replica_id FROM sys.dm_hadr_database_replica_states hdrs LEFT OUTER JOIN sys.availability_replicas ar on ar.replica_id=hdrs.replica_id ORDER BY DB_NAME(hdrs.database_id), ar.replica_server_name

SELECT @@servername AS INSTANCE,MAX(DATEDIFF(SECOND,dm_at.transaction_begin_time ,GETDATE())) AS Max_Transactiontime FROM sys.dm_tran_active_transactions dm_at WHERE [NAME] NOT IN ('worktable') GO

:CONNECT {Secondary_Instance1} SELECT @@servername AS INSTANCE,MAX(DATEDIFF(SECOND,dm_at.transaction_begin_time ,GETDATE())) AS Max_TransactionAgeInSeconds FROM sys.dm_tran_active_transactions dm_at WHERE [NAME] NOT IN ('worktable') GO

:CONNECT {Secondary_Instance2} SELECT @@servername AS INSTANCE,MAX(DATEDIFF(SECOND,dm_at.transaction_begin_time ,GETDATE())) AS Max_TransactionAgeInSeconds FROM sys.dm_tran_active_transactions dm_at WHERE [NAME] NOT IN ('worktable') GO

[patrickjolliffe]

OK, I'm on annual leave until next year, so I'll follow up then.

[BIGabor]

OK, I'm on annual leave until next year, so I'll follow up then.

Hi Pjolliffe,

did you check it?

Gabor