图片 3

《Microsoft Sql server 2008 Internal》读书笔记–第九章Plan Caching and Recompilation(2)

什么是参数化查询?
一个简单理解参数化查询的方式是把它看做只是一个T-SQL查询,它接受控制这个查询返回什么的参数。通过使用不同的参数,一个参数化查询返回不同的结果。要获得一个参数化查询,你需要以一种特定的方式来编写你的代码,或它需要满足一组特定的标准。

 

首先说明一下SQL Server内存占用由哪几部分组成。SQL
Server占用的内存主要由三部分组成:数据缓存(Data
Buffer)、执行缓存(Procedure Cache)、以及SQL Server引擎程序。SQL
Server引擎程序所占用缓存一般相对变化不大,则我们进行内存调优的主要着眼点在数据缓存和执行缓存的控制上。本文主要介绍一下执行缓存的调优。数据缓存的调优将在另外的文章中介绍。
对于减少执行缓存的占用,主要可以通过使用参数化查询减少内存占用。
1、使用参数化查询减少执行缓存占用
我们通过如下例子来说明一下使用参数化查询对缓存占用的影响。为方便试验,我们使用了一台没有其它负载的SQL
Server进行如下实验。 下面的脚本循环执行一个简单的查询,共执行10000次。
首先,我们清空一下SQL Server已经占用的缓存: dbcc freeproccache
然后,执行脚本: 复制代码 代码如下:
DECLARE @t datetime SET @t = getdate() SET NOCOUNT ON DECLARE @i INT,
@count INT, @sql nvarchar(4000) SET @i = 20000 WHILE @i = 30000 BEGIN
SET @sql = ‘SELECT @count=count(*) FROM P_Order WHERE MobileNo = ‘ +
cast( @i as varchar(10) ) EXEC sp_executesql @sql ,N’@count INT
OUTPUT’, @count OUTPUT SET @i = @i + 1 END PRINT DATEDIFF( second, @t,
current_timestamp ) 输出: DBCC 执行完毕。如果 DBCC
输出了错误信息,请与系统管理员联系。 11 使用了11秒完成10000次查询。
我们看一下SQL Server缓存中所占用的查询计划: Select Count(*)
CNT,sum(size_in_bytes) TotalSize From sys.dm_exec_cached_plans
查询结果:共有2628条执行计划缓存在SQL Server中。它们所占用的缓存达到:
92172288字节 = 90012KB = 87 MB。 我们也可以使用dbcc memorystatus
命令来检查SQL Server的执行缓存和数据缓存占用。 执行结果如下:
执行缓存占用了90088KB,有2629个查询计划在缓存里,有1489页空闲内存可以被数据缓存和其他请求所使用。
我们现在修改一下前面的脚本,然后重新执行一下dbcc
freeproccache。再执行一遍修改后的脚本: 复制代码 代码如下: DECLARE @t datetime SET @t =
getdate() SET NOCOUNT ON DECLARE @i INT, @count INT, @sql nvarchar(4000)
SET @i = 20000 WHILE @i = 30000 BEGIN SET @sql = ‘select
@count=count(*) FROM P_Order WHERE MobileNo = @i’ EXEC sp_executesql
@sql, N’@count int output, @i int’, @count OUTPUT, @i SET @i = @i + 1
END PRINT DATEDIFF( second, @t, current_timestamp ) 输出: DBCC
执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。 1
即这次只用1秒钟即完成了10000次查询。
我们再看一下sys.dm_exec_cached_plans中的查询计划: Select Count(*)
CNT,sum(size_in_bytes) TotalSize From sys.dm_exec_cached_plans
查询结果:共有4条执行计划被缓存。它们共占用内存: 172032字节 = 168KB。
如果执行dbcc memorystatus,则得到结果:
有12875页空闲内存(每页8KB)可以被数据缓存所使用。
到这里,我们已经看到了一个反差相当明显的结果。在现实中,这个例子中的前者,正是经常被使用的一种执行SQL脚本的方式。
解释一下原因:
我们知道,SQL语句在执行前首先将被编译并通过查询优化引擎进行优化,从而得到优化后的执行计划,然后按照执行计划被执行。对于整体相似、仅仅是参数不同的SQL语句,SQL
Server可以重用执行计划。但对于不同的SQL语句,SQL
Server并不能重复使用以前的执行计划,而是需要重新编译出一个新的执行计划。同时,SQL
Server在内存足够使用的情况下,此时并不主动清除以前保存的查询计划。这样,不同的SQL语句执行方式,就将会大大影响SQL
Server中存储的查询计划数目。如果限定了SQL
Server最大可用内存,则过多无用的执行计划占用,将导致SQL
Server可用内存减少,从而在执行查询时尤其是大的查询时与磁盘发生更多的内存页交换。如果没有限定最大可用内存,则SQL
Server由于可用内存减少,从而会占用更多内存。
对此,我们一般可以通过两种方式实现参数化查询:一是尽可能使用存储过程执行SQL语句,二是使用sp_executesql
方式执行单个SQL语句(注意不要像上面的第一个例子那样使用sp_executesql)。
在现实的同一个软件系统中,大量的负载类型往往是类似的,所区别的也只是每次传入的具体参数值的不同。所以使用参数化查询是必要和可能的。另外,通过这个例子我们也看到,由于使用了参数化查询,不仅仅是优化了SQL
Server内存占用,而且由于能够重复使用前面被编译的执行计划,使后面的执行不需要再次编译,最终执行10000次查询总共只使用了1秒钟时间。
2、检查并分析SQL Server执行缓存中的执行计划
通过上面的介绍,我们可以看到SQL缓存所占用的内存大小。也知道了SQL
Server执行缓存中的内容主要是各种SQL语句的执行计划。则要对缓存进行优化,就可以通过具体分析缓存中的执行计划,看看哪些是有用的、哪些是无用的执行计划来分析和定位问题。
通过查询DMV:
sys.dm_exec_cached_plans,可以了解数据库中的缓存情况,包括被使用的次数、缓存类型、占用的内存大小等。
SELECT usecounts, cacheobjtype, objtype,size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans

有两种不同的方式来创建参数化查询。第一个方式是让查询优化器自动地参数化你的查询。另一个方式是通过以一个特定方式来编写你的T-SQL代码,并将它传递给sp_executesql系统存储过程,从而编程一个参数化查询。这篇文章的后面部分将介绍这个方法。

《Microsoft Sql server 2008 Internals》读书笔记订阅地址:

通过缓存计划的plan_handle可以查询到该执行计划详细信息,包括所对应的SQL语句:

参数化查询的关键是查询优化器将创建一个可以重用的缓存计划。通过自动地或编程使用参数化查询,SQL
Server可以优化类似T-SQL语句的处理。这个优化消除了对使用高贵资源为这些类似T-SQL语句的每一次执行创建一个缓存计划的需求。而且通过创建一个可重用计划,SQL
Server还减少了存放过程缓存中类似的执行计划所需的内存使用。

SELECT TOP 100 usecounts,

现在让我们看看使得SQL Server创建参数化查询的不同方式。

《Microsoft Sql server 2008 Internals》索引目录:

objtype,

参数化查询是怎样自动创建的?

《Microsoft Sql server 2008
Internal》读书笔记–目录索引

p.size_in_bytes,

微软编写查询优化器代码的人竭尽全力地优化SQL
Server处理你的T-SQL命令的方式。我想这是查询优化器名称的由来。这些尽量减少资源和最大限度地提高查询优化器执行性能的方法之一是查看一个T-SQL语句并确定它们是否可以被参数化。要了解这是如何工作的,让我们看看下面的T-SQL语句:

 

[sql].[text]

SELECT* FROMAdventureWorks.Sales.SalesOrderHeader
WHERESalesOrderID=56000;
GO在这里,你可以看到这个命令有两个特点。第一它简单,第二它在WHERE谓词中包含一个用于SalesOrderID值的指定值。查询优化器可以识别这个查询比较简单以及SalesOrderID有一个参数(“56000”)。因此,查询优化器可以自动地参数化这个查询。

在这一节,我们将继续关注Adhoc和参数化

FROM sys.dm_exec_cached_plans p

如果你使用下面的SELECT语句来查看一个只包含用于上面语句的缓存计划的、干净的缓冲池,那么你会看到查询优化器将T-SQL查询重写为一个参数化T-SQL语句:

Adhoc混合负载的优化

OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql

SELECTstats.execution_countAScnt, p.size_in_bytesAS[size],
[sql].[text]AS[plan_text] FROMsys.dm_exec_cached_plansp
OUTERAPPLYsys.dm_exec_sql_text(p.plan_handle)sql
JOINsys.dm_exec_query_statsstats ONstats.plan_handle=p.plan_handle;
GO当我在一个SQL Server
2008实例上运行这个命令时,我得到下面的输出,(注意,输出被重新格式化了,以便它更易读):

如果你的查询大多数是临时(adhoc)的,从来不会被重用,似乎浪费内存去缓存这些计划了。SQL
Server 2008增加了一个配置选项在这种情况下也能满足你的需求。一旦选项启用,仅仅在第一次任何一个adhoc查询被编译时缓存一个存根,在第二次编译后,存根用以取代全部计划。

ORDER BY usecounts

cnt size plan_text

**控制为Ad Hoc混合负载设置的优化**

我们可以选择针对那些执行计划占用较大内存、而被重用次数较少的SQL语句进行重点分析。看其调用方式是否合理。另外,也可以对执行计划被重复使用次数较多的SQL语句进行分析,看其执行计划是否已经经过优化。进一步,通过对查询计划的分析,还可以根据需要找到系统中最占用IO、CPU时间、执行次数最多的一些SQL语句,然后进行相应的调优分析。篇幅所限,这里不对此进行过多介绍。读者可以查阅联机丛书中的:sys.dm_exec_query_plan内容得到相关帮助。


启用选项方法有二:
1、

附:


EXEC sp_configure ‘optimize for ad hoc workloads’, 1;
RECONFIGURE;

1:关于DBCC MEMORY,可以查看微软的知识库:

1 49152 (@1 int)SELECT * FROM
[AdventureWorks].[Sales].[SalesOrderHeader]

2、 在SQL Server Management Studio界面,高级-属性-中启用。
图片 1

2:关于sys.dm_exec_cached_plans和sys.dm_exec_sql_text,请参阅联机丛书。

WHERE [SalesOrderID]=@1

 编译计划存根

如果你看看上面输出中的plan_text字段,你会看到它不像原来的T-SQL文本。如前所述,查询优化器将这个查询重新编写为一个参数化T-SQL语句。在这里,你可以看到它现在有一个数据类型为(int)的变量(@1),它在之前的SELECT语句中被定义的。另外在plan_text的末尾,
值“56000”被替换为变量@1。既然这个T-SQL语句被重写了,而且被存储为一个缓存计划,那么如果未来一个T-SQL命令和它大致相同,只有SalesOrderID字段被赋的值不同的话,它就可以被用于重用。让我们在动作中看看它。

当 optimize for ad hoc workloads启用时,SQL
Server缓存的存根仅仅大约300字节,并不包含查询计划的任何部分。它基本上仅仅是一个容器,以保持跟踪某一特殊的查询的前次编译。
这个存根包含了全部的缓存键和一个指向实际查询文本的指针。这个存根被存在SQL
Manager 缓存。该存根的usecounts值始终是1,因为从来不会重用。

如果我在我的机器上运行下面的命令: DBCCFREEPROCCACHE; GO SELECT*
FROMAdventureWorks.Sales.SalesOrderHeader WHERESalesOrderID=56000; GO
SELECT* FROMAdventureWorks.Sales.SalesOrderHeader
WHERESalesOrderID=56001; GO SELECTstats.execution_countAScnt,
p.size_in_bytesAS[size], [sql].[text]AS[plan_text]
FROMsys.dm_exec_cached_plansp
OUTERAPPLYsys.dm_exec_sql_text(p.plan_handle)sql
JOINsys.dm_exec_query_statsstats ONstats.plan_handle=p.plan_handle;
GO
我从最后的SELECT语句得到下面的输出,(注意,输出被重新格式化以便它更易读):

当生成编译计划存根的一个查询或批处理被重新编译时,这个存根被全编译计划代替。初始,usecounts被设置为1,因为不确定前一个查询是否与执行计划完全精确相同。

cntsizeplan_text

249152(@1int)SELECT*FROMAdventureWorks].[Sales].[SalesOrderHeader]
WHERE[SalesOrderID]=@1在这里,我首先释放过程缓存,然后我执行两个不同、但却类似的非参数化查询来看看查询优化器是会创建两个不同的缓存计划还是创建用于这两个查询的一个缓存计划。在这里,你可以看到查询优化器事实上很聪明,它参数化第一个查询并缓存了计划。然后当第二个类似、但有一个不同的SalesOrderID值的查询发送到SQL
Server时,优化器可以识别已经缓存了一个计划,然后重用它来处理第二个查询。你可以这么说是因为“cnt”字段现在表明这个计划被用了两次。

数据库配置选项PARAMETERIZATION可以影响T-SQL语句怎样被自动地参数化。对于这个选项有两种不同的设置,SIMPLE和FORCED。当PARAMETERIZATION设置被设置为SIMPLE时,只有简单的T-SQL语句才会被参数化。要介绍这个,看下下面的命令:

SELECTSUM(LineTotal)ASLineTotal
FROMAdventureWorks.Sales.SalesOrderHeaderH
JOINAdventureWorks.Sales.SalesOrderDetailDOND.SalesOrderID=H.SalesOrderID
WHEREH.SalesOrderID=56000这个查询类似于我前面的示例,除了在这里我添加了一个额外的JOIN标准。当数据库AdventureWorks的PARAMETERIZATION选项被设置为SIMPLE时,这个查询不会被自动地参数化。SIMPLE
PARAMETERIZATION设置告诉查询优化器只参数化简单的查询。但是当选项PARAMETERIZATION被设置为FORCED时,这个查询将被自动地参数化。

当你设置数据库选项为使用FORCE
PARAMETERIZATION时,查询优化器试图参数化所有的查询,而不仅仅是简单的查询。你可能会认为这很好。但是在某些情况下,当数据库设置PARAMETERIZATION为FORCED时,查询优化器将选择不是很理想的查询计划。当数据库设置PARAMETER为FORCED时,它改变查询中的字面常量。这可能导致当查询中涉及计算字段时索引和索引视图不被选中参与到执行计划中,从而导致一个无效的计划。FORCED
PARAMETERIZATION选项可能是改进具有大量类似的、传递过来的参数稍有不同的查询的数据库性能的一个很好的解决方案。一个在线销售应用程序,它的客户对你的产品执行大量的类似搜索,
产品值不同,这可能是一个能够受益于FORCED
PARAMETERIZATION的很好的应用程序类型。

不是所有的查询从句都会被参数化。例如查询的TOP、TABLESAMPLE、
HAVING、GROUP BY、ORDER BY、OUTPUT…INTO或FOR XML从句不会被参数化。

使用sp_execute_sql来参数化你的T-SQL

你不需要依赖于数据库的PARAMETERIZATION选项来使得查询优化器参数化一个查询。你可以参数化你自己的查询。你通过重新编写你的T-SQL语句并使用“sp_executesql”系统存储过程执行重写的语句来实现。正如已经看到的,上面包括一个“JOIN”从句的SELECT语句在数据库的PARAMETERIZATION设置为SIMPLE时没有被自动参数化。让我重新编写这个查询以便查询优化器将创建一个可重用的参数化查询执行计划。

为了说明,让我们看两个类似的、不会被自动参数化的T-SQL语句,并创建两个不同的缓存执行计划。然后我将重新编写这两个查询使得它们都使用相同的缓存参数化执行计划。

让我们看看这个代码:

DBCCFREEPROCCACHE GO SELECTSUM(LineTotal)ASLineTotal
FROMAdventureWorks.Sales.SalesOrderHeaderH
JOINAdventureWorks.Sales.SalesOrderDetailDOND.SalesOrderID=H.SalesOrderID
WHEREH.SalesOrderID=56000 GO SELECTSUM(LineTotal)ASLineTotal
FROMAdventureWorks.Sales.SalesOrderHeaderH
JOINAdventureWorks.Sales.SalesOrderDetailDOND.SalesOrderID=H.SalesOrderID
WHEREH.SalesOrderID=56001 GO SELECTstats.execution_countAScnt,
p.size_in_bytesAS[size], LEFT([sql].[text],200)AS[plan_text]
FROMsys.dm_exec_cached_plansp
OUTERAPPLYsys.dm_exec_sql_text(p.plan_handle)sql
JOINsys.dm_exec_query_statsstatsONstats.plan_handle=p.plan_handle;
GO在这里,我释放了过程缓存,然后运行这两个包含一个JOIN的、不同的非简单的T-SQL语句。然后我将检查缓存计划。这是这个使用DMV
的SELECT语句的输出(注意,输出被重新格式化了,以便它更易读):

我们看一个例子:

cntsizeplan_text

149152SELECTSUM(LineTotal)ASLineTotal
FROMAdventureWorks.Sales.SalesOrderHeaderH
JOINAdventureWorks.Sales.SalesOrderDetailD
OND.SalesOrderID=H.SalesOrderID WHEREH.SalesOrderID=56001
149152SELECTSUM(LineTotal)ASLineTotal
FROMAdventureWorks.Sales.SalesOrderHeaderH
JOINAdventureWorks.Sales.SalesOrderDetailD
OND.SalesOrderID=H.SalesOrderID
WHEREH.SalesOrderID=56000正如你从这个输出看到的,这两个SELECT语句没有被查询优化器参数化。优化器创建了两个不同的缓存执行计划,每一个都只被执行了一次。我们可以通过使用sp_executesql系统存储过程来帮助优化器为这两个不同的SELECT语句创建一个参数化执行计划。下面是上面的代码被重新编写来使用sp_executesql
系统存储过程: DBCCFREEPROCCACHE; GO
EXECsp_executesqlN’SELECTSUM(LineTotal)ASLineTotal
FROMAdventureWorks.Sales.SalesOrderHeaderH
JOINAdventureWorks.Sales.SalesOrderDetailDOND.SalesOrderID=H.SalesOrderID
WHEREH.SalesOrderID=@SalesOrderID’,N’@SalesOrderIDINT’,@SalesOrderID=56000;
GO EXECsp_executesqlN’SELECTSUM(LineTotal)ASLineTotal
FROMAdventureWorks.Sales.SalesOrderHeaderH
JOINAdventureWorks.Sales.SalesOrderDetailDOND.SalesOrderID=H.SalesOrderID
WHEREH.SalesOrderID=@SalesOrderID’,N’@SalesOrderIDINT’,@SalesOrderID=56001;
GO SELECTstats.execution_countASexec_count,
p.size_in_bytesAS[size], [sql].[text]AS[plan_text]
FROMsys.dm_exec_cached_plansp
OUTERAPPLYsys.dm_exec_sql_text(p.plan_handle)sql
JOINsys.dm_exec_query_statsstatsONstats.plan_handle=p.plan_handle;
GO如同你所看到的,我重新编写了这两个SELECT语句,使它们通过使用“EXEC
sp_executesql”语句来执行。对这些EXEC语句中的每一个,我都传递三个不同的参数。第一个参数是基本的SELECT语句,但是我将SalesOrderID的值用一个变量(@SalesOrderID)替代。在第二个参数中,我确定了@SalesOrderID的数据类型,在这个例子中它是一个integer。然后在最后一个参数中,我传递了SalesOrderID的值。这个参数将控制我的SELECT根据SalesOrderID值所生成的结果。sp_executesql的每次执行中前两个参数都是一样的。但是第三个参数不同,因为每个都有不同的SalesOrderID值。

现在当我运行上面的代码时,我从DMV
SELECT语句得到下面的输出(注意,输出被重新格式化了,以便它更易读):

EXEC sp_configure ‘optimize for ad hoc workloads’, 1;
RECONFIGURE;
GO
USE Northwind2;
DBCC FREEPROCCACHE;
GO
SELECT * FROM Orders WHERE CustomerID = ‘HANAR’;
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
        CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype LIKE ‘Compiled Plan%’
        AND [text] NOT LIKE ‘%dm_exec_cached_plans%’;
GO
SELECT * FROM Orders WHERE CustomerID = ‘HANAR’;
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
        CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype LIKE ‘Compiled Plan%’
        AND [text] NOT LIKE ‘%dm_exec_cached_plans%’;
GO

cntsizeplan_text

249152(@SalesOrderIDINT)SELECTSUM(LineTotal)ASLineTotal
FROMAdventureWorks.Sales.SalesOrderHeaderH
JOINAdventureWorks.Sales.SalesOrderDetailDOND.SalesOrderID=H.SalesOrderID
WHEREH.SalesOrderID=@SalesOrderID从这个输出,你可以看出,我有一个参数化缓存计划,它被执行了两次,为每个EXEC语句各执行了一次。

使用参数化查询来节省资源和优化性能

在语句可以被执行之前,每个T-SQL语句都需要被评估,而且需要建立一个执行计划。创建执行计划会占用宝贵的CPU资源。当执行计划被创建后,它使用内存空间将它存储在过程缓存中。降低CPU和内存使用的一个方法是利用参数化查询。尽管数据库可以被设置为对所有查询FORCE参数化,但是这不总是最好的选择。通过了解你的哪些T-SQL语句可以被参数化然后使用sp_executesql存储过程,你可以帮助SQL
Server节省资源并优化你的查询的性能。

图片 2
图片 3
如果关闭选项,执行:

EXEC sp_configure ‘optimize for ad hoc workloads’, 1;
RECONFIGURE;
GO

■简单参数化(Simple
Parameterization)