- 頁是 SQL Server 中數據存儲的基本單位，大小為 8KB。

- 區是空間管理的基本單位，8個物理上連續的頁的集合（64KB）。

- 頁的類型包括：

1. Data

2. Index

3. Text/Image

4. Global Allocation Map

5. Shared Global Allocation Map

6. Page Free Space, Index Allocation Map

7. Bulk Changed Map

8. Differential Changed Map

- 在一個數據庫里，絕大多數的頁面都是 Data 或者 Text/Image 類型。

- varchar(max), nvarchar(max) 可以突破 8KB 大小的限制。

1. 如果字段行的總長不超過8KB，數據還是會一起存放在普通的數據頁里。

2. 如果超過8KB，SQL Server就會把這些字段的數據分開，單獨存放在一種叫 Row-Overflow（行溢出）的頁面里。

-區分為：統一區和混合區。統一區由單個對象所有，混合區最多可由8個對象共享。

-通常從混合區向新表或索引分配頁。當表或索引增長到8頁時，將變成使用統一區進行后續分配。

-SQL Server 數據庫引擎內部會將日志文件分成多個虛擬日志單元。日志文件每自動增長一次，會至少增加一個虛擬日志單元。

-tempdb 數據庫是一個全局資源，主要保存這三類對象：

1. 用戶對象：用戶自定義表和索引，系統表和索引，全局臨時表和索引，局部臨時表和索引，表變量，表值函數中返回的表。

2. 內部對象：SQL Server 數據庫引擎創建的，用戶處理SQL語句。包括用于游標操作的工作表，用戶哈希聯接或哈希聚合操作的工作表，GROUP BY, ORDER BY或UNION 查詢的中間排序結果。

3. 版本存儲區：版本存儲區是數據頁的集合，它包含支持使用行版本控制的功能所需的數據行。

-SQL Server 三種組織分區中的數據和索引頁的方法：

1. 用B樹存儲有聚集索引的表數據頁（表數據和聚集索引的排序順序一致）

2. 堆是沒有聚集索引的表（SQL Server對堆的管理比較簡單，算法能力弱，所有大的，經常使用的表都應該建立聚集索引）

3. 非聚集索引（表數據和非聚集索引的排序順序不一致）

-DELETE vs TRUNCATE

1. 數據表上建立聚集索引（否則DELETE語句后，表仍會包含空頁）。

2. 如果刪除整張表數據，使用TRUNCATE TABLE（日志少，鎖少，釋放空間）。

3. 如果刪除表本身，使用DROP TABLE。

-DBCC SHRINKFILE 做的都是區級別的動作，它會把使用過的區前移，把沒有使用的區從文件中移除。

1. 如果一個數據庫中有很多只使用了一兩個頁面的區，DBCC SHRINKFILE的效果會不明顯。

2. 解決辦法：重建聚集索引。

3. 如果是Text/Image數據類型，使用DBCC EXTENTINFO分析數據文件里所有區的分配情況，考慮重建對象。

-SQL Server 會為所有的修改記錄日志，以便將來重新提交或者回滾。（機器斷電，SQL Server 服務崩潰）

-DBCC LOG，日志文件記錄的是數據的變化，而不是記錄用戶的操作。日志文件服務的是 SQL Server，而不是用戶。

- DBCC OPENTRAN，返回當前數據庫最久未被提交的事務。

- KILL 命令并不是百試不爽，如果一個連接正處于提交或者回滾的過程中，SQL Server 會尊重它的執行而不去強制終止它。

- 數據庫自動收縮功能，會在空閑空間大于25%的情況下自動運行 DBCC SHRINKFILE 的動作。

- 對于一個比較繁忙的數據庫，推薦的設置是開啟數據庫自動增長選項，以防數據庫空間用盡，但要嚴格避免自動增長的發生。同時，盡量不要使用自動收縮功能。

- SQL Server 服務啟動順序

1. 從注冊表讀取SQL Server啟動信息。

2. 檢測硬件，配置內存與CPU。

3. 初始化MS DTC。

4. 系統數據庫啟動：master -> mssqlsystemresource -> model -> tempdb。

5. 準備網絡連接。

6. 啟動msdb數據庫和其他用戶數據庫。

- DBCC CHECKDB 命令：

　　1. 檢查數據庫里有沒有損壞發生。

　　2. 盡力修復數據庫損壞。

- DBCC CHECKDB 做些什么：

1. 首先檢查一些關鍵的系統表。（任意一張系統表上發生了損壞，DBCC CHECKDB都會直接失敗，只有恢復數據庫的備份）

2. 對數據庫運行DBCC CHECKALLOC。（檢查數據庫中所有頁的分配）

3. 對數據庫中的每個表和視圖運行DBCC CHECKTABLE。

4. 對數據庫運行DBCC CHECKCATALOG。

5. 驗證數據庫中每個索引視圖的內容。

6. 驗證數據庫中的Service Broker數據。

- DBCC CHECKDB 修復選項：

1. REPAIR_ALLOW_DATA_LOSS 嘗試修復報告的所有錯誤。

2. REPAIR_FAST 保留這是為了向后兼容，未執行任何修復操作。

3. REPAIR_REBUILD 執行次要，快速修復（例如修復非聚集索引中的額外鍵）以及耗時修復（例如重新生成索引）。執行這些修復時不會有丟失數據的危險。

-REPAIR_ALLOW_DATA_LOSS執行：

1. DBCC CHECKDB 將由于I/O或校驗錯誤而標記為不可訪問的頁重新標記為可訪問，如同這些錯誤沒有出現過一樣。

2. DBCC CHECKDB 將嘗試使用常規的基于日志的恢復技術恢復數據庫。

3. 如果由于事務日志損壞而導致數據庫恢復失敗，則將重新生成事務日志。

-REPAIR_ALLOW_DATA_LOSS無法修復的時候，應該怎么做？

1. 按照預先的備份恢復策略，恢復數據庫備份。

2. 如果損壞發生在某些用戶對象上（用戶表，視圖，存儲過程等），可以把它們DROP掉試試。

3. 將數據庫設成緊急只讀模式，用 SELECT...INTO 或其他方式，將數據導入到一個新建的空數據庫里。

- ALTER DATABASE <DBNAME> SET EMERGENCY 設置數據庫緊急模式。

- 緩沖區（Buffer Pool）管理是實現高效 I/O 操作的關鍵。一個緩沖區就是一個 8KB 的內存頁，緩沖區高速緩存被劃分為多個 8KB 頁。

- 緩沖區管理器負責將數據頁或索引頁從數據庫盤文件讀入緩沖區高速緩存中，并將修復后的頁寫回磁盤。

- 臟頁寫入磁盤有三種方式：

1. 惰性寫入（Lazy writing）：寫入不經常使用的頁，保證存在可用的緩沖區。

2. 勤奮寫入（Eager writing）：立即寫入。

3. 檢查點（Check Point）：定時寫入，保證在創建的檢查點全部 臟頁 都已寫入磁盤。一般SQL Server會以一分鐘左右的頻率創建檢查點。

- SQLIOSim 測試安裝 SQL Server 服務器的 I/O 子系統是否沒有問題。

- 通常建議把數據庫文件和日志放在不同的物理磁盤上。如果可能的話，日志文件要放在寫入速度比較快的磁盤上。

- 一個數據文件組可以有多個文件，并且放在不同的物理磁盤上。這樣一來，I/O 工作會分布到不同的硬盤上。

- SQL Server 任務調度：

1. 只有需要運行任務的連接才會被分配線程?？臻e狀態的連接，不會占用線程資源。

2.對于每一個CPU，SQL Server 內部會有一個Scheduler，由這個 Scheduler 決定在某個時間點，到底是哪個 SQL Server 線程去運行。

3. 每個Worker跟一個線程（或纖程Fiber）相對應，是SQL Server 任務的執行單位。SQL Server 不直接調度線程/纖程，而是調度Worker，使得SQL Server 能夠控制任務調度。

4. 在Worker上運行的最小任務單元。最簡單的Task就是一個簡單Batch。

5. SQL OS調度算法的核心就是所有在邏輯Scheduler 上運行的Worker都是非搶占式的。Worker會始終在Scheduler上運行，直到它運行結束，或者主動將Scheduler讓出給其他Worker為止。這個“讓出”的動作，就是Yielding。

　注：

　　1.對于每個CPU，SQL Server 都會有一個 Scheduler 與之對應。在每個 Scheduler 里，會有若干個 Worker，對應于每個線程。

　　2.在客戶端發過來請求之后，SQL Server 會將其分解成一個或多個 Task。根據每個 Scheduler 的繁忙程度，Task 會被分配到某個 Scheduler 上面。

　　3.如果 Scheduler 里有空閑的 Worker，Task 就會被分配到某個 Worker 上。如果沒有，Scheduler 會創建新的 Worker，供 Task 使用。

　　4.如果 Scheduler 里的 Work 已經到了它的上限值，而它們都有 Task 要運行，那么新的 Task 只好進入等待 Worker 的狀態。

- 鎖：

1. 共享鎖：資源上存在共享鎖（S鎖）時，任何其他事務都不能修改數據。

2. 更新鎖：一次只有一個事務可以獲得資源的更新鎖（U鎖）。事務真正修改數據時，將更新鎖（U鎖）轉換為排他鎖（X鎖）。

3. 排他鎖：排他鎖（X鎖）可以防止并發事務對資源進行訪問。使用排他鎖（X鎖）時，任何其他事務都無法讀取或者修改數據；僅在使用NOLOCK提示或未提交讀隔離級別時才會進行讀取操作。

4. 意向鎖：數據庫引擎使用意向鎖來保護鎖層次結構的底層資源，以防止其他事務對自己鎖住的資源造成傷害，提高鎖沖突檢測性能。

5.1 架構鎖：數據庫引擎在表數據定義語言（DDL）操作（例如添加列或刪除表）的過程中使用架構修改（Sch-M）鎖，阻止其他用戶對這個表格的訪問。

5.2架構鎖：數據庫引擎在編譯和執行查詢時使用架構穩定性（Sch-S）鎖。Sch-S 鎖不會阻止其他事務訪問表格里的數據。但是，會阻止對表格做修改性的DDL操作和DML操作。

6. 大容量更新鎖：數據庫引擎在將大容量復制到表中時使用大容量更新（BU）鎖。它允許多個線程將數據并發地大容量加載到同一表，同時防止其他不進行大容量加載數據到進程訪問該表。

　注：

　　1.數據修改語句（如INSERT，UPDATE和DELETE）合并了修改和讀取操作。

　　2.語句在執行所需的修改操作之前首先執行讀取操作以獲取數據。因此，數據修改語句通常請求共享鎖和排他鎖。

　　3.例如，UPDATE語句可能根據與一個表的聯接修改另一個表中的行。在此情況下，除了請求更新行上的排他鎖之外，UPDATE語句還將請求在聯接表中讀取的行上的共享鎖。

- 如果應用申請的鎖粒度都比較小，產生阻塞的幾率就會比較小。如果一個連接會經常申請頁面級，表級，甚至是數據庫一級的鎖資源，程序產生阻塞的可能性就會很大。

- 如果一個連接總是能夠非常快地把申請到的鎖釋放掉，那阻塞就不容易發生。如果它總是長時間地持有某些鎖資源，那么就很容易發生阻塞了。

1. 一個事務內部要訪問或者修改的數據量越大，它所要申請的鎖的數目就會越多，粒度也就可能越大。

2. 一個事務做的事情越復雜，它要申請的鎖的范圍也就會越大。

3. 一個事務延續的時間越長，它持有的鎖的時間也會越長。

- 事務的隔離級別能影響鎖的申請以及釋放的時間；而語句的執行計劃，也會影響到鎖的粒度以及申請的數量。

- 并發控制：當許多人試圖同時修改數據庫中的數據時，必須實現一個控制系統，使一個人所做的修改不會對他人所做的修改產生負面影響。

- 隔離級別：

1. 未提交讀（READ UNCOMMITTED）：語句可以讀取已由其他事務修改但尚未提交的行。

2. 已提交讀（READ COMMITTED）：語句不能讀取已由其他事務修改但尚未提交的行。（避免臟讀）

3. 可重復讀（REPEATABLE READ）：語句不能讀取已由其他事務修改但尚未提交的行，并且其他任何事務都不能在當前事務完成之前讀取的數據。（避免不可重復讀）

4. 可序列化（SERIALIZABLE）：在可重復讀的基礎上，當前事務完成之前，其他事務不能使用當前事務中任何語句讀取的鍵值范圍之內插入新行。（避免幻讀）

- 要確保有足夠的索引，防止語句做全表掃描（因數據量多而導致鎖增多），但是也要去掉那些對語句運行貢獻不大的索引（因關聯的索引被修改而導致鎖增多）。

- PAGEIOLATCH_SH 和 PAGEIOLATCH_EX

1.PAGEIOLATCH_SH常常是發生在用戶想要去訪問一個數據頁面，而SQL Server 需要把這個頁面從磁盤讀往內存。

2. PAGEIOLATCH_EX 常常是發生在用戶對數據頁面做了修改，SQL Server 要向磁盤回寫。出現這個等待狀態，意味磁盤的寫入速度跟不上。

3. WRITELOG 說明任務當前正在等待將日志寫入日志文件。出現這個等待狀態，意味著磁盤的寫入速度跟不上。

- 用戶請求的生命周期：

1. 客戶端向SQL Server 發出請求指令，指令經過網絡層，SQL Server 接收到。

2. SQL Server 對收到的指令進行語法，語義檢查，編譯，生成新執行計劃，或者找到緩存的執行計劃重用。

3. 運行指令

- SQL Server 會首先為指令的運行申請內存。

- 如果發現要訪問的數據不在內存里，要將數據從磁盤讀到內存中。如果發現內存里沒有足夠的空閑頁面存放所有數據，還要做一些內存整理和paging動作，騰出足夠的空間放數據。（PAGEIOLATCH_x）

　　 - 按照執行計劃，掃描或者 seek 內存中的數據頁面，將指令需要處理的記錄找出來。（LCK_x）

- 指令可能還需要做一些聯接或者計算的工作。（sum / max / sort）

- 根據指令內容，執行計劃和數據量，SQL Server 可能還要在tempdb里創建一些對象，存放臨時表，表變量，幫助做 join / sort 等。

- 如果指令要修改數據記錄，SQL Server 會修改內存緩沖區里頁面的內容。

- 如果指令發生數據修改，在提交事務之前，SQL Server 必須將相應的日志記錄按照順序寫入日志文件。（WRITELOG）

- 將結果集返回給客戶端。（ASYNC_NETWORK_IO）

- 篩選條件的寫法是有講究的，最好都能夠使用 SARG 的運算符，包括：=, >, <, >=, <=, IN, BETWEEN。有時還包括 LIKE（如LIKE'XXX%'）。

- 對于不使用 SARG 運算符的表達式，SQL Server 對它們很難使用比較優化的做法，很可能就不使用索引。非 SARG 運算符包括（NOT, <>, NOT EXISTS, NOT IN, NOT LIKE 和內部函數 Convert, Upper 等）

- 盡可能限定語句的復雜度

1. 動態語句

2. 表格聯接的數量

3. 視圖和存儲過程的深度

4. 不必要的排序和計算

5. 超大結果集申請和返回

6. 用多個簡單語句替代一個復雜語句