Řešení problémů s automatickým čištěním sledování změn

platí pro:SQL ServerAzure SQL Databaseazure SQL Managed Instance

Tento článek poskytuje způsoby řešení běžných problémů zjištěných při automatickém čištění sledování změn.

Příznaky

Obecně platí, že pokud automatické čištění nefunguje podle očekávání, můžete vidět jeden nebo více následujících příznaků:

• Vysoká spotřeba úložiště podle jedné nebo více bočních tabulek sledování změn nebo systémové tabulky syscommittab.
• Boční tabulky (interní tabulky, jejichž název začíná předponou change_tracking, například change_tracking_12345) nebo syscommittab nebo obojí, zobrazují významný počet řádků, které nejsou součástí nakonfigurovaného období uchovávání.
• dbo.MSChange_tracking_history tabulka obsahuje položky s konkrétními chybami čištění.
• CHANGETABLE výkon se v průběhu času snižuje.
• Automatické vyčištění nebo ruční vyčištění hlásí vysoké využití procesoru.

Ladění a zmírnění rizik

Pokud chcete identifikovat hlavní příčinu problému s automatickým vyčištěním sledování změn, postupujte podle těchto kroků, aby mohl být problém laděn a zmírněn.

Stav automatického vyčištění

Zkontrolujte, jestli je spuštěné automatické vyčištění. Pokud to chcete zkontrolovat, zadejte dotaz na tabulku historie čištění ve stejné databázi. Pokud je vyčištění spuštěné, tabulka obsahuje položky s počátečním a koncovým časem vyčištění. Pokud vyčištění nebylo spuštěné, tabulka je prázdná nebo obsahuje zastaralé položky. Pokud tabulka historie obsahuje položky se značkou cleanup errors ve sloupci comments, čištění selhává kvůli chybám čištění na úrovni tabulky.

SELECT TOP 1000 * FROM dbo.MSChange_tracking_history ORDER BY start_time DESC;

Automatické vyčištění se pravidelně spouští s výchozím intervalem 30 minut. Pokud tabulka historie neexistuje, pravděpodobně se automatické vyčištění nikdy nespustí. V opačném případě zkontrolujte hodnoty start_time a end_time sloupců. Pokud nejnovější položky nejsou nedávné, tj. jsou hodiny nebo dny staré, nemusí být automatické vyčištění spuštěné. V takovém případě při řešení potíží postupujte následovně.

1. Vyčištění je vypnuté

Zkontrolujte, jestli je pro databázi zapnuté automatické vyčištění. Pokud není, zapněte ho a počkejte aspoň 30 minut, než se podíváte na tabulku historie nových položek. Sledujte pokrok v tabulce historie.

SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('<database_name>')

Nenulová hodnota v is_auto_cleanup_on indikuje, že automatické vyčištění je povoleno. Hodnota doby uchovávání určuje dobu, po kterou se v systému uchovávají metadata sledování změn. Výchozí hodnota doby uchovávání sledování změn je 2 dny.

Pokud chcete povolit nebo zakázat sledování změn, přečtěte si povolení a zakázání funkce Change Tracking (SQL Server).

2. Vyčištění je zapnuté, ale není spuštěné

Pokud je zapnuté automatické vyčištění, vlákno automatického čištění se pravděpodobně zastavilo kvůli neočekávaným chybám. V současné době není možné restartovat vlákno automatického čištění. Musíte zahájit převzetí služeb na sekundární server (nebo restartovat server v případě absence sekundárního serveru) a potvrdit, že nastavení automatického vyčištění je povoleno pro databázi.

Automatické vyčištění se spustí, ale neprobíhá

Pokud jedna nebo více bočních tabulek zobrazuje významnou spotřebu úložiště nebo obsahuje velký počet záznamů nad rámec nakonfigurovaného uchovávání, postupujte podle kroků v této části, které popisují nápravy pro jednu boční tabulku. Stejný postup je možné v případě potřeby zopakovat pro více tabulek.

1. Posouzení backlogu automatického vyčištění

Identifikujte boční tabulky s velkým backlogem záznamů s vypršenou platností, které je potřeba u nich zmírnit. Spuštěním následujících dotazů identifikujte boční tabulky s velkým počtem záznamů s vypršenou platností. Nezapomeňte nahradit hodnoty v ukázkových skriptech, jak je znázorněno.

1. Získejte neplatnou verzi úklidového skriptu:

   SELECT * FROM sys.change_tracking_tables;
   

   Hodnota cleanup_version z vrácených řádků představuje neplatnou verzi čištění.

2. Spusťte následující dynamický dotaz Transact-SQL (T-SQL), který vygeneruje dotaz, aby se získal počet řádků s vypršenou platností bočních tabulek. Nahraďte hodnotu <invalid_version> v dotazu hodnotou získanou v předchozím kroku.

   SELECT 'SELECT ''' + QUOTENAME(name) + ''', COUNT_BIG(*) FROM [sys].' + QUOTENAME(name)
       + ' WHERE sys_change_xdes_id IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts <= <invalid_version>) UNION'
   FROM sys.internal_tables
   WHERE internal_type = 209;
   

3. Zkopírujte sadu výsledků z předchozího dotazu a odeberte klíčové slovo UNION z posledního řádku. Pokud spustíte vygenerovaný dotaz T-SQL prostřednictvím vyhrazeného připojení správce (DAC), poskytne dotaz počet řádků, jejichž platnost vypršela, u všech bočních tabulek. V závislosti na velikosti tabulky sys.syscommittab a počtu bočních tabulek může dokončení tohoto dotazu trvat dlouhou dobu.

   Důležitý

   Tento krok je nezbytný k tomu, aby bylo možné pokračovat kroky pro zmírnění rizik. Pokud se předchozí dotaz nepodaří spustit, identifikujte počet řádků s vypršenou platností jednotlivých bočních tabulek pomocí dalších dotazů.

Proveďte následující kroky ke zmírnění rizik u bočních tabulek seřazených v sestupném pořadí počtu řádků, jejichž platnost vypršela, dokud se počet řádků s prošlou platností nesníží na zvládnutelnou úroveň, aby automatické vyčištění mohlo dohnat.

Jakmile identifikujete boční tabulky s velkým počtem záznamů s vypršenou platností, shromážděte informace o latenci příkazů odstranění boční tabulky a míru odstranění za sekundu za posledních několik hodin. Dále odhadněte čas potřebný k vyčištění postranní tabulky s ohledem na počet zastaralých řádků a latenci při odstranění.

Pomocí následujícího fragmentu kódu T-SQL nahraďte šablony parametrů odpovídajícími hodnotami.

• Dotaz na rychlost čištění za sekundu:

  SELECT
      table_name,
      rows_cleaned_up / ISNULL(NULLIF(DATEDIFF(second, start_time, end_time), 0), 1),
      cleanup_version
  FROM dbo.MSChange_tracking_history
  WHERE table_name = '<table_name>'
  ORDER BY end_time DESC;
  

  Pro funkci DATEDIFF můžete použít také minutovou nebo hodinovou členitost.

• Najděte zastaralý počet řádků v boční tabulce. Tento dotaz vám pomůže najít počet řádků čekajících na vyčištění.

  <internal_table_name> a <cleanup_version> pro tabulku uživatelů jsou ve výstupu vráceném v předchozí části. Pomocí těchto informací spusťte následující kód T-SQL prostřednictvím vyhrazeného připojení správce (DAC):

  SELECT '<internal_table_name>',
      COUNT_BIG(*)
  FROM sys.<internal_table_name>
  WHERE sys_change_xdes_id IN (
          SELECT xdes_id
          FROM sys.syscommittab ssct
          WHERE ssct.commit_ts <= <cleanup_version>
  );
  

  Dokončení tohoto dotazu může nějakou dobu trvat. V případech, kdy vyprší časový limit dotazu, vypočítejte zastaralé řádky vyhledáním rozdílu mezi celkovými řádky a aktivními řádky, které se mají vyčistit.

• Celkový počet řádků v tabulce vedle sebe najdete spuštěním následujícího dotazu:

  SELECT sum(row_count) FROM sys.dm_db_partition_stats
  WHERE object_id = OBJECT_ID('sys.<internal_table_name>')
  GROUP BY partition_id;
  

• Počet aktivních řádků v tabulce vedle sebe najdete spuštěním následujícího dotazu:

  SELECT '<internal_table_name>', COUNT_BIG(*) FROM sys.<internal_table_name> WHERE sys_change_xdes_id
  IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts > <cleanup_version>);
  

  Odhadovaný čas pro vyčištění tabulky můžete vypočítat pomocí míry vyčištění a zastaralého počtu řádků. Představte si následující vzorec:

  Doba potřebná k vyčištění v minutách = (počet zastaralých řádků) / (rychlost vyčištění v minutách)

  Pokud je čas dokončení úklidu tabulky přijatelný, sledujte průběh a nechte automatické čištění pokračovat v práci. Pokud ne, pokračujte dalšími kroky a přejděte k podrobnostem.

2. Kontrola konfliktů zámků tabulek

Určete, jestli vyčištění nepostupuje kvůli konfliktům eskalace uzamčení tabulky, což konzistentně vymaže vyčištění od získání zámků na boční tabulce a odstranění řádků.

Pokud chcete potvrdit konflikt zámku, spusťte následující kód T-SQL. Tento dotaz načte záznamy problematické tabulky, aby zjistil, jestli existuje více položek označujících konflikty zámků. Několik sporadických konfliktů rozložených v určitém období by nemělo mít nárok na postup pro zmírnění rizika. Konflikty by se měly opakovat.

SELECT TOP 1000 *
FROM dbo.MSChange_tracking_history
WHERE table_name = '<user_table_name>'
ORDER BY start_time DESC;

Pokud tabulka historie obsahuje více položek ve sloupcích comments s hodnotou Cleanup error: Lock request time out period exceeded, je jasné, že několik pokusů o vyčištění selhalo kvůli konfliktům zámků nebo vypršení časových limitů uzamčení po sobě. Zvažte následující nápravné prostředky:

• Zakažte a povolte sledování změn v problematické tabulce. To způsobí, že budou odstraněna veškerá sledovací metadata uchovávaná pro tabulku. Data tabulky zůstávají nedotčená. Toto je nejrychlejší lék.

• Pokud předchozí možnost není možná, poté proveďte ruční vyčištění tabulky povolením příznaku trasování 8284 následujícím způsobem:

  DBCC TRACEON (8284, -1);
  GO
  EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';
  

3. Zkontrolujte další příčiny.

Další možnou příčinou zpoždění čištění je zpomalení příkazů delete. Pokud ano, zkontrolujte hodnotu hardened_cleanup_version. Tuto hodnotu je možné načíst prostřednictvím vyhrazeného připojení správce (DAC) k databázi, kterou je potřeba vzít v úvahu.

Vyhledejte posílenou verzi čištění spuštěním následujícího dotazu:

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1004;

Vyhledejte verzi čištění spuštěním následujícího dotazu:

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1003;

Pokud jsou hardened_cleanup_version a cleanup_version hodnoty stejné, přeskočte tuto část a pokračujte k další části.

Pokud se obě hodnoty liší, znamená to, že u jedné nebo více bočních tabulek došlo k chybám. Nejrychlejším zmírněním rizik je zakázat & a povolit sledování změn v problematické tabulce. To způsobí, že veškerá metadata sledování udržovaná pro tabulku budou odstraněna. Data v tabulce zůstanou nedotčená.

Pokud předchozí možnost není možná, spusťte ruční čištění tabulky.

Řešení potíží s nástrojem syscommittab

Tato část popisuje kroky pro ladění a zmírnění problémů se systémovou tabulkou syscommittab, pokud používá hodně místa v úložišti nebo má velký backlog zastaralých řádků.

Vyčištění systémové tabulky syscommittab závisí na vyčištění pomocné tabulky. Až po vyčištění všech bočních tabulek je možné syscommittab vyprázdnit. Ujistěte se, že jsou provedeny všechny kroky v oddílu Automatické čištění běží, ale nedělá pokroky.

Pokud chcete explicitně vyvolat vyčišťování syscommittab, použijte sys.sp_flush_commit_table_on_demand uloženou proceduru.

Poznámka

Uložená procedura sys.sp_flush_commit_table_on_demand může trvat dlouho, pokud odstraňuje velké množství nahromaděných řádků.

Jak je znázorněno v ukázkové části článku sys.sp_flush_commit_table_on_demand, vrátí tato uložená procedura hodnotu safe_cleanup_version()a počet odstraněných řádků. Pokud se vrácená hodnota zdá být 0a pokud je zapnutá izolace snímku, čištění nemusí odstranit nic z syscommittab.

Pokud je doba uchovávání delší než jeden den, je bezpečné znovu spustit uloženou proceduru sys.sp_flush_commit_table_on_demand po celosvětovém povolení příznaku trasování 8239. Tento příznak trasování je vždy bezpečné použít, když je izolace snímků vypnutá, ale v některých případech to nemusí být nutné.

Vysoké využití procesoru během čištění

Problém popsaný v této části se může zobrazit ve starších verzích SQL Serveru. Pokud v databázi existuje velký počet tabulek sledovaných změn a automatické vyčištění nebo ruční vyčištění způsobí vysoké využití procesoru. Příčinou tohoto problému může být také tabulka historie, která byla zmíněna krátce v předchozích částech.

Pomocí následujícího kódu T-SQL zkontrolujte počet řádků v tabulce historie:

SELECT COUNT(*) from dbo.MSChange_tracking_history;

Pokud je počet řádků dostatečně velký, zkuste přidat následující index, pokud chybí. K přidání indexu použijte následující kód T-SQL:

IF NOT EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_MSchange_tracking_history_start_time'
        AND object_id = OBJECT_ID('dbo.MSchange_tracking_history')
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_MSchange_tracking_history_start_time
    ON dbo.MSchange_tracking_history (start_time)
END

Spouštějte čištění častěji než každých 30 minut

U konkrétních tabulek může docházet k vysokému počtu změn a můžete zjistit, že úloha automatického čištění nemůže vyčistit boční tabulky a syscommittab během 30minutového intervalu. Pokud k tomu dojde, můžete spustit úlohu ručního čištění se zvýšenou frekvencí, aby se proces usnadnil.

Pro SQL Server a Azure SQL Managed Instance vytvořit úlohu na pozadí pomocí sp_flush_CT_internal_table_on_demand s kratším intervalem než je výchozích 30 minut. Pro Azure SQL Database azure Logic Apps je možné použít k plánování těchto úloh.

Pomocí následujícího kódu T-SQL můžete vytvořit úlohu, která pomůže vyčistit boční tabulky pro sledování změn:

-- Loop to invoke manual cleanup procedure for cleaning up change tracking tables in a database
-- Fetch the tables enabled for change tracking
SELECT IDENTITY(INT, 1, 1) AS TableID,
    (SCHEMA_NAME(tbl.Schema_ID) + '.' + OBJECT_NAME(ctt.object_id)) AS TableName
INTO #CT_Tables
FROM sys.change_tracking_tables ctt
INNER JOIN sys.tables tbl
    ON tbl.object_id = ctt.object_id;

-- Set up the variables
DECLARE @start INT = 1,
    @end INT = (
        SELECT COUNT(*)
        FROM #CT_Tables
        ),
    @tablename VARCHAR(255);

WHILE (@start <= @end)
BEGIN
    -- Fetch the table to be cleaned up
    SELECT @tablename = TableName
    FROM #CT_Tables
    WHERE TableID = @start

    -- Execute the manual cleanup stored procedure
    EXEC sp_flush_CT_internal_table_on_demand @tablename

    -- Increment the counter
    SET @start = @start + 1;
END

DROP TABLE #CT_Tables;

Související obsah

• o řešení Change Tracking (SQL Server)
• Funkce sledování změn (Transact-SQL)
• Uložené procedury Change Tracking (Transact-SQL)
• Tabulky pro sledování změn (Transact-SQL)
• Zobrazení údajů a informací o prostoru záznamů pro databázi
• Řešení potíží s vysokým využitím procesoru na SQL Serveru