這個系列文主要是我個人經驗與我看待DB的角度出發
然後因為現在有 AI，語法、datatype 那些很基礎的東西網路上都很多了，所以就不會出現在這裡
系列文主要會分成兩個階段，基礎 & 效能調校。
因為都是我個人經驗，如有錯誤請多指教。

組態

sp_configure

這是預設的預存程序，他是用來變更在Instance 層級設定的。

可以直接執行他，不帶任何參數的話，就是讓你檢視目前的設定

1. Name : 執行個體層及設定名稱
2. Minimum : 此設定可以接受的最小值
3. Maximum : 此設定可以接受的最大值
4. Config_value : 設定值。 如果這個數值跟Run_value 不同的話，重新啟動服務就可以，因為這邊設定都要重啟服務才會生效。
5. Run_value : 使用中的值

sp_configure也可以用來變更某個設定值

1. 第一個參數是configname，用來傳遞想要變更設定的名稱。
2. 第二個參數是configvalue，用來傳遞設定的新數值。

因為變更這個config，就要重啟服務或是重啟組態才會生效，所以順帶一提重新設定執行個體有兩種方法。

1. RECONFIGURE : 這個是比較保守的重設，SQL Server會去認為當前改動是合理的，才會讓你執行。舉例：你想設定把Contained databases 關掉，可是目前又有這個database 那SQL Server 會拒絕這次的重設。
2. RECONFIGURE WITH OVERRIDE : 這個是更有強制力的重設，會略過部分合理性檢查，因此風險較高，除非清楚知道結果，否則不要使用。
3. 不論你用哪一種，會引發SQL Server 致命錯誤的也還是禁止重設，例如把最小RAM 設定 超過最大RAM 這種。

如果第一次用sp_configure 通常只會顯示一些參數，但其實可以先用這個語法去把進階選項也打開來看。!

也可以直接去查sys.configurations 去看相同的資訊，但是如果要改參數，還是要用sp_configure。

這張表裏面有一個 is_dynamic 欄位，是1 表示可以用RECONFIGURE 來重設，是0 表示一定要Instance 整個重開才可以。

is_advance 表示那個資料是否要先執行剛剛那個打開進階選項才能看到的。

這裡面有一百多個選項，沒辦法每一個都知道，需要用的時候再查，我會寫一些常用重要還有我知道的。

CPU & RAM

我們要設定Instance 的時候，最先首要考量的事情，應該是處理器和記憶體資源的組態。先講處理器，兩個主要考量點：Processor Affinity、最大平行處理原則。

Processor Affinity

這中文翻譯成親和性，但我是不理解什麼叫做親和性
對了剛好遇到就提一下，有一些中文翻譯，翻得很難讓人明白那功能是什麼，甚至造成誤會，所以有遇到的話我會盡量用英文。

預設情況下，執行個體能夠使用伺服器內所有的處理器核心。

但是，如果你有去使用Processor Affinity，讓特定的處理器核心跟Instance 對齊綁定，那這個Instance 就會永遠只使用這個核心。

也就是說把核心跟Instance 進行綁定，讓Instance 長期的使用這個指定的核心，就叫做Processor Affinity。

做這件事有兩個主要原因

第一

1. 如果同一台伺服器上面有很多個Instance ，在這種架構下，這些instance 會去競爭相同的處理器資源，導致互相阻擋。而Processor Affinity 是用 affinity mask 的設定來控制的。

2. 假設有四個instance 在一台有八核心的伺服器上，你可以去指定instance 1 使用 0、1核心，instance 2 使用 2、3核心以此類推。

3. 但是這個缺點就是如果某個instance 處於閒置狀態，CPU資源就會被閒置浪費。

4. 極度重要的NUMA邊界考量

   • 首先要解釋什麼是NUMA，NUMA全名是Non-Uniform Memory Access 非均勻記憶體存取，這是高階伺服器尤其雙cpu或四cpu主機板上的一種硬體架構。
   • 在以往的舊時代，採用的是UMA架構。

   舉例有一間大辦公室，裡面有8個員工 (8個核心)，而辦公室走廊盡頭有一個公用檔案櫃 (記憶體)，如果今天8個員工都要去拿資料，大家就會都擠在走廊上，排隊開檔案櫃，這就是以前的硬體瓶頸：前端匯流排塞車。而這就是UMA架構。

   為了解決這個問題，老闆把辦公室切成左右兩個部門 ( 兩個NUMA節點 )，左邊部門分派 4個員工，並配發一個左邊檔案櫃；右邊也比照辦理。

   就發生了

   • 本地存取：左邊的員工翻左邊檔案櫃、右邊的翻右邊檔案櫃，因為距離近又不塞車所以速度很快。
   • 遠端存取：左邊的員工發現他要拿的資料剛好放在右邊，他就必須跨過中間的聯絡通道（如Intel UPI），去右邊拿檔案，雖然還是拿的到，但花費時間就會變更長。

   因為去旁邊拿跟去對面拿的速度不一樣，不均勻，所以這就叫非均勻記憶體存取。

5. 因為SQL Server 是具備 NUMA 感知能力的，所以他會盡力做到一件事，當一個查詢被分配到 NUMA Node 0 的 CPU 去運算時，SQL Server 會刻意把這個查詢需要的資料，載入到 NUMA Node 0 專屬的那塊記憶體裡。

6. 所以如果當你去綁定instance 的時候，邏輯 CPU 分別位於不同的 NUMA Node，就可能增加跨 Node 存取記憶體的成本，那時間就會全部浪費在等待跨區傳輸記憶體資料上了，這是設定Processor Affinity 必須要去注意的狀況。

第二

1. 減少作業系統層級的Context Switching
2. 但這幾乎只有在 32位元作業系統中才會受到影響，64位元的作業系統微乎其微，所以綁定核心還是只有第一點影響較多。

設定 Affinity mask

可以從SSMS去設定

也可以用 Alter 去設定

還有一種做法是用上面提到的 sp_configure 去設定
但那個設定方法超難用，所以我不建議用，就不貼出來

MAXDOP

在上一篇安裝的時候，這東西被我跳過，現在要開始說明

MAXDOP 是設定每次獨立執行查詢的時候，可供使用的最大核心數量。

正常的直覺來說，肯定會期待每一筆查詢都盡可能的平行處理已達到最大效能，但事實情況並不總是如此。

某些DATA Warehousing 查詢可能會從高度的平行處理中得到更快的效能，但是許多OLTP例如 : 線上交易處理、電商結帳、進銷存系統) 這種工作，在較低的平行處理之下，效能表現可能會更好。

原因是OLTP都是最簡單的工作，如果今天是大量的簡單工作，一個這種簡單的寫入、查詢，你卻要他跨越多個平行執行續來執行，那假設其中一個執行續花費的時間比其他執行續更久才能完成，其他已經完成的執行續就會閒置在那邊等待最後一個執行續弄好，以便同步他們的資料流，一筆無所謂，但現在是高迸發，你每一筆都要等前面那筆這樣浪費時間，這就是CXPACKET 等待事件。

SQL Server 2017以後還多拆分一個CXCONSUMER 等待事件，等等講。

在很多OLTP 系統中，查詢最佳化程式之所以選擇高度平行處理，實際上也暗示了資料庫可能存在問題，例如：遺失索引、索引高度分散，或是統計資料過期。解決這些問題所帶來的效能提升，會遠大於高度平行化的方式來執行查詢，至於怎麼解決以後再寫，這邊只講平行處理的設定問題。

查詢最佳化、遺失索引、索引高度分散、統計資料過期，這些都是效能調教的東西，現在看不懂正常。

那到底要設定多大的平行處理數，這個必須經過測試才能知道，但是微軟有給一個準則，絕大多是情況下按照這個準則都可以應付。

對於具有單一 NUMA 節點的伺服器：

• CPU核心數量少於 8 個的伺服器，應將 MAXDOP 設定為等於CPU核心的數量。
• CPU核心數量大於 8 個的伺服器，應將 MAXDOP 設定為 8。

對於具有多個 NUMA 節點的伺服器，執行個體的 MAXDOP 應該依照以下規則來組態：

• 每個 NUMA 節點的CPU核心數量少於 16 個的伺服器，應將 MAXDOP 設定為等於每個 NUMA 節點的邏輯 CPU 數量。
• 每個 NUMA 節點的CPU核心數量大於 16 個的伺服器，應將 MAXDOP 設定為 16。

兩種方式去調都可以

還有一件事是跟MAXDOP的設定有關，就是提高查詢最佳化程式，在選擇平行處理而非序列計畫時候的閥值。

首先解釋什麼是平行處理、序列計畫、還有平行處理的成本閥值

假設SQL Server 現在是一家搬家公司老闆，手下有8個工人 ( 8 核心 )

1. MAXDOP 是 出車人數上限

   這決定一個任務最多可以派幾個人去，如果你設定MAXDOP 為 4，代表案子再大，最多還是派 4 個人去搬。

2. 平行處理的成本閥值

   如果今天客戶只是要搬一個紙箱 ( 代表成本極低的簡單查詢 ) ，這時候根本不需要使用到平行處理，然後又因為決定要平行處理所以出動４個人，4人溝通的時間絕對比1人直接抱起紙箱還要久。
   但是今天閥值如果過低，SQL Server 就會每次遇到這種極低成本的查詢還是去做平行處理，你會浪費 CPU。

   MAXDOP 並不是說，決定要平行處理就一定出動全部可以動用的核心，具體要怎麼做還是得看最佳化給出的執行計畫，但他是有可能一次就派出全部的核心浪費的。

3. 因此，必須設定一個門檻 :

   任務難度小於50 > 派 1 個人去 這就是序列計畫

   任務難度大於50 > 派 4 個人去 這就是平行計畫

當SQL Server 收到一段 SQL 查詢的時候，他自己的最佳化程式會先在計算出一個估計成本，然後他再依照這個估計成本去選擇到底要用序列還是平行，這個估計成本就是上面舉例的任務難度。


可以從這裡看現在閥值是多少，預設都是5。

或是也可以用這個查詢去看

5的意思，這是根據微軟1990年代末期的定義，當時電腦就這麼慢，5就是當時的電腦執行這個查詢需要 5 的成本現在電腦速度很快了，當年成本5工作現在來看可能不到0.01秒，所以如果你還是用預設 5 的話，那幾乎代表你所有工作全部給平行處理去做，浪費電腦效能。
一般先從調成50開始測試。

然後，因為這是寫給完全不懂的人看得，有一些東西寫得非常武斷，但那是因為如果寫得太細，會一次要接收太多資訊變得更難看得懂
例如說我上面寫預設 5 的話，那幾乎代表你所有工作全部給平行處理去做，浪費電腦效能。
還有上面第三點，超過多少閥值就交給哪一種計畫
但其實，就算超過 5 最佳化也只是會開始考慮平行處理，並不是一定用平行處理，可是現在如果看到這裡，你根本不知道什麼是最佳化，所以我才那樣寫。

CXPACKET 是 平行查詢中協調等待的總稱之一，雖然在上面的舉例中把它當作一種案例，但其實上面的舉例只是會發生CXPACKET 的其中一種狀況。

1. 在上面的說法是，一次的平行處理，如果有人脫後腿，所有執行續都要等這個托後腿的，這時候發生等待稱為CXPACKET。
2. 但是如果是執行續已經做完了，把資料送給下游的Consumer Threads，而這個Consumer Threads 來不及處理這些資料，但是上游的Producer Threads又一直餵資料近來，造成最後buffer 滿了得等待，這也叫做CXPACKET。
3. 所以由此可知CXPACKET 並不是單一事件造成，他是平行等待沒錯，但有很多種可能會造成平行等待。例如還有單執行緒瓶頸、DOP 過高、Operator 卡住等等。但是這邊不會把全部都寫出來，先把基礎的設定調好，以後才會再寫這些瓶頸是什麼。
4. 以往SQL Server 2017 以前，有一個原因也會被稱為CXPACKET，就是如果今天跟第二點反過來，上游有正常產資料，但是下游在等待上游產資料的這種等待，這種是正常的，產資料的本來就是要等，所以微軟特別把這種狀況標示成CXCONSUMER，並且說看到這個就直接忽略，因為這是正常的等待狀況，不代表效能有問題。
5. 第 4 點說的上游正常產資料跟第 1 點的狀況不同，第 4 點是指上游完全沒有被卡住，沒有人脫後腿的狀況下。

Producet Threads 跟 Consumer Threads
SQL Server 會把查詢轉換成一個 Execution Plan 執行計畫，當執行計畫使用平行處理時，會由多個 Worker Threads 一起執行不同的工作。而在平行執行計畫中，資料常常需要在不同 Worker Threads 之間交換，這時就會出現 Exchange Operator。
Exchange Operator 可以把它想像成一個「資料轉運站」。
在某一個 Exchange Operator 的前一側，負責產生資料並送進 Exchange 的 Worker，稱為 Producer；在 Exchange Operator 的後一側，負責從 Exchange 取出資料並繼續處理的 Worker，稱為 Consumer。
Producer 可能負責掃描資料表 ( FROM )、套用部分篩選條件 ( WHERE )、產生中間結果，然後把資料送進 Exchange Operator。
Consumer 則會從 Exchange Operator 取出資料，接著做後續處理，例如排序、分組、聚合、Join、函數運算，或將多個平行執行緒的結果合併起來。
但是
例如 ORDER BY、GROUP BY、JOIN 本身也可能被 SQL Server 拆成平行處理，不一定永遠只由單一 Consumer 執行緒處理。

Ram

設定記憶體的時候，我個人建議把最大跟最小都設定成相同的值，這樣可以避免SQL Server 在動態管理保留記憶體數量時所產生的額外負擔。

但微軟建議設定成0跟最大

但這邊是在說如果你只有一台instance 的狀況下。

假如今天instance 部署在 active / active cluster 上，就是平常是兩台電腦運行，但是他們組成一個cluster，我看過一個例子，他們啟用Lock Pages In Memory，這表示資料庫會把記憶體鎖的更死一點，不還給作業系統。

然後他們作法也是跟前面說的一樣，把最大&最小都設定相同的值，而且假設伺服器有64G的RAM，他們就設定給資料庫58G RAM，乍看之下很正確。

但有一天發生容錯移轉時，就當機了，因為一台伺服器只有64G RAM，最大最小都設定一樣，代表資料庫一定會用到58G，然後容錯移轉過去變成兩個instance 都要 58G RAM，作業系統沒有RAM 可用，就當機了。

所以到底要設定多少得看當下的環境，還有整個叢集架構，要用最壞的打算去規劃跟設定RAM。

這裡也是一個寫得很武斷的地方，就是設定最小 RAM 就代表一定會用最小 58G，但其實他不會一開始馬上就占用 58G，他是會慢慢成長到 58G 並且不會釋放 RAM。可是這些都太細，所以正文不會寫成這樣，以後我都會當補充用。

通常會將最小與最大記憶體都設定為以下兩個公式中較小的值：

• RAM - 2GB
• RAM * 7/8

Buffer cache

Buffer cache 會儲存資料頁與索引頁，這些頁面可能是在從磁碟讀取之前或之後、或寫入磁碟之前或之後被放入快取中。即使查詢所需要的頁面不在快取中，SQL Server 仍然會先把它們寫入 Buffer cache，然後再從記憶體中取出，而不是直接從磁碟讀取。

Procedure cache

Procedure cache 儲存執行計畫。它不只儲存預存程序的執行計畫，也包含臨時查詢、預備語句，以及觸發程序的執行計畫。當 SQL Server 開始最佳化一個查詢時，會先檢查這個快取，看看是否已經存在合適的執行計畫。

Log cache

Log cache 會在記錄寫入交易記錄檔之前，先儲存交易記錄資料。

Log pool

Log pool 是一種雜湊表，可讓 HA/DR 與資料分散技術，例如 AlwaysOn、Mirroring、Replication，快速存取所需的交易記錄資料。

CLR

CLR 指的是在 SQL Server 執行個體內部使用的 .NET 程式碼。在較舊版本的 SQL Server 中，CLR 位於主要記憶體集區之外，因為當時記憶體集區只處理單一 8KB 頁面的配置。從 SQL Server 2012 開始，記憶體集區可以同時處理單頁與多頁配置，因此 CLR 也被納入主要記憶體集區中。

再次強調，很多東西現在會看不懂，正常，例如說什麼叫做在記錄寫入交易紀錄檔之前，因為那個是有一個動作叫做 COMMIT，但無所謂這邊只要大概知道就好，之後都會有詳細的說明。

Trace Flags

Trace flags 是 SQL Server 內部的開關，可用來開啟或關閉某些功能。

這個東西同時也非常的底層，這不會是當你想要調教SQL Server時後第一個想到的東西，務必要在非常確定這個開關的用途、使用情境與副作用，且在測試環境預先測試過之後，再把他開啟。

這邊只會介紹他怎麼開怎麼關，具體每一個開關是幹嘛的，很難一次寫完。
好在現在有 AI，這種問題問 AI 基本上不會有錯。

在 SQL Server 執行個體內，Trace flags 可以設定在 session 層級，也可以使用 DBCC TRACEON 的 DBCC 指令，將它套用到整個執行個體，也就是 global 層級。

例如上面的舉例，trace flag 634 。設定這個 trace flag 會關閉負責定期壓縮 columnstore index 的背景執行緒。很明顯，這種設定不可能只套用在某個特定 session 上。

Trace flag 1211 會停用根據記憶體壓力或鎖定數量所觸發的 lock escalation。

接著這個舉例使用 DBCC TRACESTATUS 顯示 trace flags 的狀態。

最後，再使用 DBCC TRACEOFF 將行為切回預設值。

如果要指定 global scope，要使用第二個參數 -1。

預設情況下，trace flag 會設定在 session 層級。

使用 DBCC TRACEON 的限制是，即使你用 global scope 設定，它仍然是暫時性的。

當 SQL Server instance 重新啟動後，這些設定不會被保留下來。

因此，如果想對 SQL Server instance 做永久性的設定變更，就必須在 SQL Server service 上使用 -T startup parameter。

絕大多數 trace flags 只在非常特定的情況下才有幫助，所以你真的要很清楚知道自己在做什麼，再來開這個東西。

以下是一些常用的。

Trace Flag 634

上面有說，這是用來關閉 Columnstore Index 的背景壓縮工作。

SQL Server 有一個背景工作叫做 Tuple Mover。

它會定期去處理 Columnstore Index 裡面尚未壓縮的資料，將資料壓縮成 columnstore rowgroup。

而T634，就是去關閉這個背景壓縮工作，如果系統白天交易量很大，而 SQL Server 背景突然去壓縮 columnstore rowgroup，就可能影響正在執行的工作負載。

所以有人會用 T634 先關掉背景自動壓縮，改成自己控制壓縮時間。

但是如果你開T634了，然後你有再用Columnstore Index ，阿又不去手動壓縮，那會導致未壓縮資料一直累積，這時查詢效能就會變差。

這裡又是一個問題了，什麼是Columnstore Index? 他為什麼要壓縮? 之後效能調教會說的。

Trace Flag 1211

這是用來停用 Lock Escalation 。

各家的資料庫都會有很多種LOCK，這邊因為講到Trace Flag 所以只簡單說一下什麼是LOCK。

SQL Server 執行查詢或更新時，會對資料加鎖。

例如更新很多筆資料

一開始 SQL Server 可能會加很多 Row Lock，像這樣
第 1 筆資料：Row Lock
第 2 筆資料：Row Lock
第 3 筆資料：Row Lock
...
第 5000 筆資料：Row Lock

加鎖的目的是要確保在更新的時候其他指令不會影響到這邊來，保證資料的正確性。

但是如果鎖太多了，SQL Server 會覺得，管理這麼多小鎖太浪費記憶體，乾脆升級成一個大鎖，於是Row Lock 就升級成 Table Lock。

這個動作就叫做 Lock Escalation。

而T1211 就是禁止SQL Server 去做這個升級的動作。

打開T1211 的目的就是為了避免大量更新的時候把整張表鎖起來，如果鎖整張表，其他人就會無法在這段時間去SELECT 沒有被UPDATE的資料。

但是有個問題是，如果你禁止升級鎖，那大量更新就會使用到大量的記憶體，而如果沒有足夠記憶體去配置這些鎖，SQL Server會發生錯誤。

所以還有另外一個 T1224，他也是停用升級鎖，但是如果出現記憶體壓力，那仍然可以升級。

一般來說遇到這種鎖的問題不會直接來開這個Trace，除非你非常確定現在是

1. 大量更新造成Table Lock
2. 然後Table Lock嚴重阻塞其他交易
3. 已經確認問題就是 Lock Escalation
4. 測試環境驗證過
5. 可以承受更多的lock memory使用
6. 有在監控lock memory、blocking、deadlock

而且這些以下這些方法已經嘗試過但還是沒有改善這個症狀

1. 把大交易切小批次
2. 建立更適合的索引
3. 縮小update / delete 範圍
4. 調整交易時間
5. 用適合的 isolation level

都沒有用的話再來開這個功能。而且開這個功能最好只開table 層級。

Trace Flag 3042

當 SQL Server 使用 backup compression 來執行備份時，SQL Server 會使用一種 preallocation 演算法。

這個演算法，會根據現在資料庫的大小，先為備份檔案配置一定比例的空間。

這個好處是備份檔案不用一邊備份一邊慢慢長大，可以提升備份的效能。

而T3042 是把這個演算法關掉。

有些人會不希望壓縮備份佔用太多硬碟空間，例如現在資料庫是100G，壓縮備份後可能剩40G，但是如果使用SQL Server 那個演算法，他可能一開始分割硬碟60G來給這備份，有些情況就是不想要多那個20G的空間。

那開啟T3042 就會把 preallocation 演算法關掉，在備份的時候就不會預先去分割空間，而是備份的過程讓檔案逐步長大，缺點就是備份時間會變長。

Trace Flag 3226

預設情況下，每次你執行備份時，SQL Server 都會在 SQL Server log 中記錄一筆成功訊息。

Database backed up. Database: xxx, creation date…

不過，如果你很頻繁地執行 transaction log backup，這些備份成功訊息很快就會在 log 裡造成大量「雜訊」。

這會讓問題排查變得更困難，也更花時間。

如果遇到這種情況，可以開啟 Trace Flag 3226。

這會讓成功的備份訊息不再被寫入 log，讓 log 變得更小、更容易管理。

另一種避免雜訊的方法是建立一個 script，使用 sys.xp_readerrorlog 系統預存程序來讀取 log。

可以把結果寫入資料表，然後篩選出需要關注的事件。

Trace Flag 3625

T3625 會去限制SQL Server 錯誤訊息中的 metadata顯示。

他目的是降低錯誤訊息洩漏資料庫結構資訊的機率，缺點就是錯誤訊息變少、模糊，DBA或是開發人員除錯會很困難。

如果有把T3625打開，那錯誤訊息是這樣

如果用預設的，沒開T3625會是這樣

有開的話可以防止結構、資料外洩，但是真的在開發出錯的話就會很難找到問題是什麼。

Ports & Firewalls

通訊流程

要設定連接埠跟防火牆之前，要先了解整個SQL Server的通訊流程。

1. 通訊流程的起點都是，使用者執行需要SQL 連線的動作
2. Client 向作業系統要求一個port
3. 作業系統分配一個 49152~65535 之間的port
4. 判斷是否為Named Instance

如果不是Named Instance

1. Client 從本機 port 連到 SQL Server 的 port 1433
2. SQL Server 從 port 1433 回應到 client 的本機 port
3. Client session 結束

如果是 Named Instance，還要看 connection string 裡有沒有指定 port。

如果 connection string 沒有指定 port：

1. Client 從本機 port 發送請求到 SQL Server Browser Service 目的地是 UDP port 1434
2. Browser Service 從 UDP port 1434 回傳該 named instance 使用的 port number
3. Client 再用取得的 port 連線到 SQL Server instance

如果 connection string 已經指定 port：
Client 直接使用指定的 port 連線不需要再問 SQL Server Browser Service

如果希望 client 使用 Named Pipes 存取 SQL Server，而不是 TCP/IP，那 SQL Server 會透過 port 445 通訊。

這個 port 也是 Windows 檔案與印表機共享所使用的 port。

SQL Server 需要的port

如果是安裝default instance的話，他自動會是1433。

但是為了安全這個port 要改掉，因為全世界都知道1433是預設port。

但如果是 name instance 的話，安裝的時候會啟用 dynamic ports。

但是如果你用dynamic port的話，windows 防火牆層級就會很難開，因為每次重啟這個port都會變，所以還是跟前面一樣給他一個固定port。

列出幾個我知道的Port，以下這些都是預設，強烈建議如果有需要使用這些功能，不要用預設，很容易就被扁。

1. Browser Service UDP 1434
   這類似於一個查號台，當你今天是named instance，然後又不知道是實際上使用哪個port的時候，client會先來問這個Service。
2. Instance over TCP/IP TCP 1433
   這就正常連線用的
3. Instacne over named pipes TCP 445
   這代表如果 SQL Server 使用 Named Pipes 通訊，而不是 TCP/IP，它會走 Windows SMB 相關通道
4. Dedicated Administrator Connection TCP 1434
   這是 SQL Server 給的緊急管理通道。當 SQL Server 很忙、一般連線進不去時，可以嘗試用 DAC 連進去做緊急處理。
5. Service Broker TCP 4022
   Service Broker 是 SQL Server 裡的訊息佇列 / 非同步訊息功能。可以把它想成 SQL Server 內建的 message queue。
6. AlwaysOn Availability Groups TCP 5022
   這就高可用的Port
7. Merge replication with Web sync TCP 21、TCP 80、UDP 137、UDP 138、TCP 139、TCP 445
   這是 Merge Replication 搭配 Web Synchronization 可能用到的 ports。
8. T-SQL Debugger TCP 135
   用來 debug T-SQL 的功能。

幾個重要的系統DB

mssqlsystemresource

用來存放出現在每個資料庫 sys schema 中的 system objects 系統物件。

是 唯讀 的，而且除了在 Microsoft 指導下之外，絕對不應該修改它。

不會顯示在 SQL Server Management Studio 裡。

如果嘗試從 query window 連線到它，通常會失敗，除非 SQL Server 處於 single-user mode 單一使用者模式。

對於我們這種一般使用者而言，沒有需要特別設定的事項

MSDB

MSDB 是許多 SQL Server 功能用來儲存 metadata 的資料庫

雖然 MSDB 很明顯是一個重要且有用的資料庫，但它沒有特別需要考慮的設定選項。

不過，如果是在非常大型的 SQL Server instance 中，裡面有大量資料庫，而且所有資料庫都經常執行 log backup，那 MSDB 可能會變得非常大。

這代表需要定期清除舊資料，有時候也需要考慮索引策略。

要怎麼清以後會講。

他通常用來存下面這些功能

SQL Server Agent

Backup / Restore

Database Mail

Log Shipping

Policies

以及更多功能

Master

SQL Server 的系統資料庫，裡面存放 instance-level objects 執行個體層級物件 的 metadata。

例如

Logins
Linked Servers
TCP endpoints
master keys
certificates

對Master 最重要的考量是備份策略

資料庫進行以下動作之後，應該要立即備份Master

建立或修改 logins
建立或修改 linked servers
修改 system configurations
建立或修改 keys / certificates
建立或刪除 user databases

還有不要把使用者物件放在Master上，看過有人把sp放在Master裡，原因是所有database都要共用這個sp。但這樣做會增加複雜度，使用者物件儲存位置被打散、Master需要更頻繁備份。

Model

Model 是 SQL Server instance 上所有新建資料庫的範本。

也就是說，當在這個 SQL Server instance 裡建立新的 user database 時，SQL Server 會以 model database 為基礎，複製它的部分設定到新資料庫。

因此，花一點時間設定 model database，可以在之後建立 user database 時節省時間，也可以減少人為錯誤。

例如 : Recovery Model、或是希望每個資料庫都自動存在物件例如sp、database role

這種就可以建在Model，之後建立的database就都會自動建好。

**TempDB**

已經寫過很多次TempDB有多重要，這邊會詳細寫TempDB在做什麼。

首先這是SQL Server 需要建立暫時物件的時候使用的工作空間。

通常一般人理解都是#TempTable 這種暫存表，但比較少人知道還有包刮 table variables 資料表變數，雖然這東西聽起來很像是在記憶體中使用，但她仍然會在TempDB裡面建立物件；只是在資料達到特定大小門檻的時候，才會被寫入磁碟。

另外SQL Server 還有很多原因會需要建立暫時物件例如 :

1. 排序
2. Hash運算，JOIN & Aggregate Grouping
3. Online Index Operations
4. 索引操作，尤其是結果需要在TempDB中排序
5. Triggers
6. DBCC Commands
7. DML 語句中的 OUTPUT 子句
8. Row Versioning，例如 : Snapshot Isolation、Read Committed Snapshot Isolation、MARS 查詢

正因如此 TempDB 非常的忙碌，所以效能對整個 Instance 非常重要；由於 TempDB 負責這麼多工作，在高流量的 Instance 中，他會有非常高的吞吐量，因此 TempDB 裁示應該花最多時間去設定的系統資料庫，目的是確保 data-tier applications，可以得到最好的效能。

首先要調整的就是 TempDB 的大小

理想情況下，對於大型或高交易量的 Instance ，TempDB 應該要做容量規劃，規劃的方法大致如下：

1. 先用一台測試SERVER ，把這個 Instance 上所有的 user database 擴展到預期會成長到的大小

2. 讓這些資料庫執行代表性的 workload，並監控 TempDB 的使用情況

3. 同時也要對資料庫職行管理工作，例如重建索引

4. 然後再從這些活動期間，查看TempDB的使用量是什麼樣子

5. 有許多DMV可以協助規劃。
   sys.dm_db_session_space_usage
   顯示目前每個session 已配置的頁面數量
   使用者與系統資料表
   - 使用者與系統索引
   - 暫存資料表
   - 資料表變數
   - 暫存索引
   - 函數回傳的資料表
   - 用於排序與雜湊作業的內部物件
   - 用於 spool 與大型物件作業的內部物件

   sys.dm_db_task_space_usage
   顯示由 task 配置的頁面數量。這會包含與sys.dm_db_session_space_usage 相同物件類型的頁面數量。

   sys.dm_db_file_space_usage
   顯示資料庫中所有檔案的完整使用資訊，包含頁面數量與 extent 數量。若要回傳 TempDB 的資料，必須在 TempDB database 的 context 中查詢這個 DMV，因為它也可以回傳 user databases 的資料。

   sys.dm_tran_version_store
   針對 version store 中的每一筆記錄回傳一列資料。可以檢視這些資料列，或彙總它們以取得大小總計。

   sys.dm_tran_active_snapshot_database_transactions
   針對目前每一個可能需要存取 version store 的交易回傳一列資料，原因可能是 isolation level、triggers、MARS（Multiple Active Results Sets），或 online index operations。

我真的很想寫給一個完全沒用過SQL SERVER 的人看的董，但真的很難，例如到現在又出現一堆新名詞，所以到這裡只要知道 TEMPDB 非常重要也就可以了。

Buffer Pool Extension

Buffer Pool 是 SQL Server 使用的一塊記憶體區域，用來在頁面寫入磁碟之前，以及從磁碟讀取之後，快取這些頁面的地方。

在 buffer cache 中有兩種類型頁面

1. claen pages
   • 這是指沒有修改過的頁面，通常存在cache 之中，因為她曾經被讀取-作業存取過，例如 SELECT 語法
   • DML 語法可以存取 clean pages、修改，然後更新他的 dirty page 標記
2. dirty pages
   • 這是已經被語法修改過的頁面，例如insert、update、delete。
   • 這些頁面必須先將其相關的 log record 寫入磁碟，接著 dirty pages 本身也會被 flush 到磁碟，之後才會被視為 clean。
   • 先寫入 log record 的程序叫做 WAL，這是 SQL Server 確保已經提交資料不會遺失的方式，即使發生系統故障也是如此。
   • dirty pages 會一直保留在 cahce 中，直到他們被flush到磁碟。
   • SQL Server 會根據 least-recently-used policy，來淘汰頁面；而這也表示如果 buffer cache 沒有正確調整大小，讀取密集型工作負載可能會很快開始遭遇記憶體壓力。
   • 然而現實的問題是，RAM相較於儲存設備來說較昂貴，因此不可能一直透過增加記憶體來解決問題，所以SQL SERVER 2014 中引入這一個技術 Buffer Pool Extension。

這個東西要搭配非常快速的SSD，這個Extension 會成為只用於 clean pages 的第二層快取。

當 clean pages 從 cache 中被淘汰時，他們會被移動到 buffer pool extension 中，在這裡可以比回到主要 I/O 更快的被取回。

這功能有兩個注意事項

1. 必須記得，buffer pool extension 永遠無法提供與正確配置大小的 buffer cache 相同的效能改善，原因就是因為這是硬碟不是RAM，物理上速度就是有差。

2. 使用 buffer pool extension 所得到的效能提升取決於你在做什麼樣的工作。例如：讀取密集型 OLTP 工作附載可能會從 buffer pool exteinsions 中獲得顯著效益；但寫入密集型工作附載幾乎不會有任何幫助。

   這是因為 dirty pages 無法被 flush 到 extension

   大型資料倉儲也不太可能從 buffer pool extensions 中獲得顯著效益；這是因為資料表很可能大到完整的 table scan，在這類工作負載情境中很常見，可能會消耗 cache 與 extension 中的大部分空間。表示它會清除 extension 中的其他資料，而且不太可能讓後續查詢受益。

然後把這個 Extension 的 SSD 做RAID 也是一種常見的作法。

最後我建議把這個 Extension 設定成 instance 記憶體最大值的四到八倍。

Hybrid Buffer Pool

這是一個SQL SERVER 2019 才引進的功能，並且到2022加強。

Hybrid Buffer Pool 是讓 SQL Server 可以直接使用 PMEM 上的資料頁，不一定要先把資料頁複製進 DRAM 的 Buffer Pool，藉此降低 I/O 延遲。

正常SQL Server 讀資料大約是這樣 :

1. 硬碟上的 data file
2. 讀進 DRAM 的 Buffer Pool
3. SQL SERVER 查詢使用

Hybrid Buffer Pool 則是 :

1. PMEM 上的 data file
2. SQL Server 直接 memory-map 存取
3. 不一定複製進 DRAM

正常來說，常用的資料頁會從硬碟讀取到 DRAM，但這過程就會比較慢因為有碰到硬碟。

PMEM 是持久性記憶體，速度介於 DRAM 到 SSD 之間，他接近記憶體匯流排上、不是機械硬碟、可以像記憶體一樣用 byte 為單位存取、斷電後資料仍保留。

一般 Buffer Pool 只有 DRAM。

Hybrid Buffer Pool 則讓 SQL Server 的 Buffer Pool 可以「引用」PMEM 上 database files 裡的 data pages。

Microsoft 官方說法是：Hybrid Buffer Pool 讓 buffer pool objects 可以 reference 位於 PMEM 裝置上的 database files 中的 data pages，而不是一定要從 disk 取回一份 page copy 並快取到 DRAM。

傳統的 I/O：

1. SQL Server 要 page
2. 作業系統去讀檔案
3. 作業系統從 storage 把資料搬到記憶體
4. SQL Server 使用那份記憶體資料

memory-mapped I/O：

1. 把檔案的一部分映射到記憶體位址空間
2. 程式像讀記憶體一樣讀檔案內容

同樣的這只能用在 clean page

然後要開Trace Flag 809

這篇我知道，一定很多東西看不懂，而且這屬於INSTANCE 的東西在大多數已經運行的DB中，也不好去變動，就當看過知道有這東西，以後需要的時候知道可以往什麼方向去處理就好了。

下一篇 : 資料庫檔案設定