如果SQL Server已經找到一個好的方式去執行一段代碼時,應該把它作為隨后的請求重用,因為生成執行計劃是耗費時間且資源密集的,這樣做是有有意義的。
如果沒找到被緩存的計劃,然后命令分析器(Command Parser)在T-SQL基礎上生成一個查詢樹(query tree)。查詢樹(query tree)的內部結構是通過樹上的每個結點代表查詢中需要的執行操作。這個樹然后被傳給查詢優化器(Query Optimizer)去處理。我們的簡單查詢沒有一個存在的計劃,因此一個查詢樹(query tree)會被創建,然后傳給查詢優化器(Query Optimizer)。
上圖展示了命令分析器(Command Parser)是用來檢查現存執行計劃的計劃緩存(plan cache),因為在緩存里沒找到我們查詢的任何信息,還有從命令分析器(Command Parser)輸出傳給優化器的查詢樹(query tree)。
查詢優化器(Query Optimizer)是被SQL Server團隊視為最有價值的財產,也是產品中最復雜、機密的部分之一。幸運的是,只有底層的算法和源代碼被很好保護(即使在微軟內部),優化器如何工作才能被研究和監視。
這個所謂的基于成本(cost-based)的優化器,意味要去評估執行查詢的各種方式,然后選擇被認為擁有最小成本的方式去執行。執行方式以查詢計劃(query plan)實現并從查詢優化器(Query Optimizer)輸出。
基于剛才的介紹,你認為優化器的工作是找到最好的查詢計劃會被原諒的,因為那看起來是很明顯的設想。然而它的實際工作是在一段時間內找到好的計劃,而不是最佳計劃。優化器的目標通常被描述為找最有效率的計劃。
如果優化器每次都嘗試去找最好的計劃,比起執行一個慢的計劃,找個最好的計劃花費的時間更長(一些內建的試探法實際上在保證優化器從不花更長的時間找到好計劃,而是就找一個計劃并執行它)。
優化器同樣在成本的基礎執行多級優化,在每一階段增加更多可用選擇項來找更好的計劃。當一個好計劃被找到時,優化器就停在那一階段了。
第1階段被稱之為預優化,當語句是足夠簡單而只有一個最佳計劃時,在第一階段就退出剩下的步驟,移除額外成本需要。沒有join的基本查詢被認為簡單,計劃成本產出為0,然后被提及為普通計劃(trivial plans)。
優化實際上開始的下一階段包含三個查找時期:
如果已經找到的計劃成本小于0.2,優化器會停在這里。在這個階段生成的計劃稱為事務處理(transaction PRocessing)或簡稱TP計劃。
如果已經知道的計劃成本小于1.0,優化器會停在這里。這個階段生成的計劃被稱為快速計劃(quick plans)。
第2時期的完成是找到計劃的成本對優化需要的時間之間的平衡。在這個時期生成的計劃有完全級別(level of "Full")的優化。
它的花費需要多少?
這里提及的花費不能用多少秒或其他有意義的表達來衡量;它只是標記代表計劃資源消耗值的一個任意數。然而,在早期的微軟SQL Server世界里,它的起源是在桌面電腦上的基準檢查程序(benchmark)(跑分)。
在計劃里,每個運算符都有一個底線成本,然后用它來乘以行的大小和預計行數來獲得那個運算符的成本,計劃成本就是這些所有運算符的成本。
因為成本來自于底線值且與你的硬件速度無關,在每個SQL Server裝置(同比版本 like-for-like version。博主注:與版本無關。)里生成每個計劃的成本是一樣的。
因為我們的SELECT查詢非常簡單,它退出在預優化時期的操作,因為這個計劃對優化器非常明顯(一個普通計劃)。現在已經有查詢計劃了,它向查詢執行器(Query Executor)去執行。
查詢執行器(Query Executor)查詢執行器的工作是不釋自明的,它執行查詢。更準確的說,它通過干完包含與存儲引擎相互作用的檢索或修改數據的每一步來執行查詢。
(此處有信息需要完善…………)
這個SELECT查詢需要檢索數據,因此請求傳給存儲引擎(Storage Engine)通過OLE DB接口傳給存取方法(access Methods)。
上圖展示了作為優化器的輸出的執行計劃(query plan)正傳給查詢執行器(Query Executor),同時引入了存儲引擎(Storage Engine),它被查詢執行器(Query Executor)通過OLE作為接口給存取方法(Access Methods)。
存取方法(Access Methods)存取方法是為你數據和索引提供存儲結構,還有通過數據檢索或數據修改接口的一批代碼。它包含檢索數據的所有代碼單本身不執行操作,它向緩存區管理器(Buffer Manager)傳遞請求。
假設我們的SELECT語句需要讀取一些記錄行的數據剛好在一頁。存取方法(Access Methods)的代碼會讓緩存區管理器(Buffer Manager)檢索頁,因此它可以準備一個OLE DB的記錄集傳回給關系引擎(Relational Engine)。
緩存區管理器(Buffer Manager)緩存區管理器(Buffer Manager),如名所示,管理緩沖池(buffer pool),它代表著SQL Server的主要內存使用。如果你需要從頁讀一些記錄行(當我們談論UPDATE查詢時會提及修改數據),緩存區管理器(Buffer Manager)在緩沖池(buffer pool)檢查數據緩存看看在內存里是否有被緩存的這頁。如果這頁已被緩存了,結果就會傳回給存取方法(Access Methods)。
如果這頁沒被緩存,然后緩存區管理器(Buffer Manager)從磁盤里拿這頁,把它放入數據緩存(Data Cache),然后把結果傳回給存取方法(Access Methods)。
你這里要記住的要點是你永遠只和內存中的數據打交道。在作為記錄集返回前,你請求的每個新的數據讀取,首先從磁盤讀取,然后寫回內存(數據緩存(the data cache))。
這就是為什么SQL Server需要在內存里保持最小級別的可用頁面;如果第一時間在緩存里沒有空間來放數據,你就不能讀取任何新數據。
存取方法(Access Methods)代碼決定SELECT查詢需要一個新頁,因此它向緩存區管理器(Buffer Manager)拿。緩存區管理器(Buffer Manager)檢查它是否已在數據緩存(data cache),如果沒找到的話就從磁盤加載到緩存。
數據緩存(Data Cache)數據緩存一直是緩沖池(buffer pool)最大一部分;因此也是在SQL Server最大內存用戶。這里每個從磁盤讀取的數據頁在被用之前都會被寫回。
這個sys.dm_os_buffer_descriptors動態管理視圖(DMV)每一行代表當前內存持有的每個數據頁,你可以用這個腳本看看在數據緩存區(Data Cache)每個數據庫占用多少空間:
1 SELECT count(*)*8/1024 AS 'Cached Size (MB)'2 ,CASE database_id3 WHEN 32767 THEN 'ResourceDb'4 ELSE db_name(database_id)5 END AS 'Database'6 FROM sys.dm_os_buffer_descriptors7 GROUP BY db_name(database_id),database_id8 ORDER BY 'Cached Size (MB)' DESC
輸出結果看起來會類似如下:
Cached Size (MB) Database
3287 People
34 tempdb
12 ResourceDb
4 msdb
這個例子里,People數據庫在數據緩存(Data Cache)里有3287 MB數據頁。
頁在緩存里停留時間量由最近最少使用(least recently used:LRU)策略決定。
(此處有信息待完善………………)
一個簡單SELECT語句(查詢)生命周期總結SELECT查詢的整個生命周期在這里被介紹:
新聞熱點
疑難解答