怀疑一切,但不否定一切

posts(36) comments(148) trackbacks(11)
  • 博客园
  • 订阅 订阅
  • 管理

搜索

 

常用链接

  • 我的参与
  • 最新评论
  • 我的标签

随笔档案

  • 2009年5月 (1)
  • 2009年3月 (1)
  • 2008年12月 (2)
  • 2008年11月 (2)
  • 2008年10月 (2)
  • 2008年9月 (3)
  • 2008年8月 (2)
  • 2008年7月 (1)
  • 2008年6月 (4)
  • 2008年5月 (2)
  • 2008年4月 (4)
  • 2008年3月 (3)
  • 2007年11月 (1)
  • 2007年4月 (1)

最新评论

  • 1. re: 调整引导扇区提高IO性能
  • Microsoft Windows [版本 5.2.3790] (C) 版权所有 1985-2003 Microsoft Corp. C:\Documents and Settings\Admini...
  • --深山老林
  • 2. re: 使用SQL2005强制计划解决遗留系统性能问题
  • 楼主是在哪,这么厉害啊!
  • --zp
  • 3. re: 非域环境下带自动故障转移数据库镜像的实现方法
  • 好文章,帮大忙了。
  • --深山老林
  • 4. re: 非域环境下带自动故障转移数据库镜像的实现方法
  • 不是在域环境中配置的,用的证书,找个日志有没有什么好的解决方法哦,我的见证服务还是不的行,还是报这个错
  • --xchit
  • 5. re: 非域环境下带自动故障转移数据库镜像的实现方法
  • @xchit 你是在域环境中配置吗?一般生产环境都在域中创建,确保域账号有相应的权限就可以了。 日志满了的原因可能是因为下面三个原因: 1、你没有定期对日志进行备份 2、镜像服务器挂起导致备份日志时不...
  • --凉面

阅读排行榜

  • 1. 数据库分页操作(3135)
  • 2. 为什么尽量避免使用触发器(2878)
  • 3. 减少海量数据库的存储空间(2145)
  • 4. 为SQLSERVER打好地基-硬盘碎片和索引碎片(2095)
  • 5. 生成器工作内幕分析(1873)

评论排行榜

  • 1. 减少海量数据库的存储空间(24)
  • 2. 为什么尽量避免使用触发器(22)
  • 3. 为SQLSERVER打好地基-硬盘碎片和索引碎片(16)
  • 4. 结果集大小如何影响并发性(13)
  • 5. 数据库分页操作(11)

View Post

为什么尽量避免使用触发器

   上次没有具体说明,宽宽一直在追问这个问题,现在补充如下:
     如果你是使用的SQL2000,这个问题会更加严重。触发器操作要作为外部事务的一部分,因此instered和deleted两个虚拟表都是写到事务日志中的。因为日志是顺序写入的,所以在把新旧记录写入日志时,会阻止其它事务写入。同时,读取日志时也会因为有其它写入时而被阻塞。这个无疑给并发操作带来很大影响。
     SQLSERVER在内部会把触发器作为一个存储过程来对待,除了不能输入参数等限制外。另一个值得考虑的问题是触发器的执行计划问题,它不随过程的重新编译而编译。上次演示过因为错误的缓存导致的错误的执行计划示例,不知道这个算不算作一个问题。
     现在SQL2005的新旧记录不再保存在日志中,而是使用新的行版本技术存储于tempdb中。这带来了一定程度上的并发优势,但是维护这些版本记录同样也需要额外的开销。同时,在这样的版本链列表中寻找记录也一样会有性能损耗。现在2005的update、delete、insert操作都带了output子句,可以使用它来替换触发器的操作。但是output有个限制是不能直接into到有约束的表,如果有约束也可以先把前后记录存入一个临时的地方再做进一步处理。 
    
下面的代码演示了使用output和trigger时,所观察到的情况,两者都会用到tempdb。但是output是在自己独有的空间中存储这些记录的,而trigger则是在一个公用的空间中存储就像是私家车库与公用停车场一样。因此output会更专职于处理某个请求的操作。具体代码如下:
--更新所有在London的供应商的产品体格为原价格的1.5倍
 USE Northwind;
 
GO
 
--创建价格变动历史记录表
 IF OBJECT_ID('Price_history','U') IS NOT NULL
     
DROP TABLE Price_history
 
GO
 
SELECT 1 AS ProductID,UnitPrice AS OldPrice,UnitPrice AS NewPrice,GETDATE() AS Date
 
INTO Price_history
 
FROM dbo.Products
 
WHERE 1=0
 
GO
 
IF OBJECT_ID('trg_Products_u','TR') IS NOT NULL
     
DROP TRIGGER trg_Products_u;
 
GO
 
--sys.dm_tran_version_store用于存储行版本记录所用,此记录在没有被引用的情况下在一分钟内会被清理线程清除
 select * from sys.dm_tran_version_store--确保此时没有版本记录存在
 --请确保Products表现在没有任何其它更新触发器存在
 --更新完成后发现没有记录相应的行版本,
--
如果你打开性能计数器跟踪SQLSERVER:TRANSACTIONS对象的free space in tempdb,你会发现它确实也用到了tempdb。
--
因为这个更新没有多少记录,看不出结果。你可以换一个Sales.SalesOrderDetail表来试一下。
 update p
 
set UnitPrice=UnitPrice*1.5
 output deleted.ProductID,deleted.UnitPrice,inserted.UnitPrice,
getdate() into price_history
 
from dbo.Products p
     
join dbo.Suppliers s
     
on p.SupplierID=s.SupplierID
 
where s.city=N'London'
 
select * from sys.dm_tran_version_store
 
 
GO
 
 
--现在我们创建一个更新触发器来完成此功能
 IF OBJECT_ID('trg_Products_u','TR') IS NOT NULL
     
DROP TRIGGER trg_Products_u;
 
GO
 
CREATE TRIGGER trg_Products_u ON dbo.Products FOR UPDATE
 
AS
 
--如果更新的不是UnitPrice或没有更新直接返回
 IF NOT UPDATE(UnitPrice) OR @@ROWCOUNT=0
     
RETURN;
 
ELSE
     
INSERT INTO price_history
     
SELECT i.ProductID,d.UnitPrice,i.UnitPrice,getdate()
     
FROM inserted i
         
join deleted d
         
on i.ProductID=d.ProductID
 
GO
 
--使用触发器时,完成更新查看版本记录中有6条记录
 --因为在'London'的供应商有三个产品,所以新旧记录加起来总共是6条记录
 
 
update p
 
set UnitPrice=UnitPrice*1.5
 
from dbo.Products p
     
join dbo.Suppliers s
     
on p.SupplierID=s.SupplierID
 
where s.city=N'London'
 
select * from sys.dm_tran_version_store
 
 
 
DBCC FREEPROCCACHE;--清除过程缓存以观察触发器的缓存计划
 GO
 
 
--创建显示重新编译的存储过程
 SET ANSI_NULLS ON
 
GO
 
SET QUOTED_IDENTIFIER ON
 
GO
 
IF OBJECT_ID('prc_UpdateProductPrice','P') IS NOT NULL
     
DROP PROC prc_UpdateProductPrice;
 
GO
 
CREATE PROCEDURE prc_UpdateProductPrice
 
WITH RECOMPILE
 
AS
 
BEGIN
     
SET NOCOUNT ON;
     
update p
     
set UnitPrice=UnitPrice*1.5
     
from dbo.Products p
         
join dbo.Suppliers s
         
on p.SupplierID=s.SupplierID
     
where s.city=N'London'
 
END
 
GO
 
EXEC prc_UpdateProductPrice
 
GO
 
--反复执行上述过程后,发现触发器的执行计划不会因为过程的重新编译而被重新编译
 --这可能会因为缓存的原因,造成优化器错误的选择了执行计划
 --不知道这个结果是喜是忧
 SELECT usecounts, cacheobjtype, objtype, text 
 
FROM sys.dm_exec_cached_plans 
 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
 
GO

posted on 2008-06-25 03:35 凉面 阅读(2878) 评论(22)  编辑 收藏 所属分类: SQL Server 性能优化

View Comments

#1楼   回复  引用  查看    
恩,只用了一回,就遇到了问题,所以以后就一直没有使用。
2008-06-25 06:37 | 金色海洋(jyk)      
#2楼   回复  引用  查看    
我也知道要慎用触发器,比起代码,我更希望看到这样的文字, 第一:什么什么,第二:什么什么,比较清晰的先说明一下。
2008-06-25 07:43 | BlackCat      
#3楼   回复  引用    
谢谢楼主,先收起来。
2008-06-25 08:21 | hhh[未注册用户]
#4楼   回复  引用  查看    
本人也是,但微软设计触发器肯定有它的理由的,但就是不知道我们可能从来不会用到触发器
2008-06-25 08:26 | chunfeng      
#5楼   回复  引用  查看    
也不喜欢用,关联的操作习惯写在存储过程里
用触发器总觉得不好管理
2008-06-25 09:05 | wingoo      
#6楼   回复  引用  查看    
@BlackCat
楼主已经写得很清楚了,特别是关于对事务日志的影响,讲得非常清楚。

另外,我的项目里面如果谁实在是想写触发器,必须维护一个触发器的列表,以及功能说明,以及可能会遇到的问题,以提醒其他开发人员注意,因为我已经不止一次在查找错误原因的时候,最后发现是触发器搞的鬼。
2008-06-25 09:22 | PerfectDesign      
#7楼   回复  引用    
不错的文章。
2008-06-25 10:06 | pengtyf[未注册用户]
#8楼   回复  引用    
严重同意!
一开始也经常用触发器,
现在不怎么用了。
2008-06-25 10:23 | Movie-123[未注册用户]
#9楼   回复  引用  查看    
了解
2008-06-25 11:26 | 念时      
#10楼   回复  引用  查看    
还是经常使用触发器哦。。。
2008-06-25 11:32 | 小龙3      
#11楼   回复  引用  查看    
触发器还会给数据库维护带来众多的麻烦。
用了就知道多痛苦。
2008-06-25 12:00 | 云の世界      
#12楼   回复  引用  查看    
一进来就先看到宽宽……这下出名了。
2008-06-25 13:06 | Carrod      
#13楼   回复  引用    
他已经很有名了。
2008-06-25 13:51 | 匿名人士276[未注册用户]
#14楼   回复  引用    
涛哥这里内容不少啊。好好学习一下……
楼上两位公共场合打情骂俏,不和谐啊。
2008-06-25 14:12 | 宽[未注册用户]
#15楼   回复  引用  查看    
已经很多年不用了,现终于了解
在简单才是美啊;

sql 最郁闷的一件事情就是不好调试尤其是触发器
最重要容易导致整个数据处理流程混乱、不清晰;

触发器个人认为,这东西就是一种补丁功能
在万不得已的情况下使用;
2008-06-25 20:42 | 曲滨*銘龘鶽      
#16楼   回复  引用  查看    
-_-!!!还可以这样啊.第一次听说.好文章
2008-06-26 19:50 | airwolf2026      
#17楼   回复  引用    
最主要的问题还是维护的困难。
至於并发的影响,应该不是最主要的,因为代码不管是放在sp还是哪,都是一样,这不是影咱并发的因素。
2008-07-09 12:38 | tempdb[未注册用户]
#18楼   回复  引用  查看    
"触发器操作要作为外部事务的一部分,因此instered和deleted两个虚拟表都是写到事务日志中的。"
楼主能给点提示说一下如何使用instered和deleted这两个表会导致写到事务日志呢?

难道会在instered和deleted这两个表中作插入删除产生日志?事实上instered和deleted这两个逻辑表是不能修改的,而对表的查询不产生任何日志。

我看到的sql2000通过在表上增加触发器,利用instead of insert 来插入数据,和直接插入数据产生的log是一样的啊!并不会多产生一个先插入逻辑表inserted的日志。

2008-09-06 11:10 | rO8eR70.nEt      
#19楼[楼主]   回复  引用  查看    
@rO8eR70.nEt
具体的细节请参考《SQLSERVER2000技术内幕》和《Inside SQL 2005存储引擎》这两本书里介绍的很详细。
2008-09-06 13:14 | 凉面      
#20楼   回复  引用  查看    
@凉面

《Inside SQL 2005存储引擎》有一本,怎么看也没看到这方面的内容!实在
是不知道你这句说的是什么意思呢。:)

如果你的意思是说SQl2000把新旧记录写到日志中,SQL2005不写到日志中的
话,那么这和采用不采用triger有什么关系呢?

sql2000中你更新一个表,直接更新和通过写触发器利用inserted,deleted两
个逻辑表产生的log是一样的啊?:(

如果说采用triger是会自动导致一个事务,那么如果为实现相同的目的,把相同
的代码移到triger之外就可以不采用事务了?
2008-09-06 15:07 | rO8eR70.nEt      
#21楼[楼主]   回复  引用  查看    
@rO8eR70.nEt
多谢关注,《Inside SQL 2005存储引擎》中要是没有的话应该是在,《Inside SQL 2005 Programing》那本有介绍。我觉得任何东西都有其长处,不过我觉得从维护及性能方面来讲,应该尽量少用的好一些!因为我的书已经还了,你可以看一下那两本书中关于其操作的具体细节!
2008-09-07 22:21 | 凉面      
#22楼   回复  引用  查看    
@凉面

喔,找到了,原文的意思是instered和deleted两个虚拟表是通过扫描事务日
志后构造得到的,这样在触发器中使用两个表可能存在读的瓶颈。无论是否使用
触发器,读不读这两个表,构造这两个表的信息都会写到日志中

我对楼主的这个instered和deleted两个虚拟表写到日志这句话有误解。
的确,触发器这种东西,和goto语句类似,少用为妙!
2008-09-09 10:47 | rO8eR70.nEt      
刷新评论 切换模板



发表评论

昵称: [登录] [注册]

主页:

邮箱:(仅博主可见)

验证码: 验证码 看不清,换一个

评论内容:

  登录  注册

[使用Ctrl+Enter键快速提交评论]

0 1229314

导航:网站首页 社区 新闻 博问 闪存 网摘 招聘 找找看 Google搜索



China-pub 计算机图书网上专卖店!6.5万品种 2-8折!
China-Pub 计算机绝版图书按需印刷服务

相关文章:

最新IT新闻:
19岁天才黑客发布首个iPhone 3GS破解软件
新浪邮箱大本营粉墨登场!Sina.cn开放注册
IE市场份额首次跌破60%
Google App Engine宕机6小时——云的安全在哪里?
微软新推社交网站Windows Live Planet

相关链接:
 
Powered by:
博客园
Copyright © 凉面