頁面讀取統計

改進 SQL Server 中的查詢效能

Dean Smith

Founder, Atamai Analytics

資料表資料頁

不論在記憶體或磁碟，所有資料都儲存在大小為 8 KB 的「頁面」
一個頁面可放多列，或一個值可橫跨多個頁面
一個頁面只屬於一張資料表
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 | …

SSMS 中的 STATISTICS IO

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 KB 頁面數

範例：查詢 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 中的查詢效能