SQL Server中避免触发镜像SUSPEND的N种方法
作者:lijiao 发布时间:2024-01-29 07:01:03
背景:
我们在使用数据库的过程中,很多时候要追求性能,特别在处理大批量数据的时候更希望快速处理。那么对SQL SERVER而言,数据库实现大批量插入的优化方案,这里特别介绍通过大容量插入的一种方式。
基本原理:
简单恢复模式按最小方式记录大多数大容量操作,对于完整恢复模式下的数据库,大容量导入期间执行的所有行插入操作被完整地记录到事务日志中。如果数据导入量较大,会导致迅速填满事务日志。对于大容量导入操作,按最小方式记录比完整记录更有效,并减少了大容量导入操作填满日志空间的可能性,所以性能会得到极大的提升。
但是,大容量导入中按最小方式记录日志的前提条件需要满足:
1. 当前没有复制表
2. 指定了表锁定:
注意:锁定是 SQL Server 数据库引擎用来对多个用户同时访问同一数据块的操作进行同步。当事务修改某个数据块时,它将持有保护所做修改的锁,直到事务结束。指定大容量导入操作的表锁定后,该表将在大容量导入操作期间采取大容量更新 (BU) 锁定。大容量更新 (BU) 锁允许多个线程将数据并发地大容量导入到同一表中,同时阻止其他不进行大容量导入数据的进程访问该表。表锁定可以通过减少表的锁争用来提高大容量导入操作的性能。
基本的理论信息还很多,这里不再累述。
在阿里云SQL SERVER的主备架构中,使用大容量插入时,使用时需要特别留意一个特性需要明确指定,如果不指定,会触发微软尚未在SQL Server 2008 R2中未修复的BUG,会导致镜像SUSPEND,那么如何来避免各种情况呢? 下面列举了一些常见的场景:
1、通过ado.net sqlbulkcopy 方式:
只需要将SqlBulkCopy 指定SqlBulkCopyOptions.CheckConstraints就好,数据库指定AdventureWorks2008R2的Person表。举个例子:
static void Main()
{
string srcConnString = "Data Source=(local);Integrated Security=true;Initial Catalog=testdb";
string desConnString = "Data Source=****.sqlserver.rds.aliyuncs.com,3433;User ID=**;Password=**;Initial Catalog=testdb";
SqlConnection srcConnection = new SqlConnection();
SqlConnection desConnection = new SqlConnection();
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
srcConnection.ConnectionString = srcConnString;
desConnection.ConnectionString = desConnString;
sqlcmd.Connection = srcConnection;
sqlcmd.CommandText = @"SELECT top 1000000 [PersonType],[NameStyle],[Title],[FirstName],[MiddleName],[LastName],[Suffix],[EmailPromotion]
,[AdditionalContactInfo],[Demographics],NULL as rowguid,[ModifiedDate] FROM [testdb].[dbo].[Person]";
sqlcmd.CommandType = CommandType.Text;
sqlcmd.Connection.Open();
da.SelectCommand = sqlcmd;
da.Fill(dt);
using (SqlBulkCopy blkcpy = new SqlBulkCopy(desConnString, SqlBulkCopyOptions.CheckConstraints))
//using (SqlBulkCopy blkcpy = new SqlBulkCopy(desConnString, SqlBulkCopyOptions.Default))
{
blkcpy.BatchSize = 2000;
blkcpy.BulkCopyTimeout = 5000;
blkcpy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
blkcpy.NotifyAfter = 2000;
foreach (DataColumn dc in dt.Columns)
{
blkcpy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
}
try
{
blkcpy.DestinationTableName = "Person";
blkcpy.WriteToServer(dt);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
sqlcmd.Clone();
srcConnection.Close();
desConnection.Close();
}
}
}
private static void OnSqlRowsCopied(
object sender, SqlRowsCopiedEventArgs e)
{
Console.WriteLine("Copied {0} so far...", e.RowsCopied);
}
2、通过jdbc sqlbulkcopy 方式:
只需要在初始化对象时指定setCheckConstraints属性为TRUE,例如:
QLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
copyOptions.setCheckConstraints(true);
3、通过DTS/SSIS方式:
1. import/export data方式需要先保存SSIS包,然后修改Connection Manager的属性
2. 直接使用SQL Server Business Intelligence Development Stuidio新建 SSIS包
3、通过BCP方式
1. 先将数据BCP出来 BCP ...OUT
BCP testdb.dbo.person Out "bcp_data" /t /N /U **** /P *** /S "****.sqlserver.rds.aliyuncs.com,3433"
2. 然后将数据BCP进去 BCP...IN ,但需要指定提示:/h "CHECK_CONSTRAINTS"
BCP testdb.dbo.person In "bcp_data" /C /N /q /k /h "CHECK_CONSTRAINTS" /U *** /P *** /b 500 /S "***.sqlserver.rds.aliyuncs.com,3433"
4、通过bulk insert方式(在RDS不可是实现,因为不允许上传文件)
BULK INSERT testdb.dbo.person_in
FROM N'D:\trace\bcp.txt'
WITH
(
CHECK_CONSTRAINTS
);
四种方式教你在SQL Server中避免触发镜像SUSPEND,希望对大家的学习有所帮助。
猜你喜欢
- 引言:以前写的一个批量xls转csv的python简单脚本,用的是python2.7#coding=utf-8import osimport
- 前言:支付宝 2022 集五福活动正式开启数据显示,过去六年累计参与支付宝集五福的人数已经超过了 7 亿,每 2 个中国人里就有 1 个曾扫
- lambda函数是一种小的匿名函数。lambda语法lambda函数:lambda [arg1 [,arg2,...[,argn]]] :
- 如下所示:区别ArrayTensor类型uint8,float32系列{}各类型相互转换uint8转float64:image = imag
- 分析慢查询1.查看慢SQL是否启用,查看命令:show variables like 'log_slow_queries';
- Vignere密码Vignere Cipher包含用于加密和解密的Caesar Cipher算法. Vignere Cipher与Caesa
- 通过Python脚本批量生成插入数据的SQL语句原始SQL语句:INSERT INTO system_user (id, login_nam
- 张量范数:torch.norm(input, p=2) → float返回输入张量 input 的 p 范数举个例子:>>>
- 项目介绍go-admin 是一个中后台管理系统,基于(gin, gorm, Casbin, Vue, Element UI)实现。主要目的是
- 本文实例讲述了django框架自定义模板标签(template tag)操作。分享给大家供大家参考,具体如下:django 提供了丰富的模板
- 本文实例讲述了python通过pil为png图片填充上背景颜色的方法。分享给大家供大家参考。具体分析如下:png图片有些是没有背景颜色,如果
- ASP开发网页牢记注意事项 选择自 RAINMAN_NET&
- php中可以使用 mb_detect_encoding() 函数来判断字符串是什么编码的。当在php中使用mb_detect_encodin
- 1、信息表新建立一个字段,并用0、1的方法判断信息的状态。 2、新建一个页面,定时刷新,并查询表中是否有字段值为0的记录。 3、当管理员点击
- 一、爬山法简介爬山法(climbing method)是一种优化算法,其一般从一个随机的解开始,然后逐步找到一个最优解(局部最优)。 假定所
- 视频观看视频Python 类和对象面向对象编程是编写表示现实世界中的事物和情景的类,并基于这些类来创建对象。编写类时,你定义一大类对象都有的
- OAuth是一个关于授权(authorization)的开放网络标准,在全世界得到广泛应用,目前的版本是2.0版。本文对OAuth 2.0的
- 跳过开头首先是跳过开始部分,这个在我们读取文本的时候最常用。在实际的应用当中,比如记录的日志或者是代码等等,一般来说头部都会附上一段说明,或
- PHP bin2hex() 函数实例把 "Hello World!" 转换为十六进制值:<?php $str =
- 一 基本知识millisecond 毫秒microsecond 微秒 nanosecond 纳秒1秒=1000毫秒 1毫秒=1000微秒 1