利用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语句缓存多个计划。

【编辑推荐】