Статистика читання сторінок

Покращення продуктивності запитів у SQL Server

Dean Smith

Founder, Atamai Analytics

Сторінки даних таблиці

Усі дані в памʼяті або на диску зберігаються у «сторінках» по 8 КБ
Одна сторінка може містити багато рядків, а одне значення може займати кілька сторінок
Сторінка належить лише одній таблиці
SQL Server працює зі сторінками, кешованими в памʼяті
Якщо сторінка не в кеші, її читають із диска й кешують у памʼяті

Customers: сторінки даних

Сторінка 1

| ALFKI | Alfreds Futterkiste | Maria Anders | Sales Representative | … 
| ANATR | Ana Trujillo Emparedados y helados | Ana Trujillo | Owner | … 
| ANTON | Antonio Moreno Taqueria | Antonio Moreno | Owner | … 
| AROUT | Around the Horn | Thomas Hardy | Sales Representative | … 
| BERGS | Berglunds snabbkop | Christina Berglund | Order Administrator | … 
| BLAUS | Blauer See Delikatessen | Hanna Moos | Sales Representative | … 
| BLONP | Blondesddsl pere et fils | Frederique Citeaux | Marketing Manager | …

Сторінка 2

| BOLID | Bolido Comidas preparadas | Martin Sommer | Owner | … 
| BONAP | Bon app  | Laurence Lebihan | Owner | … 
| BOTTM | Bottom-Dollar Markets | Elizabeth Lincoln | Accounting Manager | … 
| BSBEV | B s Beverages | Victoria Ashworth | Sales Representative | … 
| CACTU | Cactus Comidas para llevar | Patricio Simpson | Sales Agent | … 
| CENTC | Centro comercial Moctezuma | Francisco Chang | Marketing Manager | … 
| CHOPS | Chop-suey Chinese | Yang Wang | Owner | …

Сторінка 3

| COMMI | Comercio Mineiro | Pedro Afonso | Sales Associate | … 
| CONSH | Consolidated Holdings | Elizabeth Brown | Sales Representative | … 
| DRACD | Drachenblut Delikatessen | Sven Ottlieb | Order Administrator | … 
| DUMON | Du monde entier | Janine Labrune | Owner | … 
| EASTC | Eastern Connection | Ann Devon | Sales Agent | … 
| ERNSH | Ernst Handel | Roland Mendel | Sales Manager | … 
| FAMIA | Familia Arquibaldo | Aria Cruz | Marketing Assistant | …

Сторінка 4

| FISSA | FISSA Fabrica Inter. Salchichas S.A. | Diego Roel | Accounting Manager | … 
| FOLIG | Folies gourmandes | Martine Rance | Assistant Sales Agent | … 
| FOLKO | Folk och fa HB | Maria Larsson | Owner | … 
| FRANK | Frankenversand | Peter Franken | Marketing Manager | … 
| FRANR | France restauration | Carine Schmitt | Marketing Manager | … 
| FRANS | Franchi S.p.A. | Paolo Accorti | Sales Representative | … 
| FURIB | Furia Bacalhau e Frutos do Mar | Lino Rodriguez | Sales Manager | …

STATISTICS IO в SSMS

SET STATISTICS IO ON

SELECT UNStatisticalRegion, 
       CountryName,
       Capital
       FROM Nations
       WHERE Capital IN 
         (SELECT CityName 
          FROM Cities)
            AND Capital IN 
               (SELECT CityName 
                FROM Cities 
                WHERE Pop2017 > 1000000);

(88 rows affected)
Table 'Worktable'. Scan count 1, logical reads 104568, physical reads 0, read-ahead reads 0, lob logical reads 0, ...
Table 'Cities'. Scan count 2, logical reads 548, physical reads 0, read-ahead reads 281, lob logical reads 0, lob physical reads 0, ...
Table 'Nations'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, ...

Logical reads

(88 rows affected)
Table 'Cities'. ... , logical reads 548, ... , ...
Table 'Nations'. ... , logical reads 3, ... , ...

logical reads: кількість прочитаних 8-кілобайтних сторінок на таблицю

Приклад: запит 1

SELECT UNStatisticalRegion, 
       CountryName,
       Capital,
          (SELECT MAX(Magnitude) 
          FROM Earthquakes e 
          WHERE n.Capital = e.NearestPop
              AND n.Code2 = e.Country) MaxMagnitude
FROM Nations n
WHERE Capital IN 
        (SELECT NearestPop 
         FROM Earthquakes e
         WHERE n.Capital = e.NearestPop 
            AND n.Code2 = e.Country)
                AND Capital IN 
                   (SELECT NearestPop
                    FROM Earthquakes 
                    WHERE Magnitude >= 7.5);

Приклад: запит 1

SELECT UNStatisticalRegion, 
       CountryName,
       Capital,
          (SELECT MAX(Magnitude) -- 1st sub-query
          FROM Earthquakes e 
          WHERE n.Capital = e.NearestPop
              AND n.Code2 = e.Country) MaxMagnitude
FROM Nations n
WHERE Capital IN 
        (SELECT NearestPop 
         FROM Earthquakes e
         WHERE n.Capital = e.NearestPop 
            AND n.Code2 = e.Country)
                AND Capital IN 
                   (SELECT NearestPop
                    FROM Earthquakes 
                    WHERE Magnitude >= 7.5);

Приклад: запит 1

SELECT UNStatisticalRegion, 
       CountryName,
       Capital,
          (SELECT MAX(Magnitude) -- 1st sub-query
          FROM Earthquakes e 
          WHERE n.Capital = e.NearestPop
              AND n.Code2 = e.Country) MaxMagnitude
FROM Nations n
WHERE Capital IN 
        (SELECT NearestPop -- 2nd sub-query
         FROM Earthquakes e
         WHERE n.Capital = e.NearestPop 
            AND n.Code2 = e.Country)
                AND Capital IN 
                   (SELECT NearestPop
                    FROM Earthquakes 
                    WHERE Magnitude >= 7.5);

Приклад: запит 1

SELECT UNStatisticalRegion, 
       CountryName,
       Capital,
          (SELECT MAX(Magnitude) -- 1st sub-query
          FROM Earthquakes e 
          WHERE n.Capital = e.NearestPop
              AND n.Code2 = e.Country) MaxMagnitude
FROM Nations n
WHERE Capital IN 
        (SELECT NearestPop -- 2nd sub-query
         FROM Earthquakes e
         WHERE n.Capital = e.NearestPop 
            AND n.Code2 = e.Country)
                AND Capital IN 
                   (SELECT NearestPop -- 3rd sub-query
                    FROM Earthquakes 
                    WHERE Magnitude >= 7.5);

Приклад: запит 1

SET STATISTICS IO ON

SELECT UNStatisticalRegion, 
       CountryName,
       Capital,
          (SELECT MAX(Magnitude) -- 1st sub-query
          FROM Earthquakes e 
          WHERE n.Capital = e.NearestPop
              AND n.Code2 = e.Country) MaxMagnitude
FROM Nations n
WHERE Capital IN 
        (SELECT NearestPop -- 2nd sub-query
         FROM Earthquakes e
         WHERE n.Capital = e.NearestPop 
            AND n.Code2 = e.Country)
                AND Capital IN 
                   (SELECT NearestPop -- 3rd sub-query
                    FROM Earthquakes 
                    WHERE Magnitude >= 7.5);

Table 'Earthquakes'. ..., logical reads 54, ...
'Nations'. ..., logical reads 3, ...

Приклад: запит 2

SELECT n.UNStatisticalRegion, 
       n.CountryName,
       n.Capital,
       MAX(e.Magnitude)
FROM Nations n
INNER JOIN Earthquakes e
     ON n.Capital = e.NearestPop 
         AND n.Code2 = e.Country
WHERE e.Magnitude >=7.5
GROUP BY n.UNStatisticalRegion, 
        n.CountryName,
        n.Capital

Приклад: запит 2

SELECT n.UNStatisticalRegion, 
       n.CountryName,
       n.Capital,
       MAX(e.Magnitude)
FROM Nations n
INNER JOIN Earthquakes e
     ON n.Capital = e.NearestPop 
         AND n.Code2 = e.Country
WHERE e.Magnitude >=7.5
GROUP BY n.UNStatisticalRegion, 
        n.CountryName,
        n.Capital

SET STATISTICS IO OFF

Table 'Earthquakes'. ..., logical reads 18, ...
'Nations'. ..., logical reads 3, ...

Порівняння запитів

Перший запит містить три підзапити

Table 'Earthquakes'. ..., logical reads 54, ...
'Nations'. ..., logical reads 3, ...

SQL Server parse and compile time: 
   CPU time = 29 ms, elapsed time = 29 ms.

Другий запит використовує INNER JOIN

Table 'Earthquakes'. ..., logical reads 18, ...
'Nations'. ..., logical reads 3, ...

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.

Давайте потренуємось!

Покращення продуктивності запитів у SQL Server