標(biāo)簽：SQL SERVER/MSSQL SERVER/數(shù)據(jù)庫/DBA/內(nèi)存池/緩沖區(qū)

了解執(zhí)行計(jì)劃對(duì)數(shù)據(jù)庫性能分析很重要，其中涉及到了語句性能分析與存儲(chǔ)，這也是寫這篇文章的目的，在了解執(zhí)行計(jì)劃之前先要了解一些基礎(chǔ)知識(shí)，所以文章前面會(huì)講一些概念，學(xué)起來會(huì)比較枯燥，但是這些基礎(chǔ)知識(shí)非常重要。

目錄

• 概述
• 基礎(chǔ)概念
  • 怎樣緩存執(zhí)行計(jì)劃
  • SQL Server自動(dòng)刪除執(zhí)行計(jì)劃
  • 重新編譯執(zhí)行計(jì)劃
• 測(cè)試
  • 執(zhí)行計(jì)劃相關(guān)系統(tǒng)視圖
  • 手動(dòng)清空緩存執(zhí)行計(jì)劃
  • 測(cè)試索引更改對(duì)執(zhí)行計(jì)劃的影響
  • 測(cè)試增加字段對(duì)執(zhí)行計(jì)劃的影響
• 總結(jié)

SQL Server 有一個(gè)用于存儲(chǔ)執(zhí)行計(jì)劃和數(shù)據(jù)緩沖區(qū)的內(nèi)存池。池內(nèi)分配給執(zhí)行計(jì)劃或數(shù)據(jù)緩沖區(qū)的百分比隨系統(tǒng)狀態(tài)動(dòng)態(tài)波動(dòng)。內(nèi)存池中用于存儲(chǔ)執(zhí)行計(jì)劃的部分稱為過程緩存。

SQL Server 執(zhí)行計(jì)劃包含下列主要組件：

• 查詢計(jì)劃

執(zhí)行計(jì)劃的主體是一個(gè)重入的只讀數(shù)據(jù)結(jié)構(gòu)，可由任意數(shù)量的用戶使用。這稱為查詢計(jì)劃。查詢計(jì)劃中不存儲(chǔ)用戶上下文。內(nèi)存中查詢計(jì)劃副本永遠(yuǎn)不超過兩個(gè)：一個(gè)副本用于所有的串行執(zhí)行，另一個(gè)用于所有的并行執(zhí)行。并行副本覆蓋所有的并行執(zhí)行，與并行執(zhí)行的并行度無關(guān)。

• 執(zhí)行上下文

每個(gè)正在執(zhí)行查詢的用戶都有一個(gè)包含其執(zhí)行專用數(shù)據(jù)（如參數(shù)值）的數(shù)據(jù)結(jié)構(gòu)。此數(shù)據(jù)結(jié)構(gòu)稱為執(zhí)行上下文。執(zhí)行上下文數(shù)據(jù)結(jié)構(gòu)可以重新使用。如果用戶執(zhí)行查詢而其中的一個(gè)結(jié)構(gòu)未使用，將會(huì)用新用戶的上下文重新初始化該結(jié)構(gòu)。

SQL Server 有一個(gè)高效的算法，可查找用于任何特定 SQL 語句的現(xiàn)有執(zhí)行計(jì)劃。在 SQL Server 中執(zhí)行任何 SQL 語句時(shí)，關(guān)系引擎將首先查看過程緩存中是否有用于同一 SQL 語句的現(xiàn)有執(zhí)行計(jì)劃。SQL Server 將重新使用找到的任何現(xiàn)有計(jì)劃，從而節(jié)省重新編譯 SQL 語句的開銷。如果沒有現(xiàn)有執(zhí)行計(jì)劃，SQL Server 將為查詢生成新的執(zhí)行計(jì)劃。

什么情況下會(huì)刪除執(zhí)行計(jì)劃

在沒有人工手動(dòng)清除緩存的情況下，如果出現(xiàn)內(nèi)存不足的情況下SQL Server會(huì)自動(dòng)清除一部分沒被利用到的緩存計(jì)劃。

所有緩存的最大大小取決于max server memory的大小。

怎樣判斷需要?jiǎng)h除的執(zhí)行計(jì)劃

果存在內(nèi)存不足的情況，數(shù)據(jù)庫引擎將使用基于開銷的方法來確定從過程緩存中刪除哪些執(zhí)行計(jì)劃。怎樣確定一個(gè)執(zhí)行計(jì)劃的開銷呢，對(duì)于一個(gè)第一次執(zhí)行的執(zhí)行計(jì)劃SQL Server將它的開銷值設(shè)為0，被多次執(zhí)行過的執(zhí)行計(jì)劃SQL Server將它的開銷值設(shè)置為原始編譯開銷，所以數(shù)據(jù)庫引擎會(huì)重復(fù)檢查每個(gè)執(zhí)行計(jì)劃的狀態(tài)并將刪除當(dāng)前開銷為零的執(zhí)行計(jì)劃。如果存在內(nèi)存不足的情況，當(dāng)前開銷為零的執(zhí)行計(jì)劃不會(huì)自動(dòng)被刪除，而只有在數(shù)據(jù)庫引擎檢查該執(zhí)行計(jì)劃并發(fā)現(xiàn)其當(dāng)前開銷為零時(shí)，才會(huì)刪除該計(jì)劃。當(dāng)檢查執(zhí)行計(jì)劃時(shí)，如果當(dāng)前沒有查詢使用該計(jì)劃，則數(shù)據(jù)庫引擎將降低當(dāng)前開銷以將其推向零。

數(shù)據(jù)庫引擎會(huì)重復(fù)檢查執(zhí)行計(jì)劃，直至刪除了足夠多的執(zhí)行計(jì)劃，以滿足內(nèi)存需求為止。如果存在內(nèi)存不足的情況，執(zhí)行計(jì)劃可多次對(duì)其開銷進(jìn)行增加或降低。如果內(nèi)存不足的情況已經(jīng)消失，數(shù)據(jù)庫引擎將不再降低未使用執(zhí)行計(jì)劃的當(dāng)前開銷，并且所有執(zhí)行計(jì)劃都將保留在過程緩存中，即使其開銷為零也是如此。

根據(jù)數(shù)據(jù)庫新狀態(tài)的不同，數(shù)據(jù)庫中的某些更改可能導(dǎo)致執(zhí)行計(jì)劃效率降低或無效。SQL Server 將檢測(cè)到使執(zhí)行計(jì)劃無效的更改，并將計(jì)劃標(biāo)記為無效。此后，必須為執(zhí)行查詢的下一個(gè)連接重新編譯新的計(jì)劃。導(dǎo)致計(jì)劃無效的情況包括：

• 對(duì)查詢所引用的表或視圖進(jìn)行更改（ALTER TABLE 和 ALTER VIEW）。
• 對(duì)執(zhí)行計(jì)劃所使用的任何索引進(jìn)行更改。
• 對(duì)執(zhí)行計(jì)劃所使用的統(tǒng)計(jì)信息進(jìn)行更新，這些更新可能是從語句（如 UPDATE STATISTICS）中顯式生成，也可能是自動(dòng)生成的。
• 刪除執(zhí)行計(jì)劃所使用的索引。
• 顯式調(diào)用 sp_recompile。
• 對(duì)鍵的大量更改（其他用戶對(duì)由查詢引用的表使用 INSERT 或 DELETE 語句所產(chǎn)生的修改）。
• 對(duì)于帶觸發(fā)器的表，插入的或刪除的表內(nèi)的行數(shù)顯著增長(zhǎng)。
• 使用 WITH RECOMPILE 選項(xiàng)執(zhí)行存儲(chǔ)過程。

--1.緩存的每一個(gè)對(duì)象返回一行，包括緩存計(jì)劃的類型、緩存引用的對(duì)象、緩存計(jì)劃占用的空間、被使用次數(shù)、以及創(chuàng)建時(shí)間等SELECT * FROM sys.syscacheobjects;--2.緩存的每個(gè)查詢計(jì)劃返回一行，包括執(zhí)行計(jì)劃被使用的次數(shù)、執(zhí)行計(jì)劃的大小、內(nèi)存地址、執(zhí)行計(jì)劃的類型、語句等SELECT * FROM sys.dm_exec_cached_plans;GO---3.返回由指定的 sql_handle 標(biāo)識(shí)的 SQL 批處理的文本/*其中sql_handle來自:sys.dm_exec_query_statssys.dm_exec_requestssys.dm_exec_cursorssys.dm_exec_xml_handlessys.dm_exec_query_memory_grantssys.dm_exec_connectionsplan_handle來自:sys.dm_exec_cached_plans*/ SELECT * FROM sys.dm_exec_sql_text(sql_handle | plan_handle);GO--4.以 XML 格式返回計(jì)劃句柄指定的批查詢的顯示計(jì)劃，主要接受來自sys.dm_exec_cached_plans的plan_handle句柄SELECT * FROM sys.dm_exec_query_plan(plan_handle);GO--5.每個(gè)計(jì)劃屬性返回一行，主要接受來自sys.dm_exec_cached_plans的plan_handle句柄SELECT * FROM sys.dm_exec_plan_attributes(plan_handle);GO--6.針對(duì)每個(gè) Transact-SQL 執(zhí)行計(jì)劃、公共語言運(yùn)行時(shí) (CLR) 執(zhí)行計(jì)劃和與計(jì)劃關(guān)聯(lián)的游標(biāo)返回一行,，主要接受來自sys.dm_exec_cached_plans的plan_handle句柄SELECT * FROM sys.dm_exec_cached_plan_dependent_objects(plan_handle);--7.返回緩存查詢計(jì)劃的聚合性能統(tǒng)計(jì)信息。緩存計(jì)劃中的每個(gè)查詢語句在該視圖中對(duì)應(yīng)一行，并且行的生存期與計(jì)劃本身相關(guān)聯(lián)。在從緩存刪除計(jì)劃時(shí)，也將從該視圖中刪除對(duì)應(yīng)行。*/--該系統(tǒng)視圖針對(duì)每一個(gè)緩存中的執(zhí)行計(jì)劃統(tǒng)計(jì)其執(zhí)行時(shí)間、物理、邏輯操作等信息SELECT * FROM sys.dm_exec_query_stats 

--清空緩存中的執(zhí)行計(jì)劃DBCC FREEPROCCACHE; -- ( plan_handle | sql_handle | pool_name )GO-- 清空制定數(shù)據(jù)庫的執(zhí)行計(jì)劃DBCC FLUSHPROCINDB(<dbid>);GO---清空緩存中的數(shù)據(jù)DBCC DROPCLEANBUFFERS;---清空特定緩存存儲(chǔ)區(qū)中的執(zhí)行計(jì)劃DBCC FREESYSTEMCACHE(<cachestore>) -- 'ALL', pool_name, 'Object Plans', 'SQL Plans', 'Bound Trees'GO

---清空制定數(shù)據(jù)庫執(zhí)行計(jì)劃DECLARE @DBID INTSET @DBID=DB_ID()DBCC FLUSHPROCINDB(@DBID);GO---創(chuàng)建測(cè)試數(shù)據(jù)庫CREATE TABLE TPlan(ID INT PRIMARY KEY IDENTITY(1,1),Name NVARCHAR(20) NOT NULL,Istate INT NOT NULL,Idate DATETIME DEFAULT(GETDATE()))GO---創(chuàng)建索引CREATE INDEX IX_TPlan_NAME ON TPlan(Name)GOINSERT INTO TPlan(Name,Istate)VALUES('1',1),('2',2),('3',3)GOSELECT NAME FROM TPlanGOSELECT Cacheobjtype,objtype,dbid,objid,usecounts,pagesused,sql FROM sys.syscacheobjectsWHERE DBID=DB_ID()

使用Profiler監(jiān)控

使用SQL:StmtRecompile監(jiān)控，如果是監(jiān)控存儲(chǔ)過程則使用：SP:Recompile

修改索引

在索引中添加字段

DROP INDEX [IX_TPlan_NAME] ON [dbo].[TPlan] WITH ( ONLINE = OFF )GOUSE [Study]GOCREATE NONCLUSTERED INDEX [IX_TPlan_NAME] ON [dbo].[TPlan] (    [Name] ASC)INCLUDE ( [Istate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]GO

再執(zhí)行查詢

SELECT NAME FROM TPlan

增加查詢非相關(guān)字段

ALTER TABLE [dbo].[TPlan] ADD Number INT

刪除查詢有關(guān)的索引也同樣會(huì)導(dǎo)致執(zhí)行計(jì)劃重編譯，這里就不截圖貼出來了。

查看執(zhí)行計(jì)劃

SELECT Cacheobjtype,objtype,dbid,objid,usecounts,pagesused,sql FROM sys.syscacheobjectsWHERE DBID=DB_ID()

執(zhí)行計(jì)劃中顯示了該執(zhí)行計(jì)劃被調(diào)用了兩次，在隨機(jī)叢書中寫的是會(huì)重新編譯新的執(zhí)行計(jì)劃，如果是這樣的話那這里的值應(yīng)該是1才對(duì)。

猜測(cè)：SQL Server在架構(gòu)更改的時(shí)候通過檢測(cè)執(zhí)行計(jì)劃已經(jīng)對(duì)原先的執(zhí)行計(jì)劃進(jìn)行了編譯，所以在新的查詢中還是使用了第一次查詢的執(zhí)行計(jì)劃。

如果有誰知道結(jié)果麻煩告知。

如果文章對(duì)大家有幫助，幫忙推薦，謝謝?。。?/strong>