利用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語句緩存多個計劃。
【編輯推薦】
