Co tak naprawdę wskazuje kolumna „czyta” w sys.dm_exec_sessions?

To może wydawać się bardzo podstawowym pytaniem i tak powinno być. Jednak jako fan metody naukowej lubię tworzyć hipotezę, a następnie przetestować ją, aby sprawdzić, czy mam rację. W tym przypadku staram się lepiej zrozumieć wynik sys.dm_exec_sessions, a dokładniej, pojedyncza kolumna „czyta”.

SQL Server Books Online raczej sucho określa to jako:

Liczba odczytów wykonanych według żądań w tej sesji podczas tej sesji. Nie ma wartości zerowej.

Można przypuszczać, że wskazywałoby to liczbę stron odczytanych z dysku w celu zaspokojenia żądań wydanych przez tę sesję od początku sesji. To jest hipoteza, którą pomyślałem, że przetestuję.

logical_readsKolumny w tej samej tabeli, jest zdefiniowana jako:

Liczba logicznych odczytów, które zostały wykonane w sesji. Nie ma wartości zerowej.

Z doświadczenia w korzystaniu z SQL Server uważam, że ta kolumna odzwierciedla liczbę stron, które zostały odczytane zarówno z dysku, jak iz pamięci . Innymi słowy, łączna liczba stron kiedykolwiek przeczytanych przez sesję, bez względu na to, gdzie te strony się znajdują. Różnicujący, czyli propozycja wartości, posiadający dwie oddzielne kolumny, które oferują podobne informacje, wydaje się, że można zrozumieć stosunek stron odczytanych z dysku ( reads) do stron odczytanych z bufora pamięci podręcznej ( logical_reads) dla konkretnej sesji.

Na moim stanowisku testowym utworzyłem nową bazę danych, utworzyłem pojedynczą tabelę ze znaną liczbą stron danych, a następnie odczytałem tę tabelę w nowej sesji. Potem spojrzałem, sys.dm_exec_sessionsżeby zobaczyć, co readsi logical_readskolumny mówią o sesji. W tym momencie jestem zaskoczony wynikami. Być może ktoś tutaj może mi to wyjaśnić.

Stanowisko testowe:

USE master;
IF EXISTS (SELECT 1
    FROM sys.databases d 
    WHERE d.name = 'TestReads')
BEGIN
    ALTER DATABASE TestReads SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TestReads;
END
GO
CREATE DATABASE TestReads;
GO
ALTER DATABASE TestReads SET RECOVERY SIMPLE;
BACKUP DATABASE TestReads TO DISK = 'NUL:'; /* ensure we are in 
                                            simple recovery model */
GO

USE TestReads;
GO

/*
    create a table with 2 rows per page, for easy math!
*/
CREATE TABLE dbo.TestReads
(
    ID INT NOT NULL
        CONSTRAINT PK_TestReads
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData CHAR(4000) NOT NULL
);

/*
    insert 5000 pages of data
*/
INSERT INTO dbo.TestReads (SomeData)
SELECT TOP(10000) o1.name
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3
ORDER BY o1.object_id
    , o2.object_id
    , o3.object_id;


/*
    Verify we have 5,000 pages of data, with 10,000 rows.
*/
SELECT o.name
    , p.rows
    , au.total_pages
    , au.used_pages
    , au.data_pages
FROM sys.partitions p
    INNER JOIN sys.objects o ON p.object_id = o.object_id 
    INNER JOIN sys.allocation_units au 
        ON p.hobt_id = au.container_id 
        AND (au.type = 1 or au.type = 0)
WHERE p.index_id = 1
    AND o.name = 'TestReads'
    AND o.type = 'U';

/*
    issue a checkpoint to ensure dirty pages are flushed to disk
*/
CHECKPOINT 30;
DBCC DROPCLEANBUFFERS;
DBCC FREESYSTEMCACHE ('ALL');
DBCC FREEPROCCACHE;
DBCC FREESESSIONCACHE;
GO

/*
    ensure we have no data cached in memory for the TestReads database
*/
USE master;
ALTER DATABASE TestReads SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE TestReads SET ONLINE;

SELECT DatabaseName = d.name
    , SchemaName = s.name
    , ObjectName = o.name
    , AllocatedMB = COUNT(1) * 8192E0 / 1048576
    , PagesInMemory = COUNT(1)
FROM sys.dm_os_buffer_descriptors dobd
    INNER JOIN sys.allocation_units au 
        ON dobd.allocation_unit_id = au.allocation_unit_id
    INNER JOIN sys.partitions p 
        ON au.container_id = p.hobt_id 
             AND (au.type = 1 OR au.type = 0)
    INNER JOIN sys.objects o ON p.object_id = o.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    INNER JOIN sys.databases d 
        ON dobd.database_id = d.database_id
WHERE d.name = 'TestReads'
    AND o.name = 'TestReads'
    AND o.type = 'U'
GROUP BY d.name
    , s.name
    , o.name;

Pierwsze oświadczenie select powyżej pokazuje, że w rzeczywistości tabela składa się z 10 000 wierszy, z 5025 stron łącznie, 5020 stron używanych i 5000 stron danych; dokładnie tak, jak można się spodziewać:

Druga instrukcja select potwierdza, że ​​nie mamy nic w pamięci dla TestReadstabeli.

W nowej sesji wykonujemy następujące zapytanie, zwracając uwagę na id_sesji:

USE TestReads;

SET STATISTICS IO ON;

SELECT *
FROM dbo.TestReads;

Jak można się spodziewać, odczytuje całą tabelę z dysku do pamięci, jak pokazano w danych wyjściowych z SET STATISTICS IO ON:

(10000 row(s) affected)
Table 'TestReads'. Scan count 1, logical reads 5020, physical reads 3, 
read-ahead reads 4998, lob logical reads 0, lob physical reads 0, lob 
read-ahead reads 0.

W trzeciej sesji sprawdzamy sys.dm_exec_sessions:

SELECT des.session_id
    , des.reads
    , des.logical_reads
FROM sys.dm_exec_sessions des
WHERE des.session_id = 57; /* session_id from the 2nd (previous) session */

Spodziewam się zobaczyć sys.dm_exec_sessionspokaz co najmniej 5,000 dla obu readsi logical_reads. Niestety, widzę readspokazuje zero. logical_readspokazuje oczekiwaną liczbę odczytów gdzieś na północ od 5000 - pokazuje 5020 w moim teście:

Wiem, że SQL Server odczytuje całą TestReadstabelę do pamięci, dzięki sys_dm_os_buffer_descriptorsDMV:

USE TestReads;
GO
SELECT DatabaseName = d.name
    , SchemaName = s.name
    , ObjectName = o.name
    , AllocatedMB = COUNT(1) * 8192E0 / 1048576
    , PagesInMemory = COUNT(1)
FROM sys.dm_os_buffer_descriptors dobd
    INNER JOIN sys.allocation_units au 
        ON dobd.allocation_unit_id = au.allocation_unit_id
    INNER JOIN sys.partitions p 
        ON au.container_id = p.hobt_id 
            AND (au.type = 1 OR au.type = 0)
    INNER JOIN sys.objects o ON p.object_id = o.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    INNER JOIN sys.databases d 
        ON dobd.database_id = d.database_id
WHERE d.name = 'TestReads'
    AND o.name = 'TestReads'
    AND o.type = 'U'
GROUP BY d.name
    , s.name
    , o.name;

Co ja robię źle?

Używam SQL Server 2012 11.0.5343 do tego testu.

Dalsze ustalenia:

Jeśli uruchomię następujące:

SELECT des.session_id
    , des.reads
    , des.logical_reads
FROM sys.dm_exec_sessions des

Widzę reads784 w sesji, w której tworzę platformę testową; jednak wszystkie pozostałe sesje pokazują zero w readskolumnie.

Zaktualizowałem teraz moją instancję testową SQL Server do wersji 11.0.6020; jednak wynik jest taki sam.

Moje rozumienie zawsze było takie, że readsjest ono tylko fizyczne (tj. Z dysku) i logical_readspochodzi tylko z puli buforów (tj. Z pamięci). Zrobiłem szybki test z mniejszą tabelą, która ma tylko 2 strony danych i 3 strony łącznie, i to, co widzę, wydaje się potwierdzać te dwie definicje.

Jedną z rzeczy, która prawdopodobnie daje złe wyniki, jest to, że nie czyścisz pamięci. Powinieneś uruchomić następujące testy między testami, aby zmusić go do ponownego załadowania z dysku:

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

Moja konfiguracja testowa była następująca:

CREATE TABLE dbo.ReadTest (Col1 CHAR(7500) DEFAULT (' '));
INSERT INTO dbo.ReadTest (Col1) VALUES (DEFAULT), (DEFAULT);

Następnie uruchomiłem następujące:

SELECT reads, logical_reads FROM sys.dm_exec_sessions WHERE session_id = @@SPID;
SELECT * FROM dbo.ReadTest;

(Tak, testowałem w tej samej sesji, w której uruchomiłem DMV, ale to nie wypaczyło wyników dla readspola, a jeśli nic więcej, było przynajmniej spójne, jeśli przyczyniło się do logical_readspola.)

Do testowania uruchomiłbym polecenie DBCC, a następnie dwa zapytania SELECT. Wtedy zobaczyłbym skok w polach readsi logical_reads. Uruchomiłbym ponownie zapytania SELECT i czasami widziałem dodatkowy skok reads.

Następnie wielokrotnie uruchamiałbym dwa zapytania SELECT i readspozostałyby takie same, podczas gdy zwiększały logical_readssię o 4 za każdym razem.

Chciałbym zacząć od uruchomienia DBCC i zobaczyć ten sam wzorzec. Zrobiłem to kilka razy, a podane liczby były spójne we wszystkich testach.

Więcej informacji:

Testuję również na SQL Server 2012, SP2 - wersja 64-bitowa (11.0.5343).

Następujące polecenia DBCC, które wypróbowaliśmy i nie zaobserwowaliśmy żadnego efektu:

DBCC FREESYSTEMCACHE('ALL');
DBCC FREEPROCCACHE;
DBCC FREESESSIONCACHE;

Większość czasu DBCC DROPCLEANBUFFERSdziała, ale czasami widzę, że wciąż znajduje się w puli buforów. Dziwny.

Kiedy ja:

• DBCC DROPCLEANBUFFERS: Odczyty wzrastają o 24, a logical_reads o 52.
• Uruchom SELECT [Col1] FROM dbo.ReadTest;ponownie: Odczyty nie idą w górę, ale odczyty logiczne zwiększają się o 6.
• Dodaj spację do tekstu zapytania i uruchom ponownie: Odczyty nie idą w górę, ale czytniki logiczne zwiększają się o 52 (tak jak zaraz po DBCC DROPCLEANBUFFERS).

Wydaje się, że 52 logiczne odczyty kont do generowania planu i wyników, co oznacza, że ​​generowanie planu spowodowało dodatkowe 46 logicznych odczytów. Ale fizyczne odczyty nie idą ponownie w górę, a mimo to są takie same 52 logiczne odczyty, jak wtedy, gdy musiały także wykonywać fizyczne odczyty, dlatego logical_readsnie obejmują fizycznych reads. Po prostu wyjaśniam tę kwestię, niezależnie od tego, czy zostało to powiedziane czy dorozumiane w pytaniu.

ALE, zauważyłem jedno zachowanie, które rzuca się (przynajmniej trochę), wykorzystując istnienie stron danych tabeli sys.dm_os_buffer_descriptors: zostaje przeładowane przez inny proces. Jeśli DROPCLEANBUFFERS i sprawdzisz natychmiast, to powinno zniknąć. Ale poczekaj kilka minut, a pojawi się ponownie, ale tym razem bez wszystkich stron danych. W moim teście tabela ma 1 stronę IAM i 4 strony danych. Wszystkie 5 stron znajduje się w puli buforów po wykonaniu SELECT. Ale kiedy zostanie przeładowany przez inny proces, jest to tylko strona IAM i 1 strona danych. Myślałem, że może to być SSMS IntelliSense, ale usunąłem wszystkie odwołania do nazwy tego obiektu na karcie zapytania i nadal się ładuje.