怀疑一切,但不否定一切

posts(21) comments(78) trackbacks(4)
  • 博客园
  • 联系
  • 订阅 订阅
  • 管理

与我联系

  • 发短消息

搜索

 

常用链接

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

留言簿

  • 给我留言
  • 查看公开留言
  • 查看私人留言

随笔分类

  •  iBatis(1)
  •  Notification Service(1)
  •  SQL Server 性能优化(12)
  •  SQLServer2005小结(2)

随笔档案

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

最新评论

  • 1. re: 使用ad hoc的利弊
  • @Cheney Shue 多谢,那我不太明白sys.dm_exec_cached_plans里关于ad hoc的说明到是什么意思了?如果在.NET程序中去调用那些SQL语句,所得到的结果和在SSMS中...
  • --凉面
  • 2. re: 使用ad hoc的利弊
  • ad-hoc确实不是这样的。下面是adhoc的定义,从wiki上复制过来的:Ad hoc querying is a term in information science.Many applicat...
  • --Cheney Shue
  • 3. re: 使用ad hoc的利弊
  • 即席查询是能够进行参数嗅探的,而一般的卸载程序内的sql语句也不会变动大小写和空格啥的,都能被数据库嗅探到,而成为一个编译计划的。 最近发现一个写sql语句不太爽的地方就是不方便管理,在数据库内可以...
  • --PerfectDesign
  • 4. re: 使用ad hoc的利弊
  • 从SSMS命令窗口发送的SQL语句就是ad hoc?你用过ad hoc工具吗?
    文不对题,明明说的是使用参数绑定避免硬解析,跟ad hoc没关系。
  • --唐开礼
  • 5. re: 磁盘I/O的性能评估方法
  • 请教下I/O问题
    MSN: jxtu@msn.com
  • --_chill

阅读排行榜

  • 1. 数据库分页操作(2206)
  • 2. 为什么尽量避免使用触发器?(2168)
  • 3. 生成器工作内幕分析(1811)
  • 4. 为SQLSERVER打好地基-硬盘碎片和索引碎片(1720)
  • 5. 结果集大小如何影响并发性(1634)

评论排行榜

  • 1. 为什么尽量避免使用触发器?(17)
  • 2. 为SQLSERVER打好地基-硬盘碎片和索引碎片(16)
  • 3. 结果集大小如何影响并发性(13)
  • 4. 数据库分页操作(10)
  • 5. 如何编写高效的存储过程(8)

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 凉面 阅读(2168) 评论(17)  编辑 收藏 所属分类: 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 [未注册用户]
社区  新闻  新用户注册  刷新评论列表  

标题  
姓名  
主页
Email (只有博主才能看到) 
验证码 *  看不清,换一张 [登录][注册]
内容(请不要发表任何与政治相关的内容)  
  登录  使用高级评论  新用户注册  返回页首  恢复上次提交      
该文被作者在 2008-07-11 19:18 编辑过

相关文章:
使用Visual Studio2005入门.Net2.0系列视频教程
SQL 2000中的触发器使用
我们为什么使用ORM?
触发器
emule的使用感悟
ASP.NET AJAX入门系列(2):使用ScriptManager控件

相关链接:

所属分类的其他文章:
使用ad hoc的利弊
数据库分页操作
为SQLSERVER打好地基-硬盘碎片和索引碎片
磁盘I/O的性能评估方法
为什么尽量避免使用触发器?
结果集大小如何影响并发性
控制表连接及排序操作
过程中是否该显示删除临时表
如何正确使用事件探察器解决性能问题
如何有效利用索引编写高效过程

最新IT新闻:
淘宝网合并阿里妈妈 专家称阿里巴巴或有新战略
微软研究院发布 AutoCollage - 整理并融合照片
2008年9月5日科技博客精选
SNS网站风靡影响工作效率 公司下令封杀
《孢子》正式发布
博客园新闻频道  博客园首页  社区
 




 
Powered by:
博客园
Copyright © 凉面