SQL Server中避免触发镜像SUSPEND的N种方法
背景:
我们在使用数据库的过程中,很多时候要追求性能,特别在处理大批量数据的时候更希望快速处理。那么对SQL SERVER而言,数据库实现大批量插入的优化方案,这里特别介绍通过大容量插入的一种方式。
基本原理
简单恢复模式按最小方式记录大多数大容量操作,对于完整恢复模式下的数据库,大容量导入期间执行的所有行插入操作被完整地记录到事务日志中。如果数据导入量较大,会导致迅速填满事务日志。对于大容量导入操作,按最小方式记录比完整记录更有效,并减少了大容量导入操作填满日志空间的可能性,所以性能会得到极大的提升。
,大容量导入中按最小方式记录日志的前提条件需要满足
1. 当前没有复制表
2. 指定了表锁定:
注意锁定是 SQL Server 数据库引擎用来对多个用户访问同一数据块的操作进行同步。当事务修改某个数据块时,它将持有保护所做修改的锁,直到事务结束。指定大容量导入操作的表锁定后,该表将在大容量导入操作期间采取大容量更新 (BU) 锁定。大容量更新 (BU) 锁允许多个线程将数据并发地大容量导入到同一表中,阻止其他不进行大容量导入数据的进程访问该表。表锁定可以通过减少表的锁争用来提高大容量导入操作的性能。
基本的理论信息还很多,这里不再累述。
在阿里云SQL SERVER的主备架构中,使用大容量插入时,使用时需要特别留意一个特性需要明确指定,如果不指定,会触发微软尚未在SQL Server 2008 R2中未修复的BUG,会导致镜像SUSPEND,那么如何来避免各种情况呢? 狼蚁网站SEO优化列举了一些常见的场景
1、通过ado. 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.,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 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.,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.,3433"
4、通过bulk insert方式(在RDS不可是实现,因为不允许上传文件)
BULK INSERT testdb.dbo.person_in FROM N'D:\trace\bcp.txt' WITH ( CHECK_CONSTRAINTS );
四种方式教你在SQL Server中避免触发镜像SUSPEND,希望对大家的学习有所帮助。
编程语言
- 如何快速学会编程 如何快速学会ug编程
- 免费学编程的app 推荐12个免费学编程的好网站
- 电脑怎么编程:电脑怎么编程网咯游戏菜单图标
- 如何写代码新手教学 如何写代码新手教学手机
- 基础编程入门教程视频 基础编程入门教程视频华
- 编程演示:编程演示浦丰投针过程
- 乐高编程加盟 乐高积木编程加盟
- 跟我学plc编程 plc编程自学入门视频教程
- ug编程成航林总 ug编程实战视频
- 孩子学编程的好处和坏处
- 初学者学编程该从哪里开始 新手学编程从哪里入
- 慢走丝编程 慢走丝编程难学吗
- 国内十强少儿编程机构 中国少儿编程机构十强有
- 成人计算机速成培训班 成人计算机速成培训班办
- 孩子学编程网上课程哪家好 儿童学编程比较好的
- 代码编程教学入门软件 代码编程教程