隨記

Azure SQL Tool

commands

static analysis:

set statistic io,time on;
go;

knowledge

1 page = 8k bytes = 8192 bytes
mdf = primary data file (主要資料檔案，儲存資料庫的核心資料與結構)
ndf = secondary data file (次要資料檔案，用於擴展資料庫容量，通常分佈在不同的磁碟上以提升效能)

result set

Result set 是指查詢執行後返回的資料集合，通常以表格形式呈現。
每個 result set 包含多個資料列（rows）和資料欄（columns）。
查詢的設計會影響 result set 的大小和結構，例如使用的篩選條件、排序方式和聚合函數。
分析 result set 的結構和內容有助於確認查詢是否正確返回了所需的資料.

tds 封包

TDS (Tabular Data Stream) 是 Microsoft SQL Server 與客戶端之間的通訊協議，用於傳輸查詢請求和結果。
TDS 封包包含查詢的請求、結果集（result set）、錯誤訊息、登入資訊等。
它是基於二進制的協議，設計用來高效地傳輸資料。

tds 和 result set 的關係

當客戶端向 SQL Server 發送查詢時，查詢請求會被封裝在 TDS 封包中傳輸到伺服器。
SQL Server 執行查詢後，會將結果集（result set）封裝在 TDS 封包中返回給客戶端。
TDS 封包中包含結果集的結構資訊（例如欄位名稱、資料型別）以及實際的資料列內容。
客戶端解析 TDS 封包後，將結果集呈現為表格形式供使用者或應用程式使用。
TDS 的高效設計確保了結果集的快速傳輸，特別是在大型資料集的情況下.

statistic io output explanation

Scan count: 表示執行查詢時掃描的索引或資料表次數。
Logical reads: 表示從緩衝區快取中讀取的頁數，這是記憶體中的讀取操作。
Physical reads: 表示從磁碟中實際讀取的頁數，這是 I/O 操作。
Page server reads: 表示從頁伺服器讀取的頁數（通常用於分散式資料庫）。
Read-ahead reads: 表示 SQL Server 預先讀取的頁數，用於提升效能。
Page server read-ahead reads: 表示從頁伺服器預先讀取的頁數。
LOB logical reads: 表示從緩衝區快取中讀取的大型物件（LOB，例如文字或影像資料）的頁數。
LOB physical reads: 表示從磁碟中實際讀取的大型物件的頁數。
LOB page server reads: 表示從頁伺服器讀取的大型物件的頁數。
LOB read-ahead reads: 表示 SQL Server 預先讀取的大型物件的頁數。
LOB page server read-ahead reads: 表示從頁伺服器預先讀取的大型物件的頁數。

這些數據用於分析查詢的 I/O 效能，幫助找出潛在的效能瓶頸，例如過多的磁碟讀取或不必要的掃描操作.

Logical reads 與 buffer 的關係

Logical reads 是指 SQL Server 從緩衝區快取（Buffer Pool）中讀取資料頁的次數。
緩衝區快取是 SQL Server 的記憶體區域，用於儲存最近從磁碟讀取的資料頁，以加速後續的存取。
當查詢需要存取資料時，SQL Server 會先檢查緩衝區快取中是否已經有該資料頁：
如果資料頁已經在緩衝區中，則進行 Logical read，不需要進行磁碟 I/O 操作。
如果資料頁不在緩衝區中，則進行 Physical read，將資料頁從磁碟讀取到緩衝區，然後再進行 Logical read。
Logical reads 的次數反映了查詢對緩衝區快取的使用情況，過多的 Logical reads 可能表示查詢效率低下，需要進行優化。
減少 Logical reads 的方法包括：
使用適當的索引以減少掃描的頁數。
優化查詢語句以降低不必要的資料存取。
調整資料庫設計以提升效能.

memory consumption

Memory consumption 是指應用程式或查詢在執行過程中使用的記憶體量。
在 SQL Server 中，記憶體主要用於以下幾個方面：
緩衝區快取（Buffer Pool）：用於儲存資料頁和索引頁，減少磁碟 I/O 操作。
查詢執行記憶體：用於排序、哈希運算和其他查詢執行操作。
計劃快取（Plan Cache）：用於儲存執行計劃，以便重複使用。
內部結構：例如鎖、緩衝區描述符等。
高記憶體消耗可能導致系統效能下降，例如頁面交換（Paging）或記憶體壓力。
減少記憶體消耗的方法包括：
優化查詢語句，減少不必要的排序或哈希操作。
使用適當的索引以降低查詢的資源需求。
調整伺服器記憶體配置，確保有足夠的記憶體可用。
定期清理計劃快取，避免過多的執行計劃佔用記憶體.

page life expectancy

Page Life Expectancy (PLE) 是 SQL Server 中的一個性能指標，用於衡量緩衝區快取（Buffer Pool）中的資料頁在被替換之前能夠停留的時間（以秒為單位）。
PLE 的值越高，表示緩衝區快取的效能越好，資料頁能夠在記憶體中停留更長時間，減少磁碟 I/O 操作。
當 PLE 的值過低時，可能表示伺服器的記憶體壓力過大，導致頻繁的頁面替換。
影響 PLE 的因素包括：
查詢的記憶體需求過高。
緩衝區快取的大小不足。
資料庫設計不佳，導致過多的全表掃描或索引掃描。
提升 PLE 的方法包括：
增加伺服器的記憶體容量。
優化查詢語句，減少不必要的資料存取。
使用適當的索引以降低查詢的資源需求。
調整資料庫設計以提升效能。
PLE 的建議值因系統而異，但通常認為應該大於 300 秒（5 分鐘）。

如何查詢 Page Life Expectancy (PLE)

在 SQL Server 中，可以使用以下方法查詢 PLE：

方法 1: 使用系統動態管理檢視 (DMV)

SELECT [object_name], [counter_name], [cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
  AND [counter_name] = 'Page life expectancy';

- 此查詢會返回當前的 PLE 值，單位為秒。

方法 2: 使用 SQL Server Management Studio (SSMS)

開啟 SSMS 並連接到目標 SQL Server 實例。
在查詢視窗中執行上述 SQL 查詢。
檢視結果以了解當前的 PLE 值。

方法 3: 使用性能監視器 (Performance Monitor)

在 Windows 中，按下 Win + R，輸入 perfmon，然後按下 Enter。
在性能監視器中，新增計數器：
選擇 SQLServer:Buffer Manager 物件。
選擇 Page life expectancy 計數器。
監控該計數器的值以了解 PLE 的變化情況。

注意事項

PLE 的值應該穩定且高於 300 秒（5 分鐘），以確保緩衝區快取的效能。
如果 PLE 值持續過低，可能需要檢查伺服器的記憶體配置、查詢效能或資料庫設計.

預估執行計畫與實際執行計畫的不同

預估執行計畫 (Estimated Execution Plan)
預估執行計畫是 SQL Server 根據查詢語句和統計資訊生成的執行計畫，顯示查詢的執行步驟和資源需求的預測。
它不會實際執行查詢，因此不會產生任何 I/O 或影響資料庫的狀態。
預估執行計畫的用途包括：
- 分析查詢的潛在效能問題。
- 確認索引是否被正確使用。
- 預測查詢的資源需求（例如記憶體、CPU）。
預估執行計畫可能與實際執行計畫不同，特別是在統計資訊不準確或查詢執行時條件改變的情況下。
實際執行計畫 (Actual Execution Plan)
實際執行計畫是 SQL Server 在執行查詢後生成的執行計畫，包含查詢的實際執行步驟和資源使用情況。
它會顯示查詢的實際執行結果，例如：
- 實際的 I/O 操作次數（Logical Reads、Physical Reads）。
- 實際的執行時間和 CPU 使用量。
- 實際返回的資料列數量。
實際執行計畫的用途包括：
- 確認查詢是否按照預期執行。
- 分析查詢的實際效能問題，例如過多的 I/O 或不必要的掃描操作。
- 驗證預估執行計畫的準確性。
主要差異
預估執行計畫是基於查詢的靜態分析生成的，而實際執行計畫是基於查詢的實際執行結果生成的。
預估執行計畫不包含實際的執行數據，而實際執行計畫包含詳細的執行數據。
預估執行計畫可能與實際執行計畫不同，特別是在查詢執行時條件改變或統計資訊不準確的情況下。
如何生成執行計畫
在 SQL Server Management Studio (SSMS) 中：
- 預估執行計畫：按下 Ctrl + L 或點擊工具列中的 "Display Estimated Execution Plan" 按鈕。
- 實際執行計畫：按下 Ctrl + M 或點擊工具列中的 "Include Actual Execution Plan" 按鈕，然後執行查詢。

Live 執行計畫 (Live Query Statistics)

Live 執行計畫 是 SQL Server 提供的一項功能，用於在查詢執行過程中即時查看執行計畫的進度和效能數據。
它允許開發人員和資料庫管理員：
即時監控查詢的執行進度。
確認查詢的執行步驟是否按照預期進行。
分析查詢的效能瓶頸，例如過多的 I/O 或長時間的等待。

啟用 Live 執行計畫

在 SQL Server Management Studio (SSMS) 中啟用：
開啟 SSMS 並連接到目標 SQL Server 實例。
在工具列中點擊 "Include Live Query Statistics" 按鈕，或按下 Ctrl + Shift + M。
執行查詢後，SSMS 會顯示查詢的即時執行計畫。
使用 T-SQL 啟用：
在查詢執行前執行以下命令：
```
SET STATISTICS XML ON;
```
執行查詢後，結果中會包含即時執行計畫的 XML 資料。

使用場景

效能調校：
即時查看查詢的執行進度，快速定位效能瓶頸。
分析查詢的資源使用情況，例如記憶體、CPU 和 I/O。
問題診斷：
確認查詢是否因鎖定或等待而卡住。
分析查詢的執行步驟是否按照預期進行。

注意事項

啟用 Live 執行計畫可能會對伺服器效能產生輕微影響，特別是在高負載的環境中。
建議僅在開發或測試環境中使用，或在生產環境中短時間啟用以進行診斷。

關閉 Live 執行計畫

在 SSMS 中取消勾選 "Include Live Query Statistics" 按鈕，或再次按下 Ctrl + Shift + M。
如果使用 T-SQL，執行以下命令：
```
SET STATISTICS XML OFF;
```

計算成本與 Query Optimizer

計算成本

計算成本 是 SQL Server 查詢優化器用來評估執行計畫效能的指標。
它是基於查詢執行所需的資源（例如 CPU、I/O 和記憶體）進行估算的。
計算成本的單位是抽象的，並不直接對應於實際的時間或資源消耗。
查詢優化器會嘗試選擇計算成本最低的執行計畫，以提升查詢效能。

Query Optimizer 的角色

查詢優化器 是 SQL Server 的核心組件，負責為每個查詢生成最佳的執行計畫。
它會根據以下因素計算執行計畫的成本：
I/O 成本：讀取和寫入資料的磁碟操作。
CPU 成本：執行查詢所需的計算資源。
記憶體成本：查詢執行過程中使用的記憶體量。
查詢優化器會考慮多種執行計畫，並選擇成本最低的計畫。

計算成本的影響因素

索引的使用：
適當的索引可以顯著降低查詢的 I/O 成本。
缺乏索引可能導致全表掃描，增加計算成本。
統計資訊：
查詢優化器依賴統計資訊來估算資料分佈和大小。
不準確的統計資訊可能導致次優的執行計畫。
查詢結構：
複雜的查詢（例如多表聯結或嵌套子查詢）可能增加計算成本。
使用簡化的查詢結構可以降低成本。
資料庫設計：
正規化和分區設計可以提升查詢效能，降低計算成本。

如何分析計算成本

使用 預估執行計畫 和 實際執行計畫 查看每個操作的計算成本。
使用 SET STATISTICS IO, TIME ON; 分析查詢的 I/O 和執行時間。
使用系統動態管理檢視（DMV）監控查詢效能，例如 sys.dm_exec_query_stats。

優化建議

建立適當的索引：包括覆蓋索引和聚集索引。
更新統計資訊：確保查詢優化器有準確的資料分佈資訊。
重構查詢：簡化查詢結構，避免不必要的操作。
調整資料庫設計：例如分區表或分散式資料庫設計。

這些方法可以幫助降低計算成本，提升查詢效能.

Nested Join 和 Merge Join 的使用

Nested Join

Nested Join 是一種基於巢狀迴圈的聯結方法，適用於小型資料集或索引鍵已排序的情況。
使用場景：
當內部表（Inner Table）很小，且可以完全載入記憶體時。
當外部表（Outer Table）有索引，且可以快速存取內部表的資料時。

實現方式

SELECT A.*, B.*
FROM TableA A
JOIN TableB B
  ON A.Key = B.Key;

- SQL Server 會根據查詢優化器的選擇，自動決定是否使用 Nested Join。

優化建議

確保內部表有適當的索引。
減少內部表的大小，例如使用篩選條件。

Merge Join

Merge Join 是一種基於排序的聯結方法，適用於兩個輸入表都已排序的情況。
使用場景：
當兩個表都已經按照聯結鍵排序時。
當需要處理大型資料集且排序成本較低時。

實現方式

SELECT A.*, B.*
FROM TableA A
JOIN TableB B
  ON A.Key = B.Key
ORDER BY A.Key, B.Key;

- Merge Join 通常需要兩個表都已排序，否則 SQL Server 會自動插入排序操作。

優化建議

確保兩個表的聯結鍵有索引，並且索引是排序的。
減少排序操作的成本，例如在插入資料時就進行排序。

比較

特性	Nested Join	Merge Join
適用場景	小型資料集或索引鍵已排序	大型資料集且已排序
性能	適合小型資料集，性能較高	適合大型資料集，性能穩定
排序需求	不需要排序	需要排序
索引需求	內部表需要索引	兩個表都需要索引

這些方法可以根據資料集的大小和排序情況選擇適合的聯結方式，以提升查詢效能.

如何查詢索引的靜態資訊

在 Azure SQL Database 中，可以使用以下 SQL 查詢來檢視資料庫中的索引結構和屬性：

查詢索引的基本資訊

SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_unique AS IsUnique,
    i.is_primary_key AS IsPrimaryKey,
    i.fill_factor AS FillFactor
FROM 
    sys.indexes i
JOIN 
    sys.tables t ON i.object_id = t.object_id
WHERE 
    i.type > 0 -- 排除堆積表 (Heap)
ORDER BY 
    t.name, i.name;

查詢索引的詳細欄位資訊

SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    c.name AS ColumnName,
    ic.is_included_column AS IsIncludedColumn,
    ic.key_ordinal AS KeyOrdinal
FROM 
    sys.index_columns ic
JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN 
    sys.tables t ON i.object_id = t.object_id
WHERE 
    i.type > 0 -- 排除堆積表 (Heap)
ORDER BY 
    t.name, i.name, ic.key_ordinal;

查詢索引的使用統計資訊

SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks AS UserSeeks,
    s.user_scans AS UserScans,
    s.user_lookups AS UserLookups,
    s.user_updates AS UserUpdates,
    s.last_user_seek AS LastUserSeek,
    s.last_user_scan AS LastUserScan,
    s.last_user_lookup AS LastUserLookup,
    s.last_user_update AS LastUserUpdate
FROM 
    sys.dm_db_index_usage_stats s
JOIN 
    sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE 
    OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY 
    TableName, IndexName;

如何在建立索引時使用資料壓縮

在 Azure SQL Database 或 SQL Server 中，您可以在建立索引時啟用資料壓縮，以減少儲存空間並提升查詢效能。以下是使用資料壓縮建立索引的方法：

建立索引時啟用資料壓縮

範例 1: 使用 ROW 壓縮

CREATE NONCLUSTERED INDEX IX_Example
ON dbo.TableName (ColumnName)
WITH (DATA_COMPRESSION = ROW);

- ROW 壓縮：適用於需要減少儲存空間但不希望增加太多 CPU 負載的情況。

範例 2: 使用 PAGE 壓縮

CREATE NONCLUSTERED INDEX IX_Example
ON dbo.TableName (ColumnName)
WITH (DATA_COMPRESSION = PAGE);

- PAGE 壓縮：提供更高的壓縮率，但可能會增加 CPU 負載，適用於靜態資料或查詢頻率較低的情況。

修改現有索引以啟用資料壓縮

如果索引已經存在，可以使用以下語法修改索引以啟用資料壓縮：

範例 1: 修改為 ROW 壓縮

ALTER INDEX IX_Example
ON dbo.TableName
REBUILD
WITH (DATA_COMPRESSION = ROW);

範例 2: 修改為 PAGE 壓縮

ALTER INDEX IX_Example
ON dbo.TableName
REBUILD
WITH (DATA_COMPRESSION = PAGE);

查詢資料壓縮的設定

您可以使用以下查詢檢視資料壓縮的設定：

SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    p.data_compression_desc AS CompressionType
FROM 
    sys.partitions p
JOIN 
    sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN 
    sys.tables t ON i.object_id = t.object_id
WHERE 
    p.data_compression > 0
ORDER BY 
    t.name, i.name;

注意事項

資料壓縮可能會增加 CPU 使用量，因此需要根據工作負載進行測試和調整。
PAGE 壓縮通常適用於靜態資料或查詢頻率較低的情況，而 ROW 壓縮適用於需要頻繁查詢的情況。
在啟用資料壓縮之前，建議先進行效能測試以確保符合需求.

Azure SQL Tool

commands

knowledge

result set

tds 封包

tds 和 result set 的關係

statistic io output explanation

Logical reads 與 buffer 的關係

memory consumption

page life expectancy

如何查詢 Page Life Expectancy (PLE)

方法 1: 使用系統動態管理檢視 (DMV)

方法 2: 使用 SQL Server Management Studio (SSMS)

方法 3: 使用性能監視器 (Performance Monitor)

注意事項

預估執行計畫 與 實際執行計畫 的不同

Live 執行計畫 (Live Query Statistics)

啟用 Live 執行計畫

使用場景

注意事項

關閉 Live 執行計畫

計算成本與 Query Optimizer

計算成本

Query Optimizer 的角色

計算成本的影響因素

如何分析計算成本

優化建議

Nested Join 和 Merge Join 的使用

Nested Join

實現方式

優化建議

Merge Join

實現方式

優化建議

比較

如何查詢索引的靜態資訊

查詢索引的基本資訊

查詢索引的詳細欄位資訊

查詢索引的使用統計資訊

如何在建立索引時使用資料壓縮

建立索引時啟用資料壓縮

範例 1: 使用 ROW 壓縮

範例 2: 使用 PAGE 壓縮

修改現有索引以啟用資料壓縮

範例 1: 修改為 ROW 壓縮

範例 2: 修改為 PAGE 壓縮

查詢資料壓縮的設定

注意事項

預估執行計畫與實際執行計畫的不同