第十七章——配置SQLServer（3）——配置“對即時負載的最佳化”

標籤：

原文: 第十七章——配置SQLServer（3）——配置“對即時負載的最佳化”

前言：

        在第一次執行查詢或者預存程序時，會建立執行計畫並儲存在SQLServer的過程緩衝記憶體中。在很多時候，我們會執行一些簡單的程式，僅僅執行一次，而為這些查詢建立預存程序是非常浪費記憶體資源的。由於記憶體不足，可能會導致你的緩衝溢出，從而影響效能。在2005之前，這是一個大問題，為了糾正這個問題。微軟在SQLServer 2008中引入了對即時查詢負載的最佳化功能。這個功能在2012也依舊可用。是基於執行個體層級的。

        很多開發人員直接在生產環境運行和測試查詢，如果沒有得到期望的結果，會更改查詢然後再次執行，這會對過程緩衝造成很大壓力。所以盡量不要這樣做。

 

準備工作：

在開始之前，在測試伺服器清空緩衝，但是切記不要在生產環境這樣做：

1、 先看看有多少資料儲存在緩衝中：

SELECT  CP.usecounts AS CountOfQueryExecution ,        CP.cacheobjtype AS CacheObjectType ,        CP.objtype AS ObjectType ,        ST.text AS QueryTextFROM    sys.dm_exec_cached_plans AS CP        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS STWHERE   CP.usecounts > 0GO

結果如下：

2、 清空緩衝和緩衝池：

DBCC FREEPROCCACHE GO

3、 如果想檢查是否清空成功，可以再次執行步驟1中的語句： 

 

步驟：

1、 執行下面語句：

USE AdventureWorksGOSELECT  *FROM    Sales.SalesOrderDetailWHERE   SalesOrderDetailID = 43659GO

2、 檢查在運行了上面語句後是否有計畫快取，再次執行之前查詢計劃緩衝的語句： 

SELECT  CP.usecounts AS CountOfQueryExecution ,        CP.cacheobjtype AS CacheObjectType ,        CP.objtype AS ObjectType ,        ST.text AS QueryTextFROM    sys.dm_exec_cached_plans AS CP        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS STWHERE   CP.usecounts > 0GO

3、 下面是結果，當然，也可以在where條件中用like來減少尋找的資料量：也可以使用ctrl+alt+a來開啟活動監視器來尋找已耗用時間長的查詢。

4、 現在來把Optimize for Ad hoc Workloads設為1：

 EXEC sp_configure ‘optimize for ad hoc workloads‘, 1RECONFIGUREGO

5、 然後再次清空緩衝： 

DBCC FREEPROCCACHE GO

6、 再次執行語句：

USE AdventureWorksGOSELECT  *FROM    Sales.SalesOrderDetailWHERE   SalesOrderDetailID = 43659GO

7、 可以執行下面的語句檢查是否有新的緩衝進入： 

SELECT  CP.usecounts AS CountOfQueryExecution ,        CP.cacheobjtype AS CacheObjectType ,        CP.objtype AS ObjectType ,        ST.text AS QueryTextFROM    sys.dm_exec_cached_plans AS CP        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS STWHERE   CP.usecounts > 0        AND ST.text LIKE ‘%SELECT  *  FROM    Sales.SalesOrderDetail  WHERE   SalesOrderDetailID = 43659  %‘        AND CP.cacheobjtype = ‘Compiled Plan‘GO

8、 你會發現裡面沒有資料，現在再次執行下面語句： 

USE AdventureWorksGOSELECT  *FROM    Sales.SalesOrderDetailWHERE   SalesOrderDetailID = 43659GO

9、 使用以下查詢檢查： 

SELECT  CP.usecounts AS CountOfQueryExecution ,        CP.cacheobjtype AS CacheObjectType ,        CP.objtype AS ObjectType ,        ST.text AS QueryTextFROM    sys.dm_exec_cached_plans AS CP        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS STWHERE   CP.usecounts > 0        AND ST.text LIKE ‘%SELECT  *  FROM    Sales.SalesOrderDetail  WHERE   SalesOrderDetailID = 43659  %‘        AND CP.cacheobjtype = ‘Compiled Plan‘GO

10、這次就出現了下面的： 

 

 

分析：

        當新查詢執行時，query_hash值會在記憶體中產生，而不是整個執行計畫，當相同的查詢第二次執行的時候，SQLServer會尋找是否已經存在這個query_hash，如果不存在，執行計畫將儲存在緩衝中。這樣就使得僅執行一次的查詢將不會儲存執行計畫到緩衝中。所以強烈建議開啟這個配置。這個配置不造成任何負面影響，但是可以節省計畫快取的空間。

        一般情況下，當你執行查詢，將會產生執行計畫並儲存在過程緩衝中，所以當你執行步驟1的查詢是，會看到伺服器有很多計畫快取，但是當執行第六步後的查詢是，就發現沒有。對於即席查詢，如果只執行一次，何必需要緩衝呢？

        有些系統的計畫快取達到GB以上，開啟後可能減少一半空間。另外，如果你好奇即席查詢佔用了多少空間，可以使用下面的語句：

SELECT  SUM(size_in_bytes) AS TotalByteConsumedByAdHocFROM    sys.dm_exec_cached_plansWHERE   objtype = ‘Adhoc‘        AND usecounts = 1

第十七章——配置SQLServer（3）——配置“對即時負載的最佳化”