在SQL Server中避免触发镜像SUSPEND的几种方法

更新时间:2016-01-22 13:54:55 点击次数:2079次

背景:

我们在使用数据库的过程中,很多时候要追求性能,特别在处理大批量数据的时候更希望快速处理。那么对SQL SERVER而言,数据库实现大批量插入的优化方案,这里特别介绍通过大容量插入的一种方式。

基本原理:

简单恢复模式按小方式记录大多数大容量操作,对于完整恢复模式下的数据库,大容量导入期间执行的所有行插入操作被完整地记录到事务日志中。如果数据导入量较大,会导致迅速填满事务日志。对于大容量导入操作,按小方式记录比完整记录更有效,并减少了大容量导入操作填满日志空间的可能性,所以性能会得到极大的提升。

       但是,大容量导入中按小方式记录日志的前提条件需要满足:

1. 当前没有复制表

2. 指定了表锁定: 

注意:锁定是 SQL Server 数据库引擎用来对多个用户同时访问同一数据块的操作进行同步。当事务修改某个数据块时,它将持有保护所做修改的锁,直到事务结束。指定大容量导入操作的表锁定后,该表将在大容量导入操作期间采取大容量更新 (BU) 锁定。大容量更新 (BU) 锁允许多个线程将数据并发地大容量导入到同一表中,同时阻止其他不进行大容量导入数据的进程访问该表。表锁定可以通过减少表的锁争用来提高大容量导入操作的性能。

基本的理论信息还很多,这里不再累述。

 

在阿里云SQL SERVER的主备架构中,使用大容量插入时,使用时需要特别留意一个特性需要明确指定,如果不指定,会触发微软尚未在SQL Server 2008 R2中未修复的BUG(https://technet.microsoft.com/en-us/library/ms186247%28v=sql.105%29.aspx,会导致镜像SUSPEND,那么如何来避免各种情况呢? 下面列举了一些常见的场景:

 

通过ado.net sqlbulkcopy 方式:

只需要将SqlBulkCopy 指定SqlBulkCopyOptions.CheckConstraints就好,数据库指定AdventureWorks2008R2Person。举个例子:

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);

}

 

通过jdbc  sqlbulkcopy 方式:

只需要在初始化对象时指定setCheckConstraints属性为TRUE,例如:

QLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();

copyOptions.setCheckConstraints(true);

实例可参见:https://msdn.microsoft.com/zh-cn/library/mt221490(v=sql.110).aspx

 

通过DTS/SSIS方式:

1.    import/export data方式需要先保存SSIS包,然后修改Connection Manager的属性

 

2.    直接使用SQL Server Business Intelligence Development Stuidio新建 SSIS

 

 

通过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"

 

通过bulk insert方式(在RDS不可是实现,因为不允许上传文件)

BULK INSERT testdb.dbo.person_in

FROM N'D:\trace\bcp.txt'

WITH

(

 CHECK_CONSTRAINTS 

);

本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责,本站只提供参考并不构成任何投资及应用建议。本站是一个个人学习交流的平台,网站上部分文章为转载,并不用于任何商业目的,我们已经尽可能的对作者和来源进行了通告,但是能力有限或疏忽,造成漏登,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

回到顶部
嘿,我来帮您!