MERGE语句也支持最小化日志
MERGE语句也支持最小化日志
Minimal logging and MERGE statement
MERGE语句也支持最小化日志
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2010/06/03/minimal-logging-and-merge-statement.aspx
n my earlier blog http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/10/24/new-update-on-minimal-logging-for-sql-server-2008.aspx, we discussued how we can get minimal logging using TF-610 when using TSQL INSERT statement. Interestingly, you can get minimal logging when using MERGE statement as well. Here is a simple example to illustrate it.
use bulktest go alter database bulktest set recovery SIMPLE -- enable the trace flag 610 dbcc traceon (610, -1) -- create the staging table create table t_staging (c1 int, c2 int, c3 char (100), c4 char(1000)) go -- load 10000 rows into the staging table declare @i int select @i = 0 while (@i < 10000) begin insert into t_staging values (@i, @i+10000, 'indexkey', 'hello') select @i= @i + 1 end -- create the target table with clustered index DROP TABLE t_target GO create table t_target (c1 int, c2 int, c3 char(100), c4 char(1000)) go create clustered index ci on t_target(c1) go -- clean up the log while @@trancount > 0 rollback checkpoint -- execute a merge statement under a transaction BEGIN TRAN MERGE INTO t_target AS Target using t_staging as Source ON Target.c1 = Source.c1 when matched then update set c4 = source.c4 when not matched by target then insert (c1, c2, c3, c4) values (c1, c2, c3, c4) ; -- Now look at the top 20 log records select top 20 operation,context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName from fn_dblog(null, null) where allocunitname='dbo.t_target.ci' order by [Log Record Length] Desc
-- here is the output
You can see that after the first data page worth of rows, the rest of the inserts are minimally logged. The same can be done if the target table was a HEAP but in this case, you will need to use TABLOCK hint as shown here
MERGE INTO t_target with (TABLOCK) AS Target using t_staging as Source ON Target.c1 = Source.c1 when matched then update set c4 = source.c4 when not matched by target then insert (c1, c2, c3, c4) values (c1, c2, c3, c4) ;
需要加tablock提示和打开TF-610
这里无论表是否为空,新分配的页面都会最小化日志
This will work even when you have non-empty target table when the inserted rows go to newly allocated pages.
Thanks
Sunil Agarwal
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
2012-09-28 合理配置SQLSERVER内存