使用 SQL 参数克服临时性能问题

• 下载源文件 - 7 Kb

背景

我为一家小型网络公司工作，该公司在过去一两年里，在员工数量和网站流量方面都经历了巨大的增长。作为这种增长的一部分，我们不得不花费大量时间关注我们的数据库。由于我们缺乏 DBA，这项职责就落在了我身上。

我们现在每天要处理 100 万到 200 万的页面浏览量，在过去的一年里，我注意到我们数据库 CPU 和内存的最大负载并非主要来自性能不佳的查询（尽管确实存在一些），而是来自格式不正确的查询。

我注意到，在我阅读文章以及查看商业应用程序的代码时（稍后会详细介绍），即席查询被大量使用。尽管存在关于 SQL 注入攻击的无数警告以及使用存储过程的性能优势，但这种情况依然存在。

在本篇文章的目的中，我将（尽管不是很严格地）将即席查询定义为由 SQL 客户端应用程序以字符串形式构建并提交到 SQL Server 的 SQL 命令。本质上，这几乎包括了除了存储过程命令之外的所有内容。编写不佳的存储过程可能会因非SQL语句调优不佳的原因而消耗 CPU 和内存，但这超出了本文的范围。我打算重点介绍在 SQL 语句中未能使用参数可能对资源造成的巨大消耗，甚至可能拖垮最强大的硬件。

为了澄清起见，本文的语境是指大量用户并发执行相似的单个语句，而不是单个会话（连接）内的单个语句批处理。

SQL Server 内部机制

与任何编程语言一样，提交到 SQL Server 的每个查询都必须经过解析和编译才能执行。从宏观层面来看，这包括检查语法以及验证查询中使用的对象的存在。一旦完成这些步骤，SQL Server 就会得到一个它能够理解并用于检索请求数据的结构。为了提高效率，SQL Server 将此结构存储在内存（过程缓存）中以供重用。这样，下次提交相同的查询时，SQL Server 就可以跳过解析和编译步骤。

我们可以在以下代码示例中看到这一点。

SET STATISTICS TIME ON
SET STATISTICS IO OFF

USE AdventureWorks
go

DBCC FREEPROCCACHE
go


SELECT C.Title, C.FirstName, C.LastName, C.EmailAddress, C.Phone, E.*
FROM Person.Contact C
INNER JOIN HumanResources.Employee E ON E.ContactID = C.ContactID
WHERE C.LastName = 'Miller'

警告 - 在生产系统中使用 DBCC FREEPROCCACHE 命令不被推荐。这样做可能会导致 SQL Server 性能下降，直到过程缓存能够重新构建。在本篇文章中，它的目的是演示 SQL Server 如何使用过程缓存来存储和重用即席查询语句。

如果您在 SQL Server Management Studio (SSMS) 中查看“消息”窗格，您会发现以下内容：

SQL Server parse and compile time: 
   CPU time = 78 ms, elapsed time = 106 ms.

(1 row(s) affected)

在显示受影响行数的消息之前，您会看到解析和编译时间。这些时间会有所不同，而且这显然不是一个复杂的查询，所以不要期望看到什么显著的数字。我正在运行 SQL Server 2005 x64 在 Windows XP x64 上，配备 AMD Athlon 64 x2 4200+ 处理器和 2GB 内存。如果您再次运行仅 SELECT 语句，您很可能会看到这个：

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 9 ms.

(1 row(s) affected)

CPU 和经过时间下降的原因是，第二次运行语句（没有清除过程缓存缓冲区）时，该语句是从缓存中检索的。为了在实践中看到这一点，请创建一个以下跟踪：

/****************************************************/
/* Created by: SQL Server Profiler 2005             */
/* Date: 12/30/2006  10:01:02 AM                    */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc 
-- extension will be appended to the filename automatically. If you are 
-- writing from remote server to local drive, please use UNC path and make 
-- sure server has write access to your network share

exec @rc = sp_trace_create @TraceID output,0,N'C:\ProcedureCache',
                           @maxfilesize,NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 38, 1, @on
exec sp_trace_setevent @TraceID, 38, 21, @on
exec sp_trace_setevent @TraceID, 38, 10, @on
exec sp_trace_setevent @TraceID, 38, 35, @on
exec sp_trace_setevent @TraceID, 38, 12, @on
exec sp_trace_setevent @TraceID, 35, 1, @on
exec sp_trace_setevent @TraceID, 35, 10, @on
exec sp_trace_setevent @TraceID, 35, 35, @on
exec sp_trace_setevent @TraceID, 35, 12, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 35, 1, 6, N'AdventureWorks'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go
    

运行脚本时，请务必复制 TraceID 结果，以便在完成后使用以下命令停止跟踪：

--stop trace job
exec sp_trace_setstatus {PUT TRACEID HERE}
      , 0

创建跟踪后，再次按照前两个步骤操作。然后打开您的跟踪文件（如果您使用此脚本中的默认设置，则为 C:\ProcedureCache.trc），您将看到多个跟踪事件。其中，您将看到针对 SELECT 语句的 SP:CacheInsert 事件，以及在该语句下方针对相同 SELECT 语句的 SP:CacheHit 事件。这些事件确切地演示了我刚刚描述的内容。

即席 SQL

即席 SQL 非常灵活，而且通常比编写存储过程对开发人员来说更熟悉，因为它允许开发人员只需构建一个字符串（通常是通过字符串拼接）并将其提交给数据库。即席 SQL 可以使编写按表单查询（QBF）过程变得非常简单，因为用户输入会决定需要哪些表、连接和参数来满足请求。生成的 SQL 语句现在看起来可能像这样：

SELECT C.Title, C.FirstName, C.LastName, C.EmailAddress, C.Phone, E.*
FROM Person.Contact C
INNER JOIN HumanResources.Employee E ON E.ContactID = C.ContactID
WHERE C.LastName = 'Smith'

如果您在 SSMS 中再次运行此语句并查看解析和编译时间，您会发现该语句被重新编译了。另外，如果您仍在运行跟踪，您会发现该语句被添加到过程缓存中，而不是被重用。这表明，除非查询（包括 WHERE 子句中的值）完全相同，否则 SQL Server 必须重新编译该语句。在一个小型数据库、流量不大的情况下，这大多会被忽略。但在一个大型系统或流量相当大的系统上，您的过程缓存将会膨胀。这会产生一个副作用，即缓冲区缓存中的数据会被挤出，导致更多数据直接从磁盘读取而不是从内存读取。这会造成严重的 IO 瓶颈，最有可能拖垮您的系统。

几年前，我们购买了一个用于广告管理的 ASP.Net 应用程序。数据库大小不到 20MB，但由于流量（每秒 100-200 个批处理），它能够拖垮一台配备 3GHz Xeon 处理器、2GB 内存且未安装任何其他数据库的服务器。当我们调查这个问题时，我们发现 SQL Server 正在使用全部 2GB 内存，CPU 始终保持在 100%。

下面是一个示例，说明我们上面的查询语句在 .Net 客户端中可能看起来是什么样子：

string eName = "Smith";
DataSet data = new DataSet();
using(SqlConnection conn = new SqlConnection(
             "Application Name=AdHocClient;Server=(local);" +
             "Database=AdventureWorks;Trusted_Connection=Yes;"))
{
    StringBuilder sql = new StringBuilder();
    sql.Append("SELECT C.Title, C.FirstName, C.LastName, C.EmailAddress,");
    sql.Append("C.Phone, E.*");
    sql.Append(" FROM Person.Contact C");
    sql.Append(" INNER JOIN HumanResources.Employee E ");
    sql.Append(" ON E.ContactID = C.ContactID");
    sql.AppendFormat(" WHERE C.LastName = '{0}'", eName);
    
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    cmd.CommandText = sql.ToString();
    cmd.CommandType = CommandType.Text;
    
    try
    {
        using(SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            da.Fill(data);                        
        }
    }
    catch(SqlException ex)
    {
        //.... execption code .....
    }
}

变量 eName 代表从用户输入中检索到的值。每次进程使用不同的输入运行时，都会编译和缓存语句。这会导致我刚才描述的状况，最终压垮数据库服务器。那么，我们如何以最少的更改来解决这个问题呢？

参数化 SQL

出于安全和性能原因，存储过程是客户端应用程序推荐的数据库访问方法。但是，如果您已经编写了应用程序，并且没有时间将所有数据访问迁移到存储过程，那么另一种方法是参数化您的 SQL 语句。当您定义参数时，无论传递的值是什么，您的语句都将是相同的。所以，如果我们采用上述过程并重写如下：

string eName = "Smith";
DataSet data = new DataSet();
using(SqlConnection conn = new SqlConnection(
       "Application Name=ParameterizedClient;Server=(local);" +
       "Database=AdventureWorks;Trusted_Connection=Yes;"))
{
    StringBuilder sql = new StringBuilder();
    sql.Append("SELECT C.Title, C.FirstName, C.LastName, C.EmailAddress, ");
    sql.Append("C.Phone, E.*");
    sql.Append(" FROM Person.Contact C");
    sql.Append(" INNER JOIN HumanResources.Employee E ");
    sql.Append(" ON E.ContactID = C.ContactID");
    
    //replace contatenation of variable with parameter name
    sql.Append(" WHERE C.LastName = @LastName");
    
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    cmd.CommandText = sql.ToString();
    cmd.CommandType = CommandType.Text;
    
    //Define SqlParameter object and assign value to the parameter
    cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50);
    cmd.Parameters["@LastName"].Value = eName;
    
    try
    {
        using(SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            da.Fill(data);                        
        }
    }
    catch(SqlException ex)
    {
        //.... execption code .....
    }
}

现在，当您的客户端应用程序运行此过程时，SQL Server 将始终使用过程缓存中的已编译版本，从而防止缓存膨胀，并允许将更多系统内存用于缓冲区缓存，从而提高系统性能和应用程序的可伸缩性。

SqlCommand.Prepare()

一位会员询问使用 SqlCommand 对象的 Prepare() 方法是否会更有效。在本篇文章的上下文中，答案是：不会。Prepare() 方法调用 sp_prepexec，这是一个未公开的存储过程。本质上，它为当前会话创建一个临时存储过程，该过程在当前连接关闭时销毁。这允许在连接关闭之前，像调用存储过程一样调用查询。使用 Prepare() 的好处是它减少了网络流量，因为在客户端应用程序中定义的整个命令文本只会在网络上发送一次。在调用 Prepare() 方法后第一次执行命令时，它看起来类似于这样：

declare @p1 int
set @p1=10001
exec sp_prepexec @p1 output,N'SELECT C.Title, C.FirstName, C.LastName, 
      C.EmailAddress, C.Phone,E.*
    FROM Person.Contact C
    INNER JOIN HumanResources.Employee E ON E.ContactID = C.ContactID
    WHERE C.LastName = @LastName
 ',@EmployeeID=15073
select @p1                
            

请注意它是如何声明 @p1 并将其作为标量值与命令结果一起返回的。然后，SqlCommand 对象可以在后续请求中发送以下命令：

exec sp_execute 10001,@EmployeeID=12991

在这种情况下，使用 Prepare() 方法效率不高的原因是，除非使用相同的连接对象并且该连接保持打开状态，否则发送到 Sql Server 的请求将始终是调用 sp_prepexec。因此，这增加了创建临时存储过程以及该存储过程句柄的开销，但您却无法获得减少流量的好处。尽管如此，这种开销不太可能对 SQL Server 造成巨大影响，但我建议省略对 Prepare() 的调用，除非有必要。

当 SqlCommand 对象在同一会话中（通常在循环内）被反复调用时，Prepare() 方法将发挥优势。与不调用 Prepare() 相比，它要快得多。我在这里不会提供任何基准测试比较。留给您，读者，来完成。但在随附的示例项目中，我包含了一个方法，可以让您进行一些自己的测试。

示例项目

本文随附的示例项目运行了一个取自 AdventureWorks 数据库的存储过程，并将整个存储过程文本传递给一个命令对象，以模拟具有单个参数的更复杂的查询。该查询有两个版本：一个即席版本和一个参数化版本。每个版本都运行 10,000 次，并使用一个随机整数作为参数。

我建议在运行示例项目时使用 perfmon。我使用了计数器 Process.% Processor Time - sqlserver、SQL Server:SQL Statistics.Batch Requests/Sec 和 SQL Server:Plan Cache - Cache Pages。以下是我的结果：

	运行时间	CPU	请求/秒	缓存页 - 开始	缓存页 - 结束
即席	115 秒	95%	109	600	43684
参数化	10 秒	59%	1153	50	260

正如您所见，如果没有参数，该过程将花费 10 倍的时间来运行，CPU 负载将近高出 2 倍，而过程缓存使用的 8k 页数量将是原来的 200 倍。最后一个计数器（8k 页）将随着应用程序的运行时间而持续变差，因为当查询被参数化时，它只会向缓存添加一个计划版本。反之，即席版本将为传递给它的每个唯一变量添加一个新的计划版本。

结论

虽然存储过程是数据访问客户端推荐的方法，但如果您的代码已经在使用即席 SQL，那么重写所有内容并不总是可行的。然而，使用参数可以获得与使用存储过程几乎相同的性能优势。所以，如果您还没有这样做，那么真的不应该有任何借口不参数化您的查询。您的应用程序将具有更好的可伸缩性，并且将更稳定。

修订历史

1/10/2007

• 添加了文章语境的说明
• 添加了 SqlCommand.Prepare() 部分
• 在示例项目中添加了计时器
• 从示例项目和文章中的示例代码中移除了字符串拼接的使用
• 在示例项目中，使用 SqlCommand.ExecuteReader() 替换了 SqlDataAdapter.Fill()
• 在示例项目中添加了演示使用 SqlCommand.Prepare() 的方法