SqlServer批量複製(bcp)[C#SqlBulkCopy]效能極低問題

標籤：

• 背景

　　最近一段給xx做項目，這邊最頭疼的事情就是資料庫入庫瓶頸問題。 

• 環境

　　伺服器環境：虛擬機器，分配32CPU，磁碟1.4T，4T,5T，6T幾台伺服器不等同（轉速都是7200r），記憶體64G。

• 排查步驟

　　排查一：資料庫復原模式為簡單模式，資料庫和tempdb的初始大小。資料庫檔案初始化大小100G，記錄檔初始化大小50G，兩個檔案都是自動成長（按10%）；tempdb初始化大小10G*4個檔案，日誌5G*4個檔案，兩個檔案都是自動成長（按10%），分布在兩個磁碟中（但看了這篇文章後，有點傻眼了。）；

排查二：設定資料庫佔用最大記憶體為30G；

排查二：資料庫表刪掉所有索引，除了PK（OID bigint,Time datetime）分區使用了Time欄位（看了這篇文章，為後邊擔憂）；

排查三：系統windows server 2008(Vista核心)，升級為windows server 2008 R2 SP1(WIN7核心)；

排查四：批量入庫一次批量入庫的次數，目前還在調整中，把BatchSize設定為一個合適的值，是50W，還是200W呢？（BCP原理篇）

排查五：資料庫連接字串把172.21.xxx.xxx\work，修改為(local)\work或者.\work，是否可以採用共用記憶體的方式來建立串連呢？目前還未測試。（實戰篇，微軟文檔篇）

連接字串參數： Data Source - 或 - Server - 或 - Address - 或 - Addr - 或 - Network Address

預設值： N/A

要串連的 SQL Server 執行個體的名稱或網路地址。可以在伺服器名稱之後指定連接埠號碼： server=tcp:servername, portnumber 指定本地執行個體時，始終使用 (local)。若要強制使用某個協議，請添加下列首碼之一： np:(local), tcp:(local), lpc:(local)

連接字串參數： Network Library - 或 - Net

預設值： ‘dbmssocn‘

用於建立與 SQL Server 執行個體的串連的網路程式庫。支援的值包括 dbnmpntw（具名管道）、dbmsrpcn（多協議）、dbmsadsn (Apple Talk)、dbmsgnet (VIA)、dbmslpcn（共用記憶體）及 dbmsspxn (IPX/SPX) 和 dbmssocn (TCP/IP)。 相應的網路 DLL 必須安裝在要串連的系統上。如果不指定網路而使用一個本機伺服器（比如“.”或“(local)”），則使用共用記憶體。

排查六：SqlBulkCopy參數SqlBulkCopyOptions設定

1. 有識別欄位的表
　　1. 1 SqlBulkCopyOptions.KeepIdentity 必須設定！否則會出現複製過去的資料產生識別欄位發現變化的情況！
　　1.2 如果原表的識別欄位即為主鍵， 那按1.1 的設定已足夠。 如果原表無主鍵， 那在複製之前必須先清空原表(truncate table)， 否則會出現多個相同的標識值的列！
2. 為NULL值的列
　　2.1 SqlBulkCopyOptions.KeepNulls 必須設定！否則會出現來源資料的欄位為NULL時， 複製過去卻成了預設值！

其它幾個選項的說明與分析：
　　Default 對所有選項使用預設值。
　　KeepIdentity 保留源標識值。如果未指定，則由目標分配標識值。
　　CheckConstraints 請在插入資料的同時檢查約束。預設情況下，不檢查約束。
　　TableLock 在批量複製操作期間擷取批次更新鎖。如果未指定，則使用行鎖。
　　KeepNulls 保留目標表中的空值，而不管預設值的設定如何。如果未指定，則空值將由預設值替換（如果適用）。
　　FireTriggers 指定後，會導致伺服器為插入到資料庫中的行激發插入觸發器。 預設情況下，　是不激發觸發器的……
　　UseInternalTransaction 如果已指定，則每一批批量複製操作將在事務中發生。 在一個事務中執行，要麼都成功，要麼都不成功。

Default 就沒有什麼好說的了， 不要
KeepIdentity 和 KeepNulls 上面已有了， 不再分析。
CheckConstraints 不需要， 因為是現成的資料， 既然已在DB中， 必然是通過了約束檢查的。
TableLock 不需要， 因為複製時兩個庫都需要處於單串連狀態， 不可能有幹擾。
FireTriggers 一般就不需要了吧， 畢竟只是複製資料， 而且是現成的資料……
UseInternalTransaction 關係也不大， 反正複製失敗會記錄到自訂的日誌， 失敗了也知道， 重來一次就可以了。

 

 

• 別人給的建議

ETL來處理:

　　針對這個解決方案，搜尋了後才知道微軟的SSIS中是支援ETL的（實戰1、實戰2篇，微軟文檔篇）。具體需要測試才知道結果。

 記憶體處理：

　　就是把未能處理的資料放到記憶體中，使用Redis或者memcached來儲存，之後把這樣的資料來源排隊性的儲存到sqlsever 2008 R2資料庫中，該方案可行性還需要測試，到底需要多大的裝置，一個上邊同樣配置的虛擬機器能儲存多少條記錄會記憶體滿負載，這是該方案是否成立的根本問題，問題是我們需要的是資料量在幾百億量級的資料，這樣的情況需要測試才會有結論。

　　另外，看到SqlServer2014（SQL 2014新功能介紹系列1 – 記憶體內部 OLTP (In-Memory OLTP)）也做了比較大的調整，基本上支援記憶體儲存，可以非同步快速儲存到記憶體中，該方案以目前裝置來說，恐怕行不通。

　　其他，後來搜尋過程中學習到了怎麼實現一個複製的，複製的情境的應用模式（實戰篇）。

用Oracle來替換SqlServer：

　　該方案我也早想去這麼去做，回去就給公司建議，不過公司一定會PASS的。。。

 

 

 

 

參考資料：

DBA部落格：http://www.cnblogs.com/CareySon/archive/2012/05/08/2489748.html

DBA問題尋找經驗總結：http://blog.csdn.net/yynetsdk/article/details/6749529

SqlBulkCopy 實現原汁原味複製的注意事項：http://blog.csdn.net/yenange/article/details/35837247

SqlServer批量複製(bcp)[C#SqlBulkCopy]效能極低問題