這邊開始寫資料庫層級的設定
在一個資料庫中，資料會儲存在一個或多個資料檔案中。這些檔案會被分組到稱為 filegroups 的邏輯容器中。
每個資料庫也至少會有一個 log file。
Log file 位於 filegroup 容器之外，並且不遵循與 data files 相同的規則。
在考慮要採用哪一種 filegroup 策略之前，需要先了解SQL SERVER 是如何儲存資料的。
資料庫永遠至少包含一個 filegroup，而且這個 filegroup 至少包含一個檔案。
第一個檔案叫做 primary file 副檔名是 .mdf，除了存資料以外也會有來存 metadata；primary file 的 filegroup 稱為 primary filegroup。
如果額外建立檔案 secondary files 副檔名就是 .ndf；這些檔案可以建立在 primary filegroup，但也可以建立在 secondary filegroups 中。
Secondary files 和 filegroups 是選用的，但它們對資料庫管理非常有用
Table 跟 Index 都是存在 filegroup 上的，不是存在某個特定檔案上。
所以對於包含多個檔案的 filegroup，我們無法控制物件會被存在其中哪一個檔案中。
SQL SERVER 使用的是 round-robin 方式將資料配置到檔案中，所以存在 filegroup 的每一個物件，都有可能被分散到 filegroup 內的每個檔案中。
file_id 不會有 2，因為2永遠都是 transaction log
順帶一提，上面使用的 physloc 函數是未公開文件記載的功能。因此，Microsoft 不會為它們的使用提供支援。
標準資料與索引會儲存在一系列 8KB pages 中；這些 page 由一個 96-byte header 和 8096 bytes 的資料儲存空間組成。
96-byte header 內含關於該 page 的 metadata。
這些 8KB pages 接著會被組織成由八個連續 pages 組成的單位，合稱為一個 extent。
一個 extent 是 SQL Server 可以從磁碟讀取的最小單位。
metadata 中文叫做中繼資料，裡面存了一些關於這個 page 的資訊，例如這個page存在哪裡、有多大、存多少筆資料等等還有很多
Filestream 是一項技術，可以非結構化的方式儲存二進位資料。
二進位資料就是PDF、圖片、EXCEL那類的東西，以往效能很差的方式是直接把二進位存到資料庫欄位；後來有一種效能好一點的方式是資料庫存檔案路徑，但這有個問題是資料庫無法去操作這個檔案，在維持一致性的時候會有有可能會有一點小問題。
所以SQL Server 推出FILESTREAM功能，可以讓你存二進位物件並且不會有效能問題，而且可以超過傳統存取方式的最大2GB限制。
如果檔案大小有超過1 MB，用Filestream 讀取可能會更快。
但是，Filestream儲存的物件會使用 Windows cache，而不是 SQL Server buffer cache；好處是不會讓大型檔案填滿 buffer cache，導致其他資料被 flush 到 buffer cache extension 或硬碟。
但要注意，如果決定要用 Filestream 那你的 instance 的 max servere memory 就要小一點，因為要讓 windows 有額外的記憶體。
Filestream 需要獨立的 filegroups。
下面是示範怎麼把圖片放進 filestream 欄位的語法
我用UNIQUE constraint，而不是 primary key，因為 GUID 通常不是 primary key 的好選擇。
如果這張表一定要有 primary key，比較合理的做法可能是新增一個指定 IDENTITY 屬性的 integer 欄位。
使用 GUID，並設定 ROWGUIDCOL 屬性，是因為 SQL Server 需要這個欄位來對應到 FILESTREAM objects。
FILESTREAM 不是為了讓速度變快，是為了讓大型檔案在交易時保持一致性，如果你沒有一致性的需求，就轉去用存路徑的方式，可以減少維護這一塊。
SQL Server 有一個功能叫做 memory-optimized tables，這些資料表會完全的存在記憶體中；不過，資料也會被寫到硬碟上，為了提供持久性。
in-memory tables 跟 in-memory transactions 以後會寫，這編寫file 而已。
Memory-Optimized Table 記憶體最佳化資料表比較特殊，它是 In-Memory OLTP 用的資料表。
它的資料主要存在記憶體中，讓讀寫速度變快。
可是如果資料表是 結構和資料都要保留，SQL Server 就必須把資料保存到磁碟上。
這時候就需要一個特殊的 filegroup，就叫做Memory-Optimized Filegroups。
這種類型的 filegroup 類似於 FILESTREAM filegroup，但有一些細微差異。
第一，每個資料庫只能建立一個 memory-optimized filegroup。
第二，除非你打算同時使用這兩種功能，否則不需要明確啟用 FILESTREAM。
In-memory data 會透過兩種檔案類型持久化到磁碟：
這兩個檔案永遠成對運作，並涵蓋特定範圍交易，所以牠們的數量應該永遠相同。
我們再採用不同的filegroup 策略的時候，會考慮這些事情，來決定要用哪一種方式
為了效能去設計 filegroup 策略的時候，要考慮的是物件放置位置和應用程式查詢所執行的 join 之間的關係。
核心重點：單一 filegroup 多個 ndf，可以分散 I/O；多個 filegroup，除了分散 I/O，還可以控制，哪一個物件使用哪一組 I/O。
舉例一個大型資料倉儲
不過這裡會出現一個問題是，即使 I/O 用第三點的做法可以被分散，但是仍然無法細緻控制那些 TABLE 要被放在哪一個 logical unit numbers 上
那到底要單一 filegroup 讓資料庫自己去等比例平均分配資料到每個硬碟，還是要多 filegroup 自己去控制哪一個 table 要到哪一個硬碟?
選擇策略的依據是依照實際情況，並不是有一個唯一指標哪個最好，通常我會分下面幾個目的來建議做法。
| 目的 | 建議 |
|---|---|
| 平均分散 I/O | 單一 filegroup + 多 data files |
| 單一大表最大化掃描吞吐量 | 該表所在 filegroup 放多個 data files |
| 控制某張表放特定磁碟 | 多 filegroup |
| 控制某個索引放特定磁碟 | 多 filegroup |
| 冷熱資料分層 | 多 filegroup |
| 歷史資料封存 | 多 filegroup，搭配 read-only |
| 縮短完整備份壓力 | 多 filegroup，搭配 filegroup backup |
| 縮短災難還原時間 | 多 filegroup，搭配 piecemeal restore |
| Partition 依月份 / 年份管理 | 多 filegroup |
| 一般公司系統、不確定需求 | 單一 filegroup 即可 |
最好的做法是，不要過度設計，分散 ndf 即可；有冷熱分層、歷史資料再說，或是有規劃 partition、平行備份等等。
檔案分群還有另一項好處是備份還原的時候可以更彈性。
SQL Server 可以在檔案跟檔案群組層級進行備份，也可以在資料庫層級進行備份。
因此他可以執行所謂的分段還原，分段還原可以用分階段的方式，把資料庫恢復上線。
例如現在有一個大型資料庫，其中包含比較少量的關鍵資料。
在這種情況下，最好的做法是建立兩個 filegroup。第一個群組存放關鍵資料，第二個群組存放歷史資料。
當災難發生時，就可以先還原 mdf 跟 第一個群組，此時資料庫就可以重新上線，之後再去處理要還原比較久的歷史資料。
第二種狀況是，如果大型資料庫，完整備份要花兩小時，但是現在只有每天晚上一個小時的時間窗口可以備份資料。
那也可以把資料分散到 filegroup 之中，然後分星期去備份。
關於備份，之後會有一篇詳細說明備份。
有些組織可能會決定，想要針對大型資料庫實作儲存分層。如果是這種情況，通常需要透過資料分割來實作。
例如，假設某張資料表包含六年份的資料。目前年度的資料每天會被存取與更新很多次。
前三年的資料會用於每月報表，但除此之外很少被碰觸。
最早到六年前的資料，若因法規需求而需要時，必須能夠立即取得；但在實務上，這些資料很少被存取。
在剛才描述的情境中，可以使用按年度分割的分割區。包含目前年度資料的檔案群組，可以由位於本機、並連接到 RAID 10 LUN 的檔案所組成，以取得最佳效能。
存放第 2 年與第 3 年資料的分割區，可以放在企業 SAN 裝置的高階儲存層。超過三年以上的舊資料分割區，則可以放在 SAN 內的近線儲存上，藉此用最具成本效益的方式滿足法規要求。
在上面有提到過記憶體也可以做為檔案群組，但她不是只放在一個檔案裏面，是放在多個CONTAINER裡
Container 可以理解成 memory-optimized filegroup 裡面的一個儲存位置。
當我們再建立 moemory-optimized filegroup 時可以指定
這個 D:\Data\MyDB_mod1 ，就可以理解成一個 container。
這東西比較像是一個 SQL Server 管理的資料夾，他會在裡面一直建立 Data/Delta 兩種 file。
Data 跟 Delta
是 container 主要放的兩種檔案，他們永遠都會成對出現，稱為 checkpoint file pair。
Data file :
存放實際插入或更新後的新版本資料
Delta file :
紀錄那些資料已經被刪除或是失效，刪除的資料 SQL Server 不一定會馬上從 Data file 刪除，而是會再 Delta file 裡面紀錄。
Data \ Delta 存在的理由
因為 memory-optimized table 的設計目標是高速寫入和高迸發，如果每次刪除資料，都要去修改原本的 Data file，就會變得很慢，所以 SQL Server 採用比較接近 append-only 的方式，不要一直回頭改舊檔案，而是一直往後追加紀錄，速度會比較快。
整個 Meomry-Optimized Filegroup 運作流程
這邊說明的是假如 table 是 SCHEMA_AND_DATA，就是在記憶體裡操作的表要存到實體表上的意思
Database
│
├── Transaction Log
│
├── Regular Filegroup
│ ├── .mdf
│ └── .ndf
│
└── Memory-Optimized Filegroup
├── Container 1
│ ├── Data file
│ └── Delta file
│
└── Container 2
├── Data file
└── Delta file
資料庫重新啟動後的恢復流程
那麼這個群組策略要怎麼建立?
假設現在有兩個硬碟，然後一個硬碟放一個 container
因為 Round-Robin 機制，所以 SQL Server 會輪流放置，導致硬碟1有很多Data file、Data file，硬碟2 有很多 Delta file、Delta file
造成這樣的原因是因為 Round-Robin 他是依照檔案建立的順序，去分配的，所以如果硬碟數量是偶數，那他就會 Data、Delta、Data...這樣下去分，就會導致一邊都是Data，另一邊都是 Delta。
但是一般來說 Data file 的使用比例會非常高，如果這樣去分配 container，內就會導致 I/O失衡，硬碟1很忙碌，硬碟2沒事做。
所以微軟建議，如果是偶數硬碟數量，則在每一個硬碟上都建立兩個 Container 以達到 I/O 平衡。
新增檔案的重點只有一個，要記住 proportional fill 這個演算法。
如果資料庫運行到一半，你會想要新增檔案，要馬效能問題想要分散 I/O，要馬就是實體空間滿了。
所以那個比例填滿演算法就很重要，因為如果你的檔案現在是100G，然後你也新增一個新的 ndf ，直接給他 100G 初始空間，那這樣效能問題不會馬上解決，因為根據這個演算法，接下來SQL Server 所有資料都會丟到這個新 ndf 裡面，你等於還是沒有新的 I/O。
所以新增檔案的時候，起始大小 跟 成長大小，最好是設定跟其他檔案一模一樣。
這個在tempdb那邊有說過，當時我就建議要設定一模一樣，是一樣的意思
語法如下
每當檔案滿的時候，SQL Server 會自動把檔案擴大，這算是一個故障保護機制，我會這麼說是因為檔案其實是可以手動擴大的。
手動擴大的語法如下
但不是每個人都有辦法隨時監控、隨時去調大小。
所以這個自動成長相當好用，說是故障保護是因為如果你沒有去調大小，新資料塞不進去了，那就故障了；一方面又希望不要太過依賴這個東西。
因為檔案成長是會消耗效能的，如果你去設定每次成長1MB，那SQL SERVER 每天都做成長檔案的動作，效能超低，如果你設定一次成長 10 TB，那可能漲的兩三次你就沒空間了。
所以一次漲多少，現在空間有多少，有多少已經切出來但是還沒用的空間，這些都是需要監控的，所以不監控反而去依賴檔案自動成長，這樣是本末倒置，所以我才會說這是一個故障保護機制。
可以用這個語法去看空的空間
縮小檔案，不是指真的把一樣的資料內容縮小，是把多的空間釋出。
要縮小單一檔案可以用 DBCC SHRINKFILE，可以指定檔案的目標大小，或是使用 EMPTYFILE。
EMPTYFILE 選項會把檔案內的所有資料移動到同一個檔案群組中的其他檔案；代表當動作完成的時候你就可以把這個檔案砍了。
如果選了指定檔案目標大小，那還有另外兩個選項 TRUNCATEONLY 或 NOTRUNCATE 可選。
TRUNCATEONLY
這會把檔案從尾端開始，回收空間，直到遇到最後一個 extent 為止。
NORTUNCATE
這會把檔案從尾端開始，把 extents 移動到檔案開頭的第一個可用空間。
但是，縮小資料庫，甚至只是縮小一個檔案，再實務上很少有可以接受的使用時機。
一般來說，不應該考慮縮小資料庫檔案，而且絕對不要在資料庫上使用 Auto Shrink 選項；如果真有必要，要有心理準備，這過程會非常慢。
因為他是單執行續的作業，而且執行期間會消耗資源。
雖然 SQL Serever 2022 之後，多一個 WAIT_AT_LOW_PRIORITY可以選，但也只是緩解不是真正的解決，這功能是避免縮小程序的等待階段中，那些需要 schema modify lock 的查詢被阻塞。
相反的，這也代表如果不是2022以後，或是2022以後的版本但是你沒有開這功能，那就會在 shrink 作業進入執行階段的時候，無法取得 schema modify lock，他會等待一分鐘，然後逾時，然後靜默失敗，重新執行。
還有真的非得要用的話，絕對不要用 NORTUNCATE 選項，他會導致非常嚴重的碎片化。
以下會介紹一些資料庫範圍組態
在以前可以用 T1117、T1118 來控制自動成長事件的預設行為。
T1117 :
用來讓同一個檔案群組內的所有檔案同時成長。這對於平均分散資料非常有幫助，尤其是在資料倉儲情境中。
T1118 :
用來強制只使用 uniform extents，本質上就是關閉 mixed extents。
混合範圍指的是：同一個 extent 裡不同的頁面，可以被配置給不同的資料表。T1118 對於最佳化 TempDB 很有用，在資料倉儲情境中也可能很有幫助。
但是在新版本的 SQL Server 中，把這兩個打開也不會有作用，因為他們已經被 Database Scoped Configurations 取代。
這東西有兩個優點
在 TempDB 資料庫中，T1117 和 T1118 的等效行為預設就會被採用。不過，對於使用者資料庫，仍會採用傳統的預設行為。
允許資料庫引擎在發現同一個查詢先前執行時有效率不佳的情況下，調整該查詢的平行處理程度。
MEMORY_GRANT_FEEDBACK_PERCENTITLE：引入了一種演算法，會根據某個查詢過去多次執行的結果來設定記憶體授與量。這有助於最佳化那些記憶體需求變化很大的查詢所需的 memory grant。
MEMORY_GRANT_FEEDBACK_PERSISTENCE：允許某個查詢計畫的 memory grant 資訊被持久保存，即使該計畫已經從快取中移除也一樣。
所有資料庫範圍組態，以及它們目前針對 Primary 和 Secondary database 設定的值，都可以透過查詢取得。
這些看似都很好的功能，但是微軟不預設開啟是有理由的，因為這不是越開越好。
AUTOGROW_ALL_FILES ，假如有8個 ndf，一次長10g，這功能開下去就會一次漲80G
MIXED_PAGE_ALLOCATION 對大型表、高併發、TempDB 這類場景很好，但對很多小物件的資料庫，可能會比較浪費空間。
DOP_FEEDBACK 問題是：自動調整不一定每次都符合你的業務需求。
Memory Grant Feedback 的目標是根據過去執行狀況，自動調整下次給多少記憶體。問題是，有些查詢的記憶體需求會劇烈變化。例如 WHERE nn = 1 只有10筆、WHERE nn = 9 有 99999筆，那記憶體就會分配不夠了。
所以一再強調，這些底層的設定，除非你真的知道在做什麼以及後果，再去調整。
下一篇 : LOG 維護
iThome鐵人賽
看影片追技術