程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server中避免觸發鏡像SUSPEND的N種方法

SQL Server中避免觸發鏡像SUSPEND的N種方法

編輯:關於SqlServer

背景:
我們在使用數據庫的過程中,很多時候要追求性能,特別在處理大批量數據的時候更希望快速處理。那麼對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,希望對大家的學習有所幫助。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved