SQLSERVER编译与重编译

编译的含义


当SQLSERVER收到任何一个指令,包括查询(query)、批处理(batch)、存储过程、触发器(trigger)

、预编译指令(prepared statement)和动态SQL语句(dynamic SQL
Statement)要完成语法解释、语句解释,

然后再进行“编译(compile)”,生成能够运行的“执行计划(execution
plan)”。在编译的过程中,

SQLSERVER会根据所涉及的对象的架构(schema)、统计信息以及指令的具体内容,估算可能的执行计划,

以及他们的成本(cost),最后选择一个SQLSERVER认为成本最低的执行计划来执行。执行计划生成之后,

SQLSERVER通常会把他们缓存在内存里,术语统称他们叫“plan
cache”以后同样的语句执行,SQLSERVER就可以使用同样的执行计划,而无须再做一次编译。

这种行为叫“重用(reuse)或者叫重用执行计划”。但是有时候,哪怕是一模一样的语句,SQL下次执行还是要再做一次编译。

这种行为叫“重编译(recompile)”。执行计划的编译和重编译都是要消耗资源的。

如果执行计划能够重用,那么SQLSERVER就不需要再执行上面的过程,加快执行指令的速度,很多语句调优的文章里提到数据库重用执行计划就是指这个意思

 

 

执行计划重用的利弊


执行计划的好坏当然决定了语句最终的执行速度。对于同样的一条语句,使用好的执行计划可能会比差的要快几百倍,甚至上千倍。

所以从这一个角度来讲,每运行一条语句,都把他先编译一遍当然是最好的。他能够保证使用的执行计划是SQLSERVER能找到的最优的。

但是SQLSERVER每秒钟可能会运行成百上千的指令。如果每个都编译一遍,是资源的一种浪费。所以SQLSERVER在这里也试图寻找一个平衡点,

使用有限的compile/recompile,得到最好的整体性能

 

运行下面的指令,就能够看到SQLSERVER当前缓存的执行计划有哪些(请别在生产服务器上直接运行因为上面往往有庞大的缓存)

1 SELECT * FROM sys.[syscacheobjects]

 

 

重编译的发生场景


但是有些时候,SQLSERVER为了确保返回正确的值,或者有性能上的顾虑,有意不重用缓存在内存里的执行计划,而现场编译一份。

这种行为,被称为重编译(recompile)。下面是比较常见的会发生重编译的情形:

 

1、当指令或者批处理所涉及的任何一个对象(表格或者视图)发生了架构(schema)变化

例如,在表或者视图上添加或删除了一个字段,添加或者删除了一个索引,在表上添加或者删除了一个约束条件(constraints)等。

定义发生了变化,原来的执行计划就不一定正确了,当然要重编译

 

2运行过sp_recompile

当用户在某个存储过程或者触发器上运行过sp_recompile后,下一次运行他们就会发生一次重编译。

如果用户在某个表或者视图上运行了sp_recompile,那么所有引用到这张表(或者视图)的存储过程在下一次运行前,都要做重编译

 

3、有些动作会清除内存里的所有执行计划,迫使大家都要做重编译

例如,下列动作会清除整个SQLSERVER服务器缓存的所有执行计划:

(1)Detach一个数据库

(2)对数据库做了升级,在新的服务器上,会发生执行计划清空

(3)运行了DBCC freeproccache

(4)运行了reconfigure语句

(5)运行了alter
database..collate语句修改了某个数据库的字符集(collation)

 

下列动作会清除SQLSERVER服务器缓存的某个数据库的执行计划:

DBCC FLUSHPROCINDB

清除SQL Server 2000服务器内存中的某个数据库的存储过程缓存内容

1 DECLARE @a INT
2 SELECT @a=DB_ID('gposdb')
3 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开关会影响语句的执行的行为,甚至带来不同的结果。他们发生变化了,SQLSERVER就要根据新的设置重做执行计划

 

5、当表格或者视图上的统计信息发生变化后

当统计信息被手动更新后,或者SQLSERVER发现某个统计信息需要自动更新时,SQLSERVER会对所涉及的语句都做重编译

 

需要说明的是,在SQLSERVER里,执行计划重用并不一定是一件好事,而编译/重编译也不一定是一件坏事。

计划重用可以帮助SQLSERVER节省编译时间,对降低CPU使用率和减少阻塞都有好处,但是缺点是每次重用的计划并不一定是最合适的计划。

参数嗅探parameter
sniffing就是典型的计划重用带来的负效应。编译和重编译当然能给当前运行的语句带来尽可能准确执行计划,

但是对于经常运行的语句,尤其是一些执行速度比较快的语句,可能其编译时间占最后总时间的相当大比例。这对资源来讲是一个很大的浪费 

一般来说,SQLSERVER能够很好地在编译与重编译之间做平衡,大部分情况下没什么问题的。

一、实验素材:

 

附加学生信息表(student)

事情起因:
  排查SQL Server上的死锁问题,一开始想到的就是扩展事件,
  第一种方案,开profile守株待兔吧,显得太low了,至于profile的变种trace吧,垂垂老矣,也一直没怎么用过。
  第二种方案是开启TRACE flag(DBCC TRACEON
(3605,1204,1222,-1))将死锁写入error log,也是个不错的选择。
  不过想到系统默认的扩展事件system_health已经捕获了死锁信息(sqlserver.xml_deadlock_report),
  就没必要再重新往error
log记一次了,理论上从system_health中就能拿到死锁信息,因此尝试从system_health的ring_buffer
target获取死锁信息。

澳门微尼斯人手机版 1

排查经过:
  从sysem_health的ring_buffer中目标中,并没有捕获到预期的曾经发生的死锁事件信息,
  在完全确认发生过死锁的情况下(并且是最近,或者是刚刚),为什么sysem_health“没有能够捕获到”?
  无奈之下单独开了一个捕获死锁事件的扩展事件Session(仅捕获sqlserver.xml_deadlock_report),target目标是存储在ring_buffer中
  在人为刻意地制造一个死锁之后,来检验sysem_health和自定义的捕获扩展事件捕获的死锁信息
  此时意想不到的情况发生了,自定义的扩展事件完整地捕获到了这个死锁信息,而system_health仍旧没有捕获到对应的死锁信息。
  什么情况?

二、实验要求:

   如下自定义扩展事件脚本

1、  查询student表中所有学生的信息

CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))
WITH
(MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=10 SECONDS,
MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

ALTER EVENT SESSION [Deadlock_Monitor] ON SERVER STATE = start;

select  * from  student

死锁捕获结果,sysem_health并没有捕获到预期的死锁事件,尽管他包括了sqlserver.xml_deadlock_report事件

2、  查询student表中“姓名”“所在班级”和“成绩”列内容

澳门微尼斯人手机版 2

select  姓名,所在班级,成绩 from student

SELECT  xed.value('@timestamp', 'datetime') AS Creation_Date ,
        CAST(REPLACE(REPLACE(REPLACE(CAST(xed.query('.') AS NVARCHAR(MAX)),'&lt;', '<'),'&gt;', '>'),'&amp;apos;','''') AS xml) AS Extend_Event
FROM    ( SELECT    CAST([target_data] AS XML) AS Target_Data
          FROM      sys.dm_xe_session_targets AS xt
                    INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
          WHERE     xs.name = N'system_health'
                    AND xt.target_name = N'ring_buffer'
        ) AS XML_Data
        CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]')
        AS XEventData ( xed )
ORDER BY Creation_Date DESC

select * from sys.dm_xe_session_targets

SELECT    CAST([target_data] AS XML) AS Target_Data
FROM      sys.dm_xe_session_targets AS xt
                    INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
WHERE     xs.name = N'system_health'
          --AND xt.target_name = N'ring_buffer'

-- Parse the XML to show wait details
SELECT event_table.*
    FROM (
        SELECT CAST(event_data AS XML) xml_event_data 
        FROM sys.fn_xe_file_target_read_file(N'path\Log\system_health*', NULL, NULL, NULL)
    ) AS event_table
    CROSS APPLY xml_event_data.nodes('//event') n (event_xml)
WHERE  event_xml.value('(./@name)', 'varchar(1000)') IN ('xml_deadlock_report')   

3、  查询student表中7班的学生姓名

 

select  姓名  from  student  where  所在班级=’7′

  由于sysem_health有两个输出的target,一个ring_buffer,一个是target_file,无奈下从event_file查询捕获的死锁信息,这里又是没问题的,正常捕获到了。

4、  查询student表中成绩为90—100分的学生所有信息

  澳门微尼斯人手机版 3

select  * from  student  where 成绩 between  90  and 100

  因此就可以说,系统默认自带的sysem_health扩展事件,捕获死锁本身是没有问题的,问题出在扩展事件的输出目标ring_buffer上。
  在不过滤所有的扩展事件情况下,从ring_buffer里面解析出来的数据还有个特点,其不包含最近一段时间的任何一种事件信息。
  也就是说,ring_buffer中解出来的事件信息,是当前时间前一段时间的事件信息,并不包含所有的事件信息,以及最近一段事件所有的事件信息。
  当然你可以说ring_buffer是先进先出的队列模型,那也应该留下新的事件,而不是解析不出来最新的事件信息。

5、  查询student表中成绩低于90分或者高于95分的学生所有信息

原因分析:
  参考了sqlskill上的一篇文章,这篇文章深入地解析了这个问题,
  简单说就是:
  ring_buffer并没有“丢失”事件信息,至于为什么解析不出来,要从ring_buffer解析方式开始,ring_buffer扩展事件从sys.dm_xe_session_targets
这个DMV中解析的,
  受到sys.dm_xe_session_targets
这个DMV的目标数据列target_data字段只能容纳大约4 MB的XML数据的限制。
  当ring_buffer捕获的事件(内存中的二进制数据)转换为XML格式大于(大约)4MB的情况下,超过4MB的其他的事件会被被“截断”,
  从sys.dm_xe_session_targets解析出来的XML文件优先输出更早的事件,所以我们预期下的最近发生的事件是无法看到的。
  因此,正如上文中遇到的情况一样:“丢失”部分事件信息,并且没有最近的事件信息。

select  * from  student
 where  成绩<90 or  成绩>95

总结:  
  以此来看,使用ring_buffer为扩展事件的target,潜在以下问题
  1,解析出来的结果并不可靠(完整),可能无法解析到最近的部分事件。
  2,以下译文中还会提到,ring_buffer作为target可能会撑爆内存的情况,所以要谨慎使用。
  3,同样下文会提到,SSMS的UI对ring_buffer中的事件支持的并不好,对于ring_buffer的target,UI也仅仅是show出来一个XML文件,必须要自己解析,而不像event_file中那样表格化展示(可读性)
  因此要尽量避免在扩展事件中使用ring_buffer target。

6、  查询student表中成绩为89分,90分的学生所有信息

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注