SQL Server bulk copy (bcp) [c#sqlbulkcopy] very low performance issues

• Background

Recently to XX to do the project, the most headache here is the database storage bottleneck problem.

• Environment

Server environment: Virtual machine, allocate 32CPU, disk 1.4t,4t,5t,6t several servers are not equivalent (RPM is 7200r), memory 64G.

• Troubleshooting steps

　　Troubleshooting One: the database recovery model for simple mode, the database and the initial size of tempdb. Database file initialization size 100G, log file initialization size 50G, two files are automatically growing (by 10%); tempdb initialization size 10g*4 files, log 5g*4 files, two files are automatically grown (by 10%), distributed on two disks (but after reading this article, A little dumbfounded. ）；

Troubleshooting Two: set the database to occupy a maximum memory of 30G;

Troubleshooting Two: the database table deletes all indexes except the PK (OID bigint,time datetime) partition using the Time field (read this article, for fear behind);

Troubleshooting Three: system Windows Server (Vista kernel), upgrade to Windows Server R2 SP1 (WIN7 kernel);

Troubleshooting four: batch storage batch storage times, is still in the adjustment, the batchsize set to a suitable value, is 50W, or 200W it? (bcp principle)

Troubleshooting Five: database connection string 172.21.xxx.xxx\work, modified to (local) \work or. \work, can you use shared memory to establish a connection? Not yet tested. (Actual combat, Microsoft documentation)

Connection string Parameters: Data Source Or Server Or Address Or Addr Or Network Address

Default value: N/A

The name or network address of the instance of SQL Server to connect to. You can specify a port number after the server name: Server=tcp:servername, PortNumber Always use (local) when specifying a local instance. To enforce the use of a protocol, add one of the following prefixes: NP: (local), TCP: (local), LPC: (local)

Connection string Parameters: Network Library Or Net

Default value: ' DBMSSOCN '

A network library that is used to establish a connection to an instance of SQL Server. Supported values include DBNMPNTW (Named Pipes), DBMSRPCN (Multiprotocol), Dbmsadsn (Apple talk), Dbmsgnet (VIA), DBMSLPCN (Shared memory), and DBMSSPXN (ipx/spx) and DBMSSOCN (TCP/IP). The appropriate network DLLs must be installed on the system to which you are connecting. If you do not specify a network, use a local server (such as "." or "(local)"), the shared memory is used.

Troubleshooting six: SqlBulkCopy parameter sqlbulkcopyoptions settings

1. A table with an identity column
1.1 sqlbulkcopyoptions.keepidentity must be set! Otherwise, replication of past data will result in the identification column finding changes!
1.2 If the identity column of the original table is the primary key, it is sufficient to press the 1.1 setting. If the original table has no primary key, the original table (TRUNCATE table) must be emptied before copying, otherwise there will be multiple columns of the same identity value!
2. Columns that are null values
2.1 Sqlbulkcopyoptions.keepnulls must be set! Otherwise, when the source data field is NULL, the copy used to be a default value!

Description and analysis of several other options:
Default defaults are used for all options.
KeepIdentity retains the source identity value. If not specified, the target is assigned an identity value.
Checkconstraints Check the constraints while inserting the data. By default, constraints are not checked.
Tablelock gets the bulk update lock during a bulk copy operation. If not specified, row locks are used.
Keepnulls retains null values in the target table, regardless of the default value setting. If not specified, the null value is replaced by the default value (if applicable).
When Firetriggers is specified, it causes the server to fire the Insert trigger for rows inserted into the database. By default, triggers are not fired ...
Useinternaltransaction if specified, each batch of bulk copy operations will occur in the transaction. Executes in a transaction, either succeeds or is unsuccessful.

The Default is nothing to say, do not
KeepIdentity and Keepnulls above have been, no longer analyzed.
Checkconstraints does not need to, because is ready-made data, since already in the DB, must have passed the constraint check.
Tablelock is not required because both libraries need to be in a single-connected state during replication and there is no possibility of interference.
Firetriggers generally do not need it, after all, just copy data, and is ready-made data ...
Useinternaltransaction relationship is not big, anyway, replication failure will be logged to the custom log, failed to know, do it again.

• Advice given by others

ETL to handle:

For this solution, after the search to know that Microsoft SSIS is supporting ETL (actual combat 1, actual combat 2, Microsoft documentation). The results are not known until the test is needed.

Memory Processing:

is to put the data that can not be processed into memory, use Redis or memcached to store, and then put such a data source queued to the Sqlsever R2 database, the feasibility of the scheme needs to be tested, in the end need to how much equipment, One of the same configuration on the virtual function store how many records memory full load, this is the solution is the fundamental problem of the establishment, the problem is that we need data volume in tens of billions of levels of data, such a situation needs to be tested before the conclusion.

In addition, see SqlServer2014 (SQL 2014 new feature Introduction series-In-memory OLTP (In-memory OLTP)) also made a large adjustment, basically support memory storage, can be asynchronously quickly stored in memory, the scheme for the current device, I'm afraid it won't work.

Other, later in the search process learned how to achieve a copy, copy the scene of the application Mode (actual combat).

To replace SQL Server with Oracle:

This program I also want to go to do so, go back to the company's advice, but the company will pass the ...

Resources:

DBA Blog: http://www.cnblogs.com/CareySon/archive/2012/05/08/2489748.html

DBA Problem Lookup Experience Summary: http://blog.csdn.net/yynetsdk/article/details/6749529

SqlBulkCopy to achieve the original copy of the note: http://blog.csdn.net/yenange/article/details/35837247

SQL Server bulk copy (bcp) [c#sqlbulkcopy] very low performance issues