Dlaczego zapytanie SELECT powoduje zapisy?

Zauważyłem, że na serwerze z programem SQL Server 2016 SP1 CU6 czasami sesja zdarzeń rozszerzonych pokazuje zapytanie SELECT powodujące zapis. Na przykład:

Plan wykonania nie pokazuje żadnej oczywistej przyczyny zapisów, takich jak tablica mieszająca, bufor lub sortowanie, które mogłoby przelać się do TempDB:

Może to również powodować przypisanie zmiennych do typu MAX lub automatyczna aktualizacja statystyk, ale w tym przypadku również nie była to przyczyna zapisu.

Z czego jeszcze mogą być te zapisy?

Niezdarny

Nie pamiętam, czy umieściłem je w mojej oryginalnej odpowiedzi , więc oto kolejna para.

Szpule!

SQL Server ma wiele różnych buforów, które są tymczasowymi strukturami danych przechowywanymi w tempdb. Dwa przykłady to bufory tabel i indeksów.

Gdy wystąpią w planie zapytań, zapisy w tych buforach zostaną powiązane z zapytaniem.

Będą one również rejestrowane jako zapisy w DMV, profilerze, XE itp.

Szpula indeksu

Tabela szpuli

Oczywiście liczba wykonanych zapisów wzrośnie wraz z rozmiarem buforowania danych.

Wycieki

Gdy SQL Server nie ma wystarczającej ilości pamięci dla niektórych operatorów, może rozlać niektóre strony na dysk. Dzieje się tak przede wszystkim z rodzajami i skrótami. Widać to w rzeczywistych planach wykonania, aw nowszych wersjach serwera SQL wycieki są również śledzone w dm_exec_query_stats .

SELECT deqs.sql_handle,
       deqs.total_spills,
       deqs.last_spills,
       deqs.min_spills,
       deqs.max_spills
FROM sys.dm_exec_query_stats AS deqs
WHERE deqs.min_spills > 0;

Śledzenie

Możesz użyć podobnej sesji XE, jak ta, której użyłem powyżej, aby zobaczyć je we własnych demach.

CREATE EVENT SESSION spools_and_spills
    ON SERVER
    ADD EVENT sqlserver.sql_batch_completed
    ( ACTION ( sqlserver.sql_text ))
    ADD TARGET package0.event_file
    ( SET filename = N'c:\temp\spools_and_spills' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 1 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = OFF,
           STARTUP_STATE = OFF );
GO

W niektórych przypadkach Magazyn zapytań może powodować zapisy jako efekt instrukcji select w tej samej sesji.

Można to odtworzyć w następujący sposób:

USE master;
GO
CREATE DATABASE [Foo];
ALTER DATABASE [Foo] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, 
  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), 
  DATA_FLUSH_INTERVAL_SECONDS = 900, 
  INTERVAL_LENGTH_MINUTES = 60, 
  MAX_STORAGE_SIZE_MB = 100, 
  QUERY_CAPTURE_MODE = ALL, 
  SIZE_BASED_CLEANUP_MODE = AUTO);
USE Foo;
CREATE TABLE Test (a int, b nvarchar(max));
INSERT INTO Test SELECT 1, 'string';

Utwórz sesję zdarzeń rozszerzonych do monitorowania:

CREATE EVENT SESSION [Foo] ON SERVER 
ADD EVENT sqlserver.rpc_completed(SET collect_data_stream=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.is_system,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.sql_text)
    WHERE ([writes]>(0))),
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.is_system,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.sql_text)
    WHERE ([writes]>(0)))
ADD TARGET package0.event_file(SET filename=N'C:\temp\FooActivity2016.xel',max_file_size=(11),max_rollover_files=(999999))
WITH (MAX_MEMORY=32768 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF);

Następnie uruchom następujące polecenie:

WHILE @@TRANCOUNT > 0 COMMIT
SET IMPLICIT_TRANSACTIONS ON;
SET NOCOUNT ON;
GO
DECLARE @b nvarchar(max);
SELECT @b = b FROM dbo.Test WHERE a = 1;
WAITFOR DELAY '00:00:01.000';
GO 86400

Transakcja niejawna może, ale nie musi być konieczna do odtworzenia tego.

Domyślnie w górnej części następnej godziny zadanie gromadzenia statystyk Query Store spowoduje zapisanie danych. Wydaje się, że (czasami?) Występuje w ramach pierwszego zapytania użytkownika wykonanego w ciągu godziny. Sesja zdarzeń rozszerzonych pokaże coś podobnego do następującego:

Dziennik transakcji pokazuje zapisy, które miały miejsce:

USE Foo;
SELECT [Transaction ID], [Begin Time], SPID, Operation, 
  [Description], [Page ID], [Slot ID], [Parent Transaction ID] 
FROM sys.fn_dblog(null,null) 
/* Adjust based on contents of your transaction log */
WHERE [Transaction ID] IN ('0000:0000042c', '0000:0000042d', '0000:0000042e')
OR [Parent Transaction ID] IN ('0000:0000042c', '0000:0000042d', '0000:0000042e')
ORDER BY [Current LSN];

Sprawdzanie strony za pomocą DBCC PAGEpokazuje, że zapisy mają sys.plan_persist_runtime_stats_interval.

USE Foo;
DBCC TRACEON(3604); 
DBCC PAGE(5,1,344,1); SELECT
OBJECT_NAME(229575856);

Zauważ, że wpisy dziennika pokazują trzy zagnieżdżone transakcje, ale tylko dwa rekordy zatwierdzeń. W podobnej sytuacji produkcyjnej doprowadziło to do prawdopodobnie wadliwej biblioteki klienta, która wykorzystywała transakcje niejawne nieoczekiwanie rozpoczynając transakcję zapisu, uniemożliwiając wyczyszczenie dziennika transakcji. Biblioteka została napisana do wydawania zatwierdzeń dopiero po uruchomieniu instrukcji aktualizacji, wstawiania lub usuwania, więc nigdy nie wydała polecenia zatwierdzenia i pozostawiła otwartą transakcję zapisu.

Może się zdarzyć, że tak się stanie, i to z automatyczną aktualizacją statystyk.

Oto sesja XE, na którą będziemy patrzeć:

CREATE EVENT SESSION batches_and_stats
    ON SERVER
    ADD EVENT sqlserver.auto_stats
    ( ACTION ( sqlserver.sql_text )),
    ADD EVENT sqlserver.sql_batch_completed
    ( ACTION ( sqlserver.sql_text ))
    ADD TARGET package0.event_file
    ( SET filename = N'c:\temp\batches_and_stats' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 30 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = OFF,
           STARTUP_STATE = OFF );
GO

Następnie wykorzystamy to do zebrania informacji:

USE tempdb

DROP TABLE IF EXISTS dbo.SkewedUp

CREATE TABLE dbo.SkewedUp (Id INT NOT NULL, INDEX cx_su CLUSTERED (Id))

INSERT dbo.SkewedUp WITH ( TABLOCK ) ( Id )
SELECT CASE WHEN x.r % 15 = 0 THEN 1
            WHEN x.r % 5 = 0 THEN 1000
            WHEN x.r % 3 = 0 THEN 10000
            ELSE 100000
       END AS Id
FROM   (   SELECT     TOP 1000000 ROW_NUMBER() OVER ( ORDER BY @@DBTS ) AS r
           FROM       sys.messages AS m
           CROSS JOIN sys.messages AS m2 ) AS x;


ALTER EVENT SESSION [batches_and_stats] ON SERVER STATE = START

SELECT su.Id, COUNT(*) AS records
FROM dbo.SkewedUp AS su
WHERE su.Id > 0
GROUP BY su.Id

ALTER EVENT SESSION [batches_and_stats] ON SERVER STATE = STOP

Niektóre z interesujących wyników sesji XE:

Aktualizacja automatycznych statystyk nie pokazuje żadnych zapisów, ale zapytanie pokazuje jeden zapis natychmiast po aktualizacji statystyk.