背景
我一个小的网络公司,在过去的一年或两年经历了两个方面的工作人员以及网站流量的增长大量工作。作为增长的一部分,我们已经不得不花费大量的时间,专注于我们的数据库。因为我们缺乏一个DBA,管理已下降到我。
我们现在成为每天1至2万页的意见,并在过去的一年,它已经到了我的注意,在我们的数据库最大的CPU和内存的负载没有这么多,由于表现欠佳的查询(虽然有一些),而是很差格式化的查询。
我注意到,因为我阅读文章,也可以作为我在商业应用程序运行的代码(稍后),即席查询使用相当位。这一切,尽管多次警告,随处可见关于SQL注入式攻击以及使用存储过程的性能优势。
对于这篇文章中,我定义(而不是松散)作为字符串的内置SQL客户端应用程序,并提交给SQL Server的SQL命令的即席查询的目的。这在本质上是相当包罗万象,包括几乎一切,但存储过程命令。写得不好的存储过程可以不佳调整SQL语句以外的原因,CPU和记忆流失,但这些原因超出了本文的范围。我打算把重点放在如何未能在SQL语句中使用参数,可以是对资源的巨大消耗和削弱,即使是最强大的硬件。
澄清,这篇文章的背景下,大量的用户执行类似的单个语句,同时,而不是在一个会话(连接),以单一批次的statments。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如何使用过程高速缓存中存储和重用即席查询报表。
如果您查看"; Messagesquot;在SQL Server管理Studio(SSMS)中的窗格中,你会发现下面的:{C}
除其他事项外,前行受影响的邮件,你会看到分析和编译时间。这些时间会有所不同,这显然不是一个复杂的查询,所以不希望看到任何显著。我的AMD Athlon 64 X2 4200运行SQL Server 2005 x64的2GB的内存在Windows XP x64的。如果您运行只有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如果你从这个脚本使用的是默认值),你会看到几个跟踪事件。其中,你会看到一个SP:CacheInsert事件条目SELECT语句进一步下降为SP:相同的SELECT语句的CacheHit事件。这些事件表明,正是我刚才所描述的。即席SQL
即席SQL是非常灵活,往往比编写存储过程开发人员更熟悉,因为它允许开发人员简单地构造一个字符串(通常通过contatenation),并提交到数据库。特设可以使书面窗体查询"(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服务器使用所有2GB的内存和CPU卡100%。
下面是我们的查询语句看起来可能从一个网络客户端的例子。
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服务器将始终使用的过程高速缓存中的编译版本,以防止腹胀的缓存,并允许更多的系统内存缓冲区高速缓存的使用,这也将提高您的系统性能和应用scaleablility。SqlCommand.Prepare()
一位成员问weither使用SqlCommand对象的准备()方法,它会更有效。在这篇文章的背景下,答案是,没有。 prepare()方法调用sp_prepexec,一个未公开的存储过程。从本质上讲,它会创建一个临时存储过程为本届会议,这是当前连接被关闭时销毁。这使得被称为查询就好像它是一个存储过程,直到连接被关闭。使用准备()的好处是,它降低了网络流量,在客户端应用程序定义的,因为整个命令文本是在网络上只发送一次。第一次调用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

它之所以没有更有效地使用准备()方法是在这种情况下,因为除非使用相同的连接对象是仍然开放,将请求发送到SQL Server始终sp_prepexec呼叫。因此,这增加了创建临时存储过程,以及与该存储过程的处理开销,但你没有得到减少交通的好处。这种开销将创建一个SQL Server上产生很大的影响,然而这是不可能的,但我建议省略了调用准备(),除非它的需要。
准备()方法将大放异彩SqlCommand对象时将调用在同一会期内(通常是在一个循环)反复。没有准备()相比,它要快得多。我不会提供任何基准比较。我离开你,读者。但在示例项目中,我有一种方法,这将允许你做一些自己的测试。示例项目
本文附带的示例项目运行一个存储过程从AdventureWorks数据库和整个文本的存储过程传递一个命令对象为文本的模拟与一个单一的参数更复杂的查询。有两个版本的查询,特设版本和一个参数的版本。每一个运行10000次,与作为参数的随机整数。
我建议同时运行的PerfMon示例项目正在运行。 %处理器时间,我用的柜台流程 - SQLSERVER中,SQL Server:SQL Statistics.Batch请求/秒和SQL Server计划缓存 - 缓存页面。这里是我的结果:运行时间CPU的请求/秒缓存页面 - 开始高速缓存页 - 完特设115证交会95%10960043684参数10秒59% 1153260
正如你可以看到,不带参数,这个过程可能需要10倍更长的时间来运行,CPU运行速度高出近2倍和过程高速缓存使用8K页的数目是200倍。最后一个计数器(8K页)将继续恶化不再在应用程序运行的参数化查询时,它只会增加一个版本的计划缓存。相反,专案版本将增加传递给它的每一个独特的可变的一个新版本的计划。结论
虽然存储过程数据访问客户端推荐的方法,它不总是可行的改写一切,如果你的代码已经使用即席SQL。然而,你可以使用,你可以从存储过程的参数几乎相同的性能优势。因此,如果你还没有做,所以确实不应该有任何借口,不参数化查询的。您的应用程序将更好的规模将更加稳定。修订历史1/10/2007增加了文章的上下文的澄清新增SqlCommand.Prepare()第添加定时器示例项目删除样本项目,并在本文的示例代码使用的串联代替SqlDataAdapter.Fill(SqlCommand.ExecuteReader())在示例项目添加的方法示例项目来演示使用SqlCommand.Prepare()

回答

评论会员:明星刘 时间:2011/12/07
我读你的文章,并发现它非常适合我{S0}有用,但我也不很明白"执行查询"和"格式的查询"。请您解释它们
评论会员:?明星刘 时间:2011/12/07
性能与格式指的事实,在数据库服务器上的负载是不是由于实际执行查询(查询性能),而是由于工作需要之前执行它们来解析查询(格式)。简单地说,我指的是两个单独的步骤,提交到SQL Server的查询时,会出现。首先,SQL Server必须解析查询生成一个执行计划。由于解析查询文本​​是昂贵的CPU,如果承认它已经解析查询和存储执行计划的SQL Server,则跳过此步骤,从而节省CPU周期。一旦SQL Server已获得通过用解析或从缓存中检索,然后它会执行查询的执行计划。因此,查询的格式影响的第一步(准备/分析),执行计划(列的数量,联接,筛选和排序的列)影响的第二个步骤(查询性能)。

守则负责任:{A}
马克的博客:


比尔考斯比 - "一个明智的话是没有必要的的 - 这是愚蠢的,需要的意见。"
评论会员:明星刘 时间:2011/12/07
我看。顺便说一下,SQL Server如何知道已经有一个执行计划的查询文本,它的排序哈希
评论会员:?astanton1978 时间:2011/12/07
是的,这就是为什么使用参数执行如此之快。

守则负责任:{A}
马克的博客:

比尔考斯比 - "一个明智的话是没有必要的的 - 这是愚蠢的,需要的意见。"
评论会员:astanton1978 时间:2011/12/07
谢谢〜{S0}
评论会员:什锦黎刹 时间:2011/12/07
1000迭代 &# 160; DIM CONN作为新的SqlConnection(strConnection)
 60; DIM STRSQL新的StringBuilder
  ; strSQL.Append([用户名]从[用户],其中[用户ID] ="Convert.ToString(USERID))
作为新的SqlCommand(strSQL.ToString()DIM CMD,CONN)

 60; 尝试
 60;conn.Open()
DIM startWithoutParam的DateTime
DIM endWithoutParam的DateTime
startWithoutParam = DateTime.Now
&# 160; cmd.ExecuteScalar()
  ; endWithoutParam = DateTime.Now
  ; "总结的结果,显示完成执行
dblWithoutMS = dblWithoutMS endWithoutParam.Subtract(startWithoutParam)。TotalMilliseconds
赶上前为例外
 0; strExceptions.Append("无参数化:"strSQL.ToString()ex.Message)
最后
conn.Close()
cmd.Dispose()
 60; conn.Dispose()
END TRY

运行比一个重复的方法,这种替代

strSQL.Append("选择[用户名]从[用户],其中[用户ID] = @用户名")
作为新的SqlCommand(strSQL.ToString()DIM CMD,CONN)

 60; cmd.Parameters.Add("@用户名",SqlDbType.Int)
 0; cmd.Parameters("@用户名")值= USERID

我找错性能指标在SqlCommand的执行时间?
此外,我没有看到在syscacheobjects列出了这些疑问。

我错过什么?

- 安德鲁
评论会员:什锦黎刹 时间:2011/12/07
如果字符串的用户名的值永远不会改变,那么有没有性能上的差异。 SQL Server将编译查询一次,它存储在过程高速缓存和重用它每次。如果你想测试的差异,你需要创建一个随机字符串作为userid的值。或从数据库中所有的用户ID值的列表,并通过用户ID列表进行迭代,直到您到达1000次迭代(或任何你选择的号码)。

至于为什么这些查询中不syscacheobjects上市,我不知道我可以回答说。尝试在运行测试之前调用DBCC FREEPROCCACHE。它完成后,运行此查询:
SQL 2005 -

SELECT * FROM SYS.syscacheobjects OBJTYPE ="即席"

SQL 2000 -

SELECT * FROM syscacheobjects OBJTYPE ="即席"

除非你有其他正在运行的进程,查询您的SQL Server,那么您的测试查询应在那里唯一的。
评论会员:拉力鸡 时间:2011/12/07
我发现我的所有问题的根源。 SQL Server不缓存"微不足道"的查询。所以,我基本上每次运行一个全新的查询。

在MS的网站上的快速搜索,我不看任何地方,概述是什么使一个查询琐碎的细节,但我能理解我的例子是一个tivial如何。

我改变了查询加入几桌,我现在看到这么好,你所说的在您的文章的好处。感谢马克{S2}
评论会员:!拉力鸡 时间:2011/12/07
我觉得我在读一本书,我在家里琐碎的计划,但因为我不在家,我挖这个不错(虽然过时)定义一个简单的计划。看{A7}]完整的文章。下文是从一节题为"优化工具":

"SQL Server优化真的是由单独的块。第一部分是被称为琐细计划优化非基于成本的优化。琐细计划优化的整体思路是,基于成本的优化是昂贵的,做的时候真的有SQL语句只有一个可行的计划。一个最好的例子是一个VALUES子句的INSERT语句组成的查询。,只有一个可能的计划。另一个例子是在一个独特的覆盖索引的所有列是一个SELECT语句的WHERE有没有其他的指数列设置这两个例子的情况下,SQL Server应该简单地生成的计划和尝试研究多个计划,看看是否有更好的东西。琐细计划优化器发现真正明显的计划,这是典型的非常便宜,所以最简单的查询往往是早在这个过程中淘汰和优化不花大量的时间寻找一个好的计划,这是一件好事,因为潜在的数量在SQL Server 7.0计划高达天文数字了SQL Server的哈希联接,合并联接,指数的十字路口,其加工技术的清单。"

虽然优化已取代2005年在SQL(重新写入),我相信这个最有可能continutes的是真实的。
评论会员:西蒙斯 时间:2011/12/07
伟大的文章,但我有问题,如果我需要查询范围的值,例如:其中CountryID('1','2')从国家选择*。什么是实现这一目标的最佳策略?早在3个月前,我是开发一些网站的一种,报告有关汽车生产和需求的范围模式,由用户选择什么采取。因此,我创建存储过程,并建立动态SQL。其实查询性能不满意我,但我没有看到另一种解决方法,因为"IN"的条款不能作为SQL参数的传递。任何想法?

什锦R
评论会员:西蒙斯 时间:2011/12/07
如果你真的想要做的范围内,您可以使用之间或"CountryID> = 1 CountryID = <2"。更好列表,而不是一个范围。不过,如果你想使用IN子句是一种变通方法,让你的参数。我的另一篇文章会谈的有关参数化IN子句:{A8}
评论会员:伟文 时间:2011/12/07
我检查你的链接,我觉得你的解决方法是辉煌的,是这是我寻找
感谢您的答复。



什锦ř

关于作者

马克J.米勒
中国
我是一名编程爱好者,
谢谢orcode.com为我们提供一个学习和分享的平台。
有什么问题。可以就本内容回复,我看到时。会尽量回复的。
或代码 网站备案号:粤ICP备15020848号-1