SQL Server最佳化必備之任務排程

在前面兩篇文章中(  and  )，我們交流了一些關於SQL Server的一些術語，SQL Sever引擎

與SSMS抽象模型，SQL Server內部儲存機制和SQL Server內部體系結構等。討論的這些問題，均可以歸為一個問題，即"SQL Server是怎麼執行客戶端輸入的SQL

語句的？”，其中，重點討論了下圖(SQL Server 體系結構)

       然而，如果我們僅僅瞭解如上技術，是不具備一個資深DBA或資料庫最佳化高手條件的，原因很簡單，我們僅僅知道了SQL Server是怎麼做的(SQL Server是如

何執行SQL語句的)，但是我們並不知到是什麼做的(是什麼真正執行SQL語句？)，只知其然，而不知其所以然。本篇文章，我們將重點論述是什麼執行了SQL Server

體系結構邏輯？其實，執行SQL Server體系結構邏輯的核心元件便是SQLOS(簡稱SQL Server作業系統)。

 一  SQL Server引擎元件

       下圖為SQL Server引擎核心主件，它大致包括五大部分：協議層(Protocol Layer)，查詢引擎(Query Processor)，儲存引擎(Storage Engine)，公用元件(Utilities)和

SQL Server作業系統(SQLOS)。其中，除了Utilities和SQLOS兩大部分外，其他部分在前面兩篇文章中均已簡要論述。本篇文章，我們將重點論述SQLOS。

         （一）SQLOS

         SQLOS是SQL Server和Windows OS(Windows  作業系統)瘦使用者模式層，它主要負責一些底層的操作，如排程、處理I/O、記憶體管理、檔案管理等。

        當客戶端(如在SSMS中輸入)輸入一條SQL語句(SELECT * FROM TableName)，並基於TDS服務點，向SQL Server服務引擎提交該語句，SQL Sever引擎接收到

該語句後(準確來說，是先透過Protocol Layer協議層)，查詢引擎的命令分析器（Query Processor）先檢查語法結構，若語法結構有錯誤，則直接將錯誤返回給客戶端，

編譯不透過；若語法正確，則命令分析器會去緩衝池的計劃任務中尋找是否存在該語句的快取，若存在，則命令分析器把該計劃快取交給查詢執行器處理，若快取池的計

劃快取中沒有該計劃任務，則命令解析器將會生成查詢樹，並將該SQL語句傳遞給查詢最佳化器，查詢最佳化器根據“最少開銷演算法”生成最優方案，然後傳遞給查詢執行器；

流程到查詢執行器後，查詢執行器透過儲存引擎的訪問方法傳遞給儲存引擎，儲存引擎根據規則來配置和使用緩衝區管理器和事務管理器，接下來的工作，就交給

SQLOS來處理。

        也許讀者會問？為什麼不直接用Windos OS來排程，而要另做一個SQLOS來執行排程呢？因為Windows OS是基於“搶佔式排程演算法的”，這種演算法依據時間片來執行的，

這種演算法並沒有針對SQL Server進行最佳化，為了解決這個問題，微軟決定SQL Server應自己處理自己的排程，並透過建立使用者模式排程器(UMS)來解決該問題，UMS基於

“合作排程模式”演算法被設計，這種演算法指當執行中的執行緒需要等待其他資源才能執行時，不會因為時間片的輪詢被中斷，而是採用自動退出處理器的方式。

     （二）執行緒三態

       當在一個會話中執行請求時，SQL Server將這項工作(這個請求)分成一個或多個任務，每個任務由一個執行緒負責，任務被放在任務佇列中，等待排程器去排程工作執行緒來執行，

原則上，排程器會為每個任務分配一個工作執行緒，這個工作執行緒就負責執行該任務。每個執行緒都有三個狀態，即執行狀態(Running)、掛起狀態(Suspend)和可執行狀態(Runable)。

• 執行狀態(Running)：表示該執行緒正在執行任務       

• 掛起狀態(Suspend)：表示該執行緒因缺資源，被CPU強制掛起，如嘗試修改被鎖的表

• 可執行狀態(Runnable)：表示執行緒具備了可執行任務的條件，如執行緒1執行完任務1後，被放線上程佇列中，等待排程器分配任務

     執行緒三個狀態之間，存在如下轉換關係：

      1.具備執行條件的執行緒(Runnable)，只要獲得CPU，就可執行任務，從而從Runnable狀態變成Running狀態

      2.正在執行的執行緒(Running)因缺必要資源，被cpu掛起，從而從Running狀態變成Suspend狀態

      3.被掛起的執行緒(Suspend)因獲得可執行的資源，被cpu喚醒執行，從而從Suspend狀態變成Running狀態

      4.Runnable狀態的執行緒，排程器可不讓其執行(這裡，暫且稱之為Suspeng狀態)

      （三）Windows OS與SQL Server

       當一臺電腦剛生產出來，未裝任何系統時，我們稱之為裸機，對於裸機，我們是無法使用的，若要使用，則需要安裝作業系統，如win10，

當我們在裸機上裝上作業系統後，就可以透過作業系統來操作電腦了，如檢視cpu,管理檔案，管理記憶體等，不僅僅如此，我們還在作業系統上

安裝相應的運用軟體，如Visual Studio 2017,SQL SERVER 2014等。

二  SQL Server一個較為完整的排程流程

     當SSMS向SQL Server引擎發起請求時，將會產生一個會話(Session)，會話成功後，就可以執行SQL語句請求了。

     1.當建立會話成功後，在會話中建立一個SQL語句請求時(如執行查詢語句：SELECT * FROM TableName)，會話會將一個請求分成一個或多個任務，並將這些任務放在任務佇列中；

     2.每個任務都會被分配一個或多個工作執行緒來執行；

     3.SQLOS任務排程器透過從工作執行緒佇列中排程執行緒來執行相應的工作任務

     注意：SQLOS只是邏輯的，真的的執行還是Windows OS

     以下是一些涉及到的相關型別表

      sys.dm_exec_*：該型別表包含使用者程式碼執行和關聯連線直接或間接相關的資訊，如請求表sys.dm_exec_requests，會話表sys.dm_exec_sessions

      sys.dm_os_*：    該型別表包含操作OS相關的資訊

      sys.dm_tran_*：  該型別表包含事務相關資訊

      sys.dm_logpool_*：該型別包含日誌相關資訊

      sys.dm_io_*：      該型別表包含IO相關資訊

      sys.dm_db_*：    該型別包含資料庫相關資訊

三  參考文獻

 【01】《Microsoft SQL Server 2012 Internals》Kalen Delaney,Bob Beauchemin,Conor Cunningham,Jonathan Kehayias,Benjamin Nevarez,Paul S.Randal

 【02】《Microsoft SQL Server 2012 Administration》Tom Carpenter

 【03】《SQL Server 2012 深入解析與效能最佳化 第3版》Christian Bolton,Justin Langford,Glenn Berry,Gavin Payne,Amit Banerjee,Rob Farley著

 【04】《Microsoft SQL Server 2008 技術內幕:T-SQL查詢》ltzik Ben-Gran，Lubor Kollar，Dejan Sarka，Steve Kass著

 【05】《SQL Server 2008查詢效能最佳化》Grant Fritchey，Sajal Dam著

原文出處：https://www.cnblogs.com/wangjiming/p/10175380.html