Statistici de citire a paginilor

Îmbunătățirea performanței interogărilor în SQL Server

Dean Smith

Founder, Atamai Analytics

Paginile de date ale tabelului

Toate datele, în memorie sau pe disc, sunt stocate în „pagini" de 8 kilobytes
O pagină poate conține mai multe rânduri sau o valoare poate ocupa mai multe pagini
O pagină aparține unui singur tabel
SQL Server lucrează cu pagini stocate în cache în memorie
Dacă o pagină nu este în cache, este citită de pe disc și stocată în memorie

Clienți: pagini de date

Pagina 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 | …

Pagina 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 | …

Pagina 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 | …

Pagina 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 în 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, ...

Citiri logice

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

logical reads: numărul de pagini de 8 kilobytes citite per tabel

Exemplu: interogarea 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);

Exemplu: interogarea 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);

Exemplu: interogarea 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);

Exemplu: interogarea 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);

Exemplu: interogarea 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, ...

Exemplu: interogarea 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

Exemplu: interogarea 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, ...

Compararea interogărilor

Prima interogare cu trei subinterogări

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

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

A doua interogare cu INNER JOIN

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

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

Să exersăm!

Îmbunătățirea performanței interogărilor în SQL Server