笔记61-徐 编译与重编译 P372

笔记61-徐  编译与重编译 P372  

--编译与重编译 P372 2012-12-28

--当SQL收到任何一个指令，包括查询（query）、批处理（batch）、存储过程、触发器（trigger）
--预编译指令（prepared statement）和动态SQL语句（dynamic SQL Statement）
--要完成语法解释、语句解释，然后再进行“编译（compile）”，生成能够运行的“执行计划（execution plan）”
--在编译的过程中，SQL会根据所涉及的对象的架构（schema）、统计信息以及指令的具体内容，估算
--可能的执行计划，以及他们的成本（cost），最后选择一个SQL认为成本最低的执行计划

--执行计划生成之后，SQL通常会把他们缓存在内存里，术语统称他们叫“plan cache”
--以后同样的语句执行，SQL就可以使用同样的执行计划，而无须再做一次编译。这种
--行为叫“重用（reuse）”但是有时候，哪怕是一模一样的语句，SQL下次执行还是要
--再做一次编译。这种行为叫“重编译（recompile）”。执行计划的编译和重编译都是要
--消耗资源的


--执行计划的好坏当然决定了语句最终的执行速度。对于同样的一条语句，使用好的执行计划可能
--会比差的要快几百倍，甚至上千倍。所以从这一个角度来讲，每运行一条语句，都把
--他先编译一遍当然是最好的。他能够保证使用的执行计划是SQL能找到的最优的。但是SQL
--每秒钟可能会运行成百上千的指令。如果每个都编译一遍，是资源的一种浪费。所以
--SQL在这里也试图寻找一个平衡点，使用有限的compile/recompile，得到最好的整体性能


--运行下面的指令，就能够看到SQL当前缓存的执行计划有哪些（请别在生产服务器上直接运行
--因为上面往往有庞大的缓存）
SELECT * FROM sys.[syscacheobjects]

--对不同的指令调用方法，SQL做执行计划缓存和重用机制也有所不同。下面介绍最常见的几种
--以及怎样通过跟踪sys.[syscacheobjects]视图和SQL Trace里的事件来分析是否有编译或者
--执行计划重用


--1、Adhoc语句
--一组包含select,insert,update,delete的批处理指令。对这样的指令，只有前后完全一致
--包括字母的大小写，空格，回车换行都一致，SQL才认为是两条一样的语句，才能够重用
--执行计划。所以这个要求还是挺高的

--例如，运行下面的指令，连续两次查询sys.[syscacheobjects]视图
DBCC freeproccache
GO
SELECT * FROM sys.[syscacheobjects]
go
SELECT * FROM [sys].[syscacheobjects]
go


--在第二个结果集里会看到两个adhoc的执行计划。他们的差别就在于一个有回车
--一个没有回车


--但是如果语句是一样的，那么第二句话就可能会重用前面的执行计划。所以adhoc
--语句并不一定没有执行计划的重用

--例如下面，把同样的一句话跑两遍，会发现只有一个执行计划，而且他被使用过两次
--（字段usecount=2）

DBCC freeproccache
GO
SELECT  * FROM sys.[syscacheobjects]
go
SELECT * FROM sys.[syscacheobjects]
go


--在SQL Trace里，也能看到第一次执行有过编译事件（SP:CacheInsert），第二次执行就
--重用了先前的执行计划（SP:CacheHit）


--2、用EXEC()的方式运行动态SQL语句
--有些应用程序为了开发上的灵活度，在程序运行过程中，动态地拼接一个语句字符串，然后
--用exec（）的方式执行。这种调用方法被称为“动态SQL dynamic SQL”。他的好处是灵活
--可以根据客户的选择，动态生成指令，而不仅限于预先定义的那几种。但是他的缺点也是
--太灵活，客户发过来的语句每次都不一样，或者语句主体部分是一样的，但是参数不一样
--SQL都要做编译。这点和adhoc语句是一样的


--例如下面这个例子，同一句话运行了三遍。第一遍和第二遍用的是一样的参数值，所以第二次
--没有再做编译（有事件SP:CacheHit）。但是第三次的参数值不同，他就再编译了一遍
--（SP:CacheMiss和SP:CacheInsert）


DBCC freeproccache
GO
DECLARE @dbid VARCHAR(10)
SET @dbid='9'
EXEC('select * from sys.syscacheobjects where dbid<='+@dbid)
SET @dbid='9'
EXEC('select * from sys.syscacheobjects where dbid<='+@dbid)
SET @dbid='6'
EXEC('select * from sys.syscacheobjects where dbid<='+@dbid)
GO



--第二次运行和第三次运行SQL Trace里的事件不同

--在应用里经常会有同样的语句被不同的客户以不同的参数调用到。如果一定要参数一样才能重用
--执行计划，那执行计划重用的机会就很低了。所以adhoc语句和dynamic SQL不是最佳的调用
--方法。在SQL里，还有其他一些方法，可以对结构相同但是参数值不同的指令做执行计划重用


--3、自动参数化的查询（auto-parameterized query）
--对于一些比较简单的查询，SQL2005自己就可以做自动参数化，把语句里的参数用一个变量代替
--以提高执行计划的可重用性。
--例如，运行下面这几条语句：

USE [AdventureWorks]
GO
DBCC freeproccache
go
SELECT [ProductID],[SalesOrderID]
FROM sales.[SalesOrderDetail]
WHERE [ProductID]>1000
GO

SELECT [ProductID],[SalesOrderID]
FROM sales.[SalesOrderDetail]
WHERE [ProductID]>2000
GO

SELECT * FROM sys.[syscacheobjects]
go


--两条查询语句一模一样，只是参数的值不同。当察看缓存里的执行计划时，就能发现
--SQL不但缓存了两条语句自己的执行计划，还缓存了一个参数化了的执行计划

--如果再运行一句类似的语句：
SELECT [ProductID],[SalesOrderID]
FROM sales.[SalesOrderDetail]
WHERE [ProductID]>3000
GO


--在SQL Trace里，可以看到这句话在开始之前，先有一个SP:CacheHit的事件。语句在缓存
--中找到了一个参数化后的执行计划。然后他根据这个执行计划，做出了自己的adhoc执行计划
--（SP:CacheInsert）。这也能节省一部分编译时间



--4、用sp_executesql的方式调用的指令
--查询自动参数化在很多种条件下是不支持的，而且他还是要为每句查询生成一个adhoc的执行计划
--所以他并不是减少编译的最优手段。改用sp_executesql能够更有效地增加执行计划重用。用
--下面这个例子，两个查询结构一样，仅参数不同

USE [AdventureWorks]
GO
DBCC freeproccache
GO
EXEC [sys].[sp_executesql] N'SELECT p.[ProductID],p.[Name],p.[ProductNumber] FROM [Production].[Product] p
INNER JOIN [Production].[ProductDescription] pd
ON p.[ProductID]=pd.[ProductDescriptionID]
WHERE p.[ProductID]=@a' ,N'@a int' ,170
GO


EXEC [sys].[sp_executesql] N'SELECT p.[ProductID],p.[Name],p.[ProductNumber] FROM [Production].[Product] p
INNER JOIN [Production].[ProductDescription] pd
ON p.[ProductID]=pd.[ProductDescriptionID]
WHERE p.[ProductID]=@a' ,N'@a int' ,1201
GO

SELECT * FROM sys.[syscacheobjects]
GO

--在执行计划缓存里，只会看到一份执行计划（prepared）,不再有adhoc的那一份

--用SQL Trace，可以看到第一次执行时有SP:CacheInsert事件，第二次就只有SP:CacheHit事件了
--说明第二次语句执行已经完全不需要做编译。从而提高了执行计划重用性


--5、存储过程（stored procedure）
--对用户经常要调用的指令，把他们做成存储过程，既方便管理，规范脚本，又能够大大提高
--执行计划重用率，是值得推荐的一种做法。从SQL的角度，最好绝大多数指令都能够以存储
--过程的方式调用，尽量少使用Dynamic SQL的方式

--例如把前面的那个查询做成一个存储过程，然后用EXEC加参数的方式调用。可以清楚地看见
--第二次调用重用了第一次执行计划生成的执行计划






-------------------------重编译----------------------------------------------------------
--但是有些时候，SQL为了确保返回正确的值，或者有性能上的顾虑，有意不重用缓存在内存里
--的执行计划，而现场编译一份。这种行为，被称为重编译（recompile）。下面是比较常见
--的会发生重编译的情形：


--1、当指令或者批处理所涉及的任何一个对象（表格或者视图）发生了架构（schema）变化
--例如，在表或者视图上添加或删除了一个字段，添加或者删除了一个索引，在表上添加或者
--删除了一个约束条件（constraints）等。定义发生了变化，原来的执行计划就不一定正确
--当然要重编译



--2、运行过sp_recompile
--当用户在某个存储过程或者触发器上运行过sp_recompile后，下一次运行他们就会发生
--一次重编译。如果用户在某个表或者视图上运行了sp_recompile，那么所有引用到
--这张表（或者视图）的存储过程在下一次运行前，都要做重编译



--3、有些动作会清除内存里的所有执行计划，迫使大家都要做重编译
--例如，下列动作会清除整个SQL服务器缓存的所有执行计划：
--Detach一个数据库
--对数据库做了升级，在新的服务器上，会发生执行计划清空
--运行了DBCC freeproccache
--运行了reconfigure语句
--运行了alter database..collate语句修改了某个数据库的字符集（collation）


--下列动作会清除SQL服务器缓存的某个数据库的执行计划：
--DBCC FLUSHPROCINDB
--* DBCC FLUSHPROCINDB (db_id)
--清除SQL Server 2000服务器内存中的某个数据库的存储过程缓存内容
DECLARE @a INT
SELECT @a=DB_ID('gposdb')
DBCC flushprocindb(@a)
--ALTER DATABASE ...MODIFY NAME语句
--ALTER DATABASE ...SET ONLINE语句
--ALTER DATABASE...SET OFFLINE语句
--ALTER DATABASE...SET EMERGENCY语句
--DROP DATABASE 语句
--当一个数据库自动关闭时
--DBCC CHECKDB语句结束时



--4、当下面这些SET 开关值变化后，先前的那些执行计划都不能重用
--ansi_null_dflt_off,ansi_null_dflt_on,ansi_nulls,_ansi_padding
--ansi_warnings,arithabort,concat_null_yields_null,datefirst,dateformat,
--forceplan,language,no_browsetable,numeric_roundabort,quoted_identifier

--这是因为这些SET开关会影响语句的执行的行为，甚至带来不同的结果。他们发生变化了
--SQL就要根据新的设置重做执行计划


--5、当表格或者视图上的统计信息发生变化后
--当统计信息被手动更新后，或者SQL发现某个统计信息需要自动更新时，SQL会对所涉及
--的语句都做重编译




--需要说明的是，在SQL里，执行计划重用并不一定是一件好事，而编译/重编译也不一定
--是一件坏事。计划重用可以帮助SQL节省编译时间，对降低CPU使用率和减少阻塞都有好处
--但是缺点是每次重用的计划并不一定是最合适的计划。后面的参数嗅探parameter sniffing
--就是典型的计划重用带来的负效应。编译和重编译当然能给当前运行的语句带来尽可能准确
--执行计划，但是对于经常运行的语句，尤其是一些执行速度比较快的语句，可能其编译时间
--占最后总时间的相当大比例。这对资源来讲是一个很大的浪费


--一般来说，SQL能够很好地在编译与重编译之间做平衡，大部分情况下没什么问题。如果发现
--系统因为编译和重编译太多，或者重用了不准确的计划，导致了性能问题，那就要根据具体
--情况做调整。在SQL里，能对计划重用和编译/重编译产生影响的功能主要有：


--1、使用存储过程，或者[sys].[sp_executesql]的方式调用会被重复使用的语句，而不要直接
--用adhoc语句或者dynamic SQL

--2、在语句里引用对象（表，视图，存储过程等），要带上他的schema名字（full qualified object name）
--而不光是对象自己的名字

--例如：对于一个对象名，[SalesOrderDetail]，dbo调用他，调用到的是[dbo].[SalesOrderDetail]
--而用户Sales调用他，调用到的可能是[Sales].[SalesOrderDetail]。所以对于同一句语句，SQL
--可能会更倾向于保存多份执行计划。使用对象全名，能够更好地“引导”SQL重用执行计划

--3、将数据库parameterization属性设置为forced
--SQL2005以后，可以在单个数据库上开启强制参数化。也就是说，对于在这个数据库下运行的
--大部分语句，SQL都会先参数化，再运行。如果应用经常用adhoc方式调用一样的语句，强制
--参数化可能会有所帮助

--4、统计信息更新
--统计信息手工或者自动更新后，对和他有关的执行计划都不再能重用，而会产生重编译
--这样能够保证下次运行的语句能够根据数据变化使用新的执行计划

--5、create procedure...with recompile选项和exec ....with recompile选项
--在创建或者调用存储过程的时候使用“with recompile”，会强制SQL在调用这个存储过程
--的时候，永远都先编译，再运行。哪怕有合适的缓存执行计划，也不会重用

--这个选项一般用在DBA已经确定了对某个特定的存储过程，或者是特定语句执行计划重用
--会带来负面影响时（例如parameter sniffing后果比较严重）


--6、用户调用了sp_recompile
USE [pratice]
GO
EXEC [sys].[sp_recompile] @objname = N'dbo.bulkinserttest' -- nvarchar(776)

--sp_recompile也会产生相关对象的重编译。当DBA怀疑某个对象上现在缓存的计划
--不合适，已经造成性能问题时，可以运行sp_recompile，让SQL重新编译

--7、用户在调用语句的时候，使用了“keep plan”或者“keepfixed plan”这样的查询提示（Hint）
--keep plan放宽了对临时表的重编译阀值，使得SQL像对普通表一样对待临时表，不会在上面
--做额外的重编译。用户可以像下面这样使用他：
SELECT b.col4,SUM(a.col1)
FROM [dbo].permtable a INNER JOIN #temptable b ON a.col1=b.col2
WHERE b.col3<100
GROUP BY b.col4
OPTION(KEEP PLAN)


--keepfixed plan强制查询优化器不因统计信息的更改而重新编译查询。只有在基础表的结构
--发生变化后，或者有人运行过sp_recompile以后，才会发生重编译。用户可以像下面这样
--使用他
USE [AdventureWorks]
GO
SELECT c.[TerritoryID],COUNT(*) AS number,c.salespersonid
FROM sales.[Store] s INNER JOIN sales.[Customer] c
ON s.[CustomerID]=c.[CustomerID]
WHERE s.[Name] LIKE '%Bike%' AND [c].salespersonid>285
GROUP BY c.[TerritoryID],c.salespersonid
ORDER BY number DESC
OPTION(KEEPFIXED PLAN)



--总之，DBA可以根据实际情况，选择某一种或某几种方法来影响SQL的编译/重编译行为
--在SQL里，也提供了很多跟踪和分析编译与执行计划重用行为的方法。有些前面已经使用
--过了。现在在这里再总结一下


--1、系统管理视图sys.syscacheobjects
--从sys.syscacheobjects里可以看到SQL缓存的所有执行计划，以及他们的相关信息

--一些比较常用的字段有：
--cacheobjtype:缓存中的对象类型
--compiled plan：编译计划，也就是我们通常讲的执行计划
--executable plan:可执行计划
--parse tree:分析树
--cursor:游标
--extended stored procedure:扩展存储过程

--objtype:对象的类型
--可以缓存执行计划的对象很多，包括有：

--proc：存储过程
--prepared:预定义语句
--adhoc:即席查询（由sqlcmd或osql实用工具而不是远程过程调用作为语言事件提交的TSQL）
--repproc:复制筛选过程
--trigger:触发器
--view:视图
--default:默认值
--usrtab:用户表
--systab:系统表
--check:check约束
--rule:规则
--setopts:影响编译计划的SET选项设置
--refcounts:引用该缓存对象的其他缓存对象数，计数1为基数。每被重新引用一次，加1
--usecounts:自开始以来使用该缓存对象的次数。refcounts和usecounts这两个值越大，
--说明缓存重用得越多，这个缓存越有用

--pagesused:缓存对象占用的页数。也就是缓存消耗的内存数目

--唯一再次强调的是，在一个生产环境里，SQL缓存的执行计划可能会很多。要去查询这张表
--最好带上一些过滤条件，或者加上top 1000之类的子句，控制返回集的大小


--2、清除执行计划缓存的语句
--如果DBA有充分的理由怀疑当前的问题和内存里缓存的执行计划有关，可以用下面两句话之一
--清除执行计划，顺便也释放内存（所以在和内存相关的问题上有时也有用）
DBCC freeproccache
DECLARE @a INT
SELECT @a=DB_ID('gposdb')
DBCC flushprocindb(@a)

--3、SQL Trace里的一些和编译有关的事件
--在SQLTrace里有一些事件可用很有效地跟踪编译和重编译行为
--SQLTrace里的一些和编译有关的事件
--cursors -cursorrecompile:当游标所基于的对象发生架构变化，导致的TSQL游标做的重编译
--performance -auto stats:发生自动创建或者更新统计信息的事件

--stored procedures 下面有好几个很有用的事件：
--SP:CacheHit:说明当前语句在缓存里找到了一个可用的执行计划
--SP:CacheInsert:当前有一个新执行计划被插入到缓存里
--SP:CacheMiss:说明当前语句在缓存里找不到一个可用的执行计划
--SP:CacheRemove:有执行计划被从缓存里移除。内存有压力的时候会发生这样的行为
--SP:Recompile:一个存储过程发生了重编译。这个事件有一个数据字段叫EventSubClass
--记录了重编译发生的原因。如果DBA要跟踪为什麽一条指令老是重编译，跟踪这个事件
--的这个字段很有用，他一共有11种可能的原因。所以在处理重编译问题时，这个事件
--很有帮助


--1=架构已更改
--2=统计已更改
--3=重新编译DNR
--4=所设置的选项已更改
--5=临时表已更改
--6=远程行集已更改
--7=浏览Perm的方式已更改
--8=查询通知环境已更改
--9=MPI视图已更改
--10=光标选项已更改
--11=使用重编译选项


--而SQL2000中，只要批处理中的任何一句语句导致重新编译，就会重新编译整个批处理，
--无论此批处理是通过存储过程、触发器、即席批查询，还是通过预定义的语句进行提交
--在SQL2005和更高版本中，只会重新编译批处理中导致重新编译的语句。从这一点来看
--SQL2005比SQL2000更节约一点。因为这种变化，在SQL2005以后，要改用“TSQL-SQL:StmtRecompile”
--这个事件来跟踪重编译，因为现在的重编译都发生在语句一级


--4、一些性能监视器计数器
--相关计数器
--性能对象                                   计数器
--SQLSERVER:BUFFER MANAGER:    buffer cache hit ratio,lazy writes/sec ,procedure cache pages,total pages
--SQLSERVER：Cache Manager:    cache hit ratio,cache object counts,cache pages ,cache use counts/sec
--SQLSERVER:MEMORY MANAGER:    sql cache memory(kb)
--SQLSERVER:SQL STATISTICS:    auto-param attmpts/sec,batch request/sec,failed auto-params/sec,safe auto
--                             -param/sec, sql compilations/sec,sql re-compilations/sec,unsafe auto-params/sec