xml地图|网站地图|网站标签 [设为首页] [加入收藏]
为什麽我们一般会在自增列或交易时间列上建立
分类:数据库

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能够很好地在编译与重编译之间做平衡,大部分情况下没什么问题的。

本文的意见纯属我自己的个人意见,并不一定适合您的系统,如果交易时间的选择性不是太高的话,那么可能在交易时间或自增列上建立聚集索引就不是太合适了

 

因为这些系统一般是写多读少,所以我们会选择在自增列或交易时间列上建立聚集索引

最近遇到一起关于"I/O is frozen on database xxx. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup."的案例。


 

因为第一张表是以交易时间为聚集索引列的,所以无论交易编号是多少,记录都会插入到表的最后,因为后来的记录的交易时间肯定比前面的记录的交易时间大

What does these messages mean? Let me explain this with an example. Suppose ntbackup.exe is configured to take the backup of D drive. This drive has some data files related to few databases on SQL Server. Since the data files are in use by SQL Server, if these files are copied as it is the files in the backup will be inconsistent. To ensure that the database files are consistent in the drive backup, this application internally issues a BACKUP DATABASE [databasename] WITH SNAPSHOT command against the database. When this command is issued, the I/O for that database is frozen and the backup application is informed to proceed with its operation. Until the BACKUP WITH SNAPSHOT command is complete, the I/O for the database is frozen and the I/O is resumed once it completes. The corresponding messages are logged in the SQL Server Error Log.

每天的交易数据会不停的插入到数据库,但是读取数据就没有数据插入那么频繁

当任何备份服务利用SQL Server虚拟设备接口(VDI)尝试备份数据库(使用with snapshot时)或数据库文件所在的磁盘时,这个消息就会记录在错误日志(Error Log)里。 Micorsoft Backup(ntbackup.exe),卷影复制(Volume Shadow Copy VSS), 数据保护管理器(Data Protection Manager DPM)和第三方工具,例如赛门铁克Symantec 业务连续性卷(Business Continuance Volume)(BCV),这些都是会导致这类消息记录到SQL Server错误日志的应用程序。

例如:tranid:2,transtime:2014-1-26 31:22.180插入到表中的时候后面的记录都需要移动,而tranid:978,transtime:2014-01-26 00:29:10.830

我们先说第二张表

出现问题的时候,我去执行一个非常简单的SQL语句,执行时间非常长,检查没有阻塞。正常情况下,应该是几秒就OK。后面检查错误日志,发现有大量这类消息.而这个点,我们没有备份数据库的作业。后面搜索,了解了一下这个消息出现的原因:

 

 


 


案例Frozen messages while taking NT Backup for SQL databases

(除非人为修改系统时间造成当前时间比数据库中的某些记录的交易时间要早)

This message is logged in the Error Log whenever any backup service making use of SQL Server Virtual Device Interface (VDI) tries to backup the database (with snapshot)/drive on which the database files reside. Microsoft Backup (ntbackup.exe), Volume Shadow Copy (VSS), Data Protection Manager (DPM) and third party tools like Symantec Business Continuance Volume (BCV) are some of the application which cause this message to logged in the SQL Server Error Log.

 

 

测试

图片 1

这个移动时间是有开销的,而且每次偶数交易编号插入到表中,每插入一次就移动一次,而当前面的记录插入到表中的时候移动的记录数就越多

另外关于Database Snapshots (SQL Server)它也是有一些限制和性能开销的。如下截图所示:

总结

How It Works: SQL Server – VDI (VSS) Backup Resources

图片 2

 

我们看一下时间

 

1 SELECT TOP 10 * FROM [dbo].[transtable] ORDER BY [tranid]
2 SELECT TOP 10 * FROM [dbo].[transtable2] ORDER BY [tranid] 

1 SELECT TOP 10 * FROM [dbo].[transtable] ORDER BY [tranid]
2 SELECT TOP 10 * FROM [dbo].[transtable2] ORDER BY [tranid] 

 

验证一下偶数的交易编号是否已经插入到两个表中

图片 3

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:为什麽我们一般会在自增列或交易时间列上建立

上一篇:没有了 下一篇:没有了
猜你喜欢
热门排行
精彩图文