利用T-SQL代碼提高緩衝效率 減少記憶體消耗

在這篇文章裡，我將介紹怎樣編寫你的代碼來提高緩衝計劃的重用。瞭解當有緩衝計劃或重用一個已有的計劃時空格和注釋會產生怎樣的影響，這會協助你降低你的應用程式緩衝的計劃數目。

探究緩衝計劃

你在利用計畫快取嗎？你是否很好地利用緩衝計劃？你的應用程式曾經使用它們了嗎，它們是否被多次利用？你有沒有在同一時間在預存程序緩衝中對同一查詢具有多個緩衝計劃？這些緩衝計劃使用了多少空間？這些是你需要回答的問題，以確保你在最佳化過程緩衝以及減少你的應用程式將建立的緩衝計劃數目。你編寫你的T-SQL代碼時有些細微的地方需要注意，它會使得SQL Server為相同的T-SQL代碼去執行額外的工作來編譯和緩衝執行計畫。

在SQL Server可以處理一個T-SQL批處理之前，它需要建立一個執行計畫。為了使SQL Server建立一個執行計畫，它必須首先消耗一些寶貴的資源，比如CPU來編譯一個T-SQL批處理。當一個計劃編譯後，它被緩衝起來，因此在你的應用程式不止一次地調用相同的T-SQL語句時它可以被重用。如果你編寫你的T-SQL代碼來提高經常執行的T-SQL語句的緩衝計劃的重用，那麼你就能夠改進你的代碼效能。

隨著SQL Server 2005的推出，微軟提供了一些你可以用來探究緩衝計劃的DMV。通過使用這些DMV，你可以確認一些關於緩衝計劃的事情，下面是你可以確認的事情的簡短列表：

• 與一個緩衝計劃相關的文本

• 一個緩衝計劃執行的次數

• 緩衝計劃的規模

在後面我將告訴你怎樣使用DM來探究緩衝計劃資訊。

由於注釋或多餘空格而使得有多個計劃

我相信你們所有人都有將代碼放到預存程序中的想法。我們為了代碼在一個應用程式中或多個應用程式間重用而這麼做。但是，不是SQL Server執行的所有代碼都包含在預存程序中。一些應用程式可能以順序T-SQL代碼來編寫的。如果你在編寫順序T-SQL代碼，那麼你需要瞭解注釋你的代碼以及放置空格的方式可能會導致SQL Server為相同的T-SQL語句建立多個緩衝計劃。

下面是一個T-SQL指令碼的樣本，它包含兩個不同的T-SQL語句：

 

  
SELECT * FROM AdventureWorks.Production.Product  
  
GO  
  
SELECT * FROM AdventureWorks.Production.Product -- return records  
  
GO 
 

如同你所看到的，我有兩個類似的T-SQL語句。兩者都將返回AdventureWorks.Production.Product表的所有記錄。那麼你認為如果你運行這個代碼SQL Server會建立多少緩衝計劃呢？為了回答這個問題，讓我使用SQL Server 2005和SQL Server 2008中提供的一對DMV來看看這個緩衝計劃資訊。為了查看這兩個T-SQL語句產生的計劃，我要運行下面的代碼：

 

  
DBCC FREEPROCCACHE  
  
GO  
  
SELECT * FROM AdventureWorks.Production.Product  
  
GO  
  
SELECT * FROM AdventureWorks.Production.Product -- return records  
  
GO  
  
SELECT stats.execution_count AS exec_count,  
  
p.size_in_bytes as [size],  
  
[sql].[text] as [plan_text]  
  
FROM sys.dm_exec_cached_plans p  
  
outer apply sys.dm_exec_sql_text (p.plan_handle) sql  
  
join sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle  
  
GO 
 

在這個代碼中，我首先通過運行DBCC FREEPROCCACHE命令來釋放這個過程緩衝。這個命令刪除了記憶體中所有編譯的執行計畫。在這裡關於這個命令我必須提一個忠告。你不要在一個生產環境中運行DBCC FREEPROCCACHE命令。在你的生產環境中這麼做會刪除你所有產生的緩衝計劃，而這麼做可能會嚴影響你的生產環境，因為經常使用的計劃會被重新編譯。在釋放了過程緩衝之後，我執行我的兩個不同的SELECT語句。最後，我將從一對不同的DMV獲得的資訊串連在一起為這兩個SELECT語句返回一些緩衝的計劃資訊。當我運行這個時，我從這個引用不同DMV的SELECT語句獲得下面的輸出：

 

  
exec_count size plan_text  
  
-------------------- ----------- --------------------------------------------------------------------------  
  
1 40960 SELECT * FROM AdventureWorks.Production.Product -- return records  
  
1 40960 SELECT * FROM AdventureWorks.Production.Product 
 

正如你從這個輸出看到的，我上面的兩個SELECT語句建立了兩個不同的緩衝計劃，每個執行了一次(exec_count數目)。這個的原因是這些SELECT語句並不完全一樣。一個SELECT語句稍稍有些不同，因為它包含一個注釋。還有，注意緩衝計劃的大小，40,960位元組!這佔了很大一塊記憶體，卻只是用於這樣一個微不足道的T-SQL語句。

所以你必須注意你是怎樣注釋你的代碼的。剪下和黏貼是複製你的應用程式的一部分語句到另一部分的一個很好的方法，但是注意不要在你的類似的T-SQL語句前後或中間放置不同的注釋，這會導致多個計劃。

為相同的T-SQL命令產生多個緩衝計劃的另一個方式是在你的T-SQL語句中包含一些額外的空白字元。下面是兩個類似的命令，除了空格不同：

 

  
SELECT * FROM AdventureWorks.Production.Product  
  
GO  
  
SELECT * FROM AdventureWorks.Production.Product  
  
GO 
 

正如你所看到的，第二個語句在FROM從句和對象名稱之間包含一對多餘的空格。這個多餘的空格將導致SQL Server最佳化器認為這兩個語句是不同的，並因此為這兩個語句建立不同的緩衝計劃。在這裡就很明顯第二個T-SQL語句中有多餘的空格。但是如果你還在SELECT從句之前或語句之後添加一些其它字元，那麼這個語句可能看起來是一樣的，因為你不能看出這個空格，但是SQL Server可以看到它，所以它由於這個多餘的空格而建立多個緩衝計劃。

當SQL Server在查看一個批處理時，它將它同已經存在於預存程序緩衝中的計划進行對比。如果將要編譯的語句同一個已存在的緩衝計劃是完全一樣的話，那麼SQL Server不需要編譯並緩衝這個計划到記憶體中。SQL Server這麼做，以便它可以為類似語句重用計劃，如果這個計劃已經存在於緩衝中。為了最佳化你的代碼，你要確保你儘可能地重用緩衝計劃。

當你在開發應用程式代碼並在你的應用程式中放置T-SQL代碼、並且不使用預存程序時，你需要注意確保你儘可能地做到最佳的計劃重用。在編寫代碼時，如果我們想在我們的程式中不同的代碼塊中使用相同的代碼，那麼我們就都使用剪下和黏貼。如同你在上面的例子中看到的，你在這麼做時要小心。如果你在一個代碼塊中添加一些多餘的空格，或可能是一個不同的注釋，那麼你可能會得到不同的緩衝計劃。

最大限度地提高效能和盡量減少記憶體

要最佳化你的代碼，你不僅需要擔心你寫的每條命令以及你的資料庫設計，你還需要擔心你是否在類似的T-SQL語句中有多餘的注釋和空格。如果你不留意類似的T-SQL語句周圍的細節，你可能會導致SQL Server建立多個緩衝計劃。對相同的T-SQL語句具有多個計劃會導致SQL Server工作更繁重，而且由於儲存這些緩衝計劃而浪費記憶體。它可能看起來似乎並不是多麼重要，但是作為T-SQL語句編寫人員，我們需要確保我們在最佳化效能和盡量降低資源利用方面做到最好。其中一個方法就是確保你不會為相同的T-SQL語句緩衝多個計劃。