官方文件https://learn.microsoft.com/zh-cn/sql/relational-databases/sql-server-guides?view=sql-server-2017

1、記憶體結構
SQL SERVER 記憶體空間主要可分為：
編譯記憶體、緩衝池Buffer Pool、查詢執行記憶體授予、鎖管理器記憶體、 CLR1 記憶體
緩衝區管理元件由下列兩種機制組成：用於訪問及更新資料庫頁的緩衝區管理器和用於減少資料庫檔案 I/O 的緩衝區快取記憶體（又稱為“緩衝池”） 
緩衝區管理器主要與下列元件互動：
資源管理器。此互動用於控制記憶體的整體使用情況以及 32 位平臺中的地址空間使用情況。
資料庫管理器和 SQL Server 作業系統 (SQLOS)。此互動用於低階檔案 I/O 操作。
日誌管理器。此互動用於預寫日誌記錄。

2、物理結構
系統資料庫啟動順序master-->model-->tempdb--msdb
msdb依賴tempdb，tempdb依賴model，model依賴master 
因為資料庫每次重啟都會重建tempdb，tempdb是使用model作為模板來建立的，如果model無法啟動，則啟動tempdb也會報錯Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

tempdb無法啟動的話，資料庫例項無法啟動
tempdb資料庫的資料檔案和日誌檔案被刪除後，重啟後會自動建立tempdb的資料檔案和日誌檔案

msdb無法啟動的話，資料庫例項正常啟動，服務方面sqlserver service和sqlserver agent service都正常啟動，只是msdb資料庫狀態為Recovery Pending並且在SSMS中SQL Server Agent顯示Agent XPs disabled。

master：記錄SQL Server例項的所有系統級資訊。
msdb：用於 SQL Server 代理計劃警報和作業。
model：用作 SQL Server例項上建立的所有資料庫的模板。 對 model 資料庫進行的修改（如資料庫大小、排序規則、恢復模式和其他資料庫選項）將應用於以後建立的所有資料庫。
tempdb ：用於儲存臨時物件或中間結果集。

3、資料完整性校驗
CHECKSUM:
Checksum protection 校驗和保護
SQL Server 2005 (9.x) 中引入的“校驗和保護”提供了更強大的資料完整性檢查。 此方法將對寫入每一頁中的資料進行校驗和計算並將其值儲存在頁頭中。 每次從磁碟讀取儲存了校驗和的頁時，資料庫引擎將重新計算頁中資料的校驗和。如果新的校驗和不同於已儲存的校驗和，則引發錯誤 824。 校驗和保護比殘缺頁保護能捕獲到更多的錯誤，因為它受到頁中每個位元組的影響，但它對資源的消耗較多。啟用校驗和後，當緩衝區管理器從磁碟讀取頁時均可以檢測到因電源故障以及硬體或韌體故障導致的錯誤。

TORN_PAGE_DETECTION:
Torn page protection 殘缺頁保護
SQL Server 2000 (8.x) 中引入的撕裂頁面保護主要是由於電源故障而檢測頁面損壞的方法。 例如，意外電源故障可能導致只有部分頁寫入磁碟。 使用殘缺頁保護時，在將8KB的資料庫頁寫入磁碟時，該頁的每個 512 位元組的扇區都有一個特定的 2 位簽名模式儲存在資料庫的頁頭中。從磁碟中讀取頁時，頁頭中儲存的殘缺位將與實際的頁扇區資訊進行比較。 如果值不匹配，表明只有頁面的一部分被寫入磁碟。如果一個頁在寫入持久化儲存的過程中，只寫了一半的頁，這就是所謂的TORN_PAGE_DETECTION，SQL Server透過每個扇區提512位元組中前2位作為後設資料，總共16個扇區32位4位元組的後設資料(頁頭中標識為:m_tornBits)，透過該後設資料來檢測是否存在部分寫的TORN_PAGE，但該型別的頁驗證無法檢測出頁中的寫入錯誤，因此在SQL Server 2005及以上版本，儘量選擇CheckSum。

NONE:資料庫頁寫入不會生成 CHECKSUM 或 TORN_PAGE_DETECTION 值。 在讀取過程中，即使頁頭中存在 CHECKSUM 或 TORN_PAGE_DETECTION 值，SQL Server 也不會驗證校驗和或頁撕裂。

當使用者或系統資料庫升級到 2005 SQL Server 2005 (9.x) 或更高版本時，將保留PAGE_VERIFY值 (NONE或TORN_PAGE_DETECTION) 。 強烈建議使用 CHECKSUM 校驗和保護。 TORN_PAGE_DETECTION 殘缺頁保護可能會使用較少的資源，但提供保護的 CHECKSUM 校驗和保護最小子集。

4、資料頁結構
資料頁包含資料行。索引頁包含索引行。資料行就由行管理器來控制。而索引行，由索引管理器來負責。
　　而單行上的檢索、修改、執行，又被事務管理器和鎖管理器影響著。事務，有顯性事務和隱性事務兩種。而鎖，又有共享鎖、排它鎖、更新鎖、意向鎖。而鎖，還分為行鎖、頁鎖、表鎖、資料庫鎖。而鎖，又有死鎖的可能性。鎖的不同，加上事務的影響，這個行是否能讀、能修改，能怎樣的讀（讀一致還是髒讀），是等待事務和鎖，還是可以進行，就受了很多影響。因為一張資料頁上放的行是有限的，尤其還有填充度的影響（如填充度為80%，就這個資料頁面只能填充80%就必須分頁，以防以後有資料插入的時候，就非常影響資料插頁，這也是效能影響比較大，尤其在插入資料比較多的情況下）。SQLSERVER的一張資料頁預設是8K，除去填充度和資料頭，也沒有多少可儲存的資料了。這就是為了關係型資料庫都勸阻大家要小表大資料。也就是說，列要少，列要短，頻繁訪問的列要在前。資料可以海量。如果行長了，你想要檢索和更新多少資料頁，這需要多少頁面排程，面臨著頁面失效和鎖機制的影響。而且，大文字和可變行，都是指標儲存，需要跳轉查詢，更浪費了不少時間。

5、表或索引的壓縮選項，壓縮索引時，可以使用行壓縮和頁壓縮來壓縮葉級頁。 非葉級頁只能使用行壓縮。
DATA_COMPRESSION = {NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE}
NONE：不壓縮表或指定的分割槽。 此選項不適用於列儲存表。
ROW：使用行壓縮來壓縮表或指定的分割槽。 此選項不適用於列儲存表。
PAGE：使用頁壓縮來壓縮表或指定的分割槽。 此選項不適用於列儲存表。
COLUMNSTORE：適用於SQL Server（SQL Server 2014 (12.x) 及更高版本）和 Azure SQL 資料庫。僅適用於列儲存表。
COLUMNSTORE_ARCHIVE：適用於：SQL Server（SQL Server 2014 (12.x) 及更高版本）和 Azure SQL 資料庫。僅適用於列儲存表。

6、沒有undo檔案和transaction log的備份和截斷理解
1、sqlserver的undo資訊記錄在temp檔案中和log檔案中，temp檔案記錄事務的行版本，log檔案記錄事務修改動作發生之前的表的前像
2、日誌備份只能截斷非活動的日誌，如果一個事物長時間執行，此時備份事物日誌將不會引起截斷髮生，因為擴充套件分割槽是一個事務，該事務耗時10天，這10天內日誌在不停的增加並且因為這個事務最初發生時佔用的虛擬日誌檔案(VLM)中存在活動日誌(尾日誌)，導致首日誌(最新的活動日誌序號)和尾日誌(保留時間最長的活動日誌序號)涉及的VLM都可能有這個事務的資料，因為VLF中存在活動日誌(哪怕只有一條)，所以資料庫無法利用這個VLF的剩餘空間。所以導致redo log日誌檔案越來越來大且無法收縮。
2、如果一個事物長時間執行，不管涉及多少行版本，這個事務的行版本資訊都會記錄在tempdb中，所以隨著這個事務的時間的增加，tempdb臨時檔案和臨時檔案日誌也會越來越大

7、主鍵和聚簇索引關係
7.1、主鍵預設是clustered索引，一張表無法建立兩個clustered索引
create table t123 (hid int not null,hid2 int)
ALTER TABLE t123 ADD PRIMARY KEY CLUSTERED (hid) --hid欄位必須是not null才能建立主鍵
CREATE CLUSTERED INDEX [CI_hid2] ON [t123] (hid2) --無法建立有如下報錯，說明建立主鍵沒有加nonclustered時主鍵預設是clustered索引
報錯：Cannot create more than one clustered index on table 't123'. Drop the existing clustered index 'PK__t123__DF101B01374937EF' before creating another.
7.2、建立主鍵時加上NONCLUSTERED，主鍵就是NONCLUSTERED索引
create table t123 (hid int not null,hid2 int)
ALTER TABLE t123 ADD PRIMARY KEY NONCLUSTERED (hid)
CREATE CLUSTERED INDEX [CI_hid2] ON [t123] (hid2)

8、聚集索引和非聚集索引的體系結構
8.1、在聚集索引中，葉節點包含基礎表的資料頁。 根節點和中間級節點包含存有索引行的索引頁。 每個索引行包含一個鍵值和一個指標，該指標指向 B+ 樹上的某一中間級頁或葉級索引中的某個資料行。 每級索引中的頁均被連結在雙向連結列表中。
![](http://img.blog.itpub.net/blog/2022/10/24/a4a579753f2dfd20.gif?x-oss-process=style/bb)
8.2、非聚集索引的葉級別是由索引頁而不是由資料頁組成。 非聚集索引的葉級別的索引頁包含鍵列以及包含列。非聚集索引行中的行定位器或是指向行的指標，或是行的聚集索引鍵，如下所述：
如果表是堆（意味著該表沒有聚集索引），則行定位器是指向行的指標。 該指標由檔案識別符號 (ID)、頁碼和頁上的行數生成。 整個指標稱為行 ID (RID)。
如果表有聚集索引或索引檢視上有聚集索引，則行定位器是行的聚集索引鍵。
![](http://img.blog.itpub.net/blog/2022/10/24/2da1c84acc9ec07d.gif?x-oss-process=style/bb)

9、SQL Server鎖升級的粒度，參考ALTER TABLE語句中LOCK_ESCALTATION屬性
在SQL Server記憶體結構中，有一段是鎖管理器記憶體。一個簡單例子理解鎖升級：假如一張表有1000萬資料，每行資料1000bytes，Sqlserver資料頁預設8KB大小，也就是一個資料頁小於等於8KB/1000B=8行記錄，如果對這張表執行一個select語句，資料庫+架構+表+頁+行上面都是共享鎖。如果對這張表執行一個delete 40000行的語句，資料庫+架構都是共享鎖，表+頁都是意向排他鎖，行是排他鎖，其中資料庫(1個)+架構(1個)共享鎖總計2個，表(1個)和頁(40000行/8行=5000個)意向排他鎖總計5001個，行排他鎖40000個，總計1+1+5001+40000=45003個鎖，每個鎖消耗64+32=96Bytes的記憶體，這個delete 40000行的操作就要消耗鎖記憶體45003*96/1024/1024=4.12MB的記憶體。當很多delete併發出現時，鎖記憶體會大大增加，所以Sqlserver會有鎖升級，一旦把行鎖上升到表鎖，就可以大大減少鎖記憶體的佔用，比如前面delete 30000行的例子，行鎖上升到表鎖後，總計3個鎖=資料庫(1個)共享鎖+架構(1個)共享鎖+表(1個)排他鎖，鎖記憶體變成288B，大大小於之前的4.12MB。

SQL Server鎖升級的粒度不是從行級到頁級再到表級，而是直接從行級到分割槽級或表級，禁用鎖升級並不是最好的選項，因為SQL Server的鎖管理器會消耗大量的記憶體
ALTER TABLE TABLENAME SET (LOCK_ESCALATION={AUTO|TABLE|DISABLE})
AUTO：藉助此選項，SQL Server 資料庫引擎 可選擇適合於表架構的鎖升級粒度。
    如果該表已分割槽，鎖升級到分割槽級別。 鎖升級到分割槽級別之後，該鎖以後將不會升級到TABLE粒度。
    如果表未分割槽，鎖升級到TABLE粒度。
TABLE：無論表是否已分割槽，鎖都升級到表級粒度。 預設值為TABLE。
DISABLE：在大多數情況下禁止鎖升級。 表級鎖並未完全被禁止。 例如，如果掃描的表在可序列化隔離級別下沒有聚集索引，資料庫引擎必須使用表鎖來保證資料完整性。

鎖（由鎖管理器維護）：每個所有者 64 位元組 + 32 位元組

鎖升級可能在以下條件之一下發生：
    達到記憶體閾值 - 達到鎖記憶體的40%的記憶體閾值。 當鎖記憶體超過緩衝池的24%時，可以觸發鎖升級。 鎖記憶體限制為可見緩衝池的60%。 鎖升級閾值設定為鎖記憶體的40%。 這是緩衝池60%中的40%，即24%。 如果鎖記憶體超過60%的限制(如果禁用鎖升級) ，則所有分配其他鎖的嘗試都會失敗，並且1204會生成錯誤。
    達到鎖閾值 - 檢查記憶體閾值後，評估在當前表或索引上獲取的鎖數。 如果該數字超過5000，則會觸發鎖升級。

Sqlserver儲存引擎體系結構簡介_Part1

相關文章