C# code to obtain Maximum SQL Server 2012 Write Performance

Question

I want to know if we are already getting the fastest SqlSever Write Performance for our application.

We created a sample application that performs a BulkCopy operation to a local SQL Server database. The BulkCopy operation writes 100,000 rows of data from a DataTable in memory. The table being inserted into has no indexes. This is because we just want to get the maximum write speed of SQL Server.

Here is the schema of the table we are inserting into:

CREATE TABLE [dbo].[HistorySampleValues](
[HistoryParameterID] [bigint] NOT NULL,
[SourceTimeStamp] [datetime2](7) NOT NULL,
[ArchiveTimestamp] [datetime2](7) NOT NULL,
[ValueStatus] [int] NOT NULL,
[ArchiveStatus] [int] NOT NULL,
[IntegerValue] [int] SPARSE  NULL,
[DoubleValue] [float] SPARSE  NULL,
[StringValue] [varchar](100) SPARSE  NULL,
[EnumNamedSetName] [varchar](100) SPARSE  NULL,
[EnumNumericValue] [int] SPARSE  NULL,
[EnumTextualValue] [varchar](256) SPARSE  NULL
) ON [PRIMARY]

We measure the performance from our C# code.

public double PerformBulkCopy()
    {
        DateTime timeToBulkCopy = DateTime.Now;
        double bulkCopyTimeSpentMs = -1.0;

        DataTable historySampleValuesDataTable = CreateBulkCopyRecords();

        //start the timer here
        timeToBulkCopy = DateTime.Now;
        using (SqlConnection sqlConn = ConnectDatabase())
        {
            sqlConn.Open();

            using (SqlTransaction sqlTransaction = sqlConn.BeginTransaction())
            {
                try
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.KeepIdentity, sqlTransaction))
                    {
                        sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_HISTORY_PARMETER_ID, SqlServerDatabaseStrings.SQL_FIELD_HISTORY_PARMETER_ID);
                        sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_SOURCE_TIMESTAMP, SqlServerDatabaseStrings.SQL_FIELD_SOURCE_TIMESTAMP);
                        sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_VALUE_STATUS, SqlServerDatabaseStrings.SQL_FIELD_VALUE_STATUS);
                        sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_ARCHIVE_STATUS, SqlServerDatabaseStrings.SQL_FIELD_ARCHIVE_STATUS);
                        sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_INTEGER_VALUE, SqlServerDatabaseStrings.SQL_FIELD_INTEGER_VALUE);
                        sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_DOUBLE_VALUE, SqlServerDatabaseStrings.SQL_FIELD_DOUBLE_VALUE);
                        sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_STRING_VALUE, SqlServerDatabaseStrings.SQL_FIELD_STRING_VALUE);
                        sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_ENUM_NAMEDSET_NAME, SqlServerDatabaseStrings.SQL_FIELD_ENUM_NAMEDSET_NAME);
                        sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_ENUM_NUMERIC_VALUE, SqlServerDatabaseStrings.SQL_FIELD_ENUM_NUMERIC_VALUE);
                        sqlBulkCopy.ColumnMappings.Add(SqlServerDatabaseStrings.SQL_FIELD_ENUM_TEXTUAL_VALUE, SqlServerDatabaseStrings.SQL_FIELD_ENUM_TEXTUAL_VALUE);

                        sqlBulkCopy.DestinationTableName = SqlServerDatabaseStrings.SQL_TABLE_HISTORYSAMPLEVALUES;

                        sqlBulkCopy.WriteToServer(historySampleValuesDataTable);
                    }

                    sqlTransaction.Commit();
                    //end the timer here
                    bulkCopyTimeSpentMs = DateTime.Now.Subtract(timeToBulkCopy).TotalMilliseconds;
                }
                catch (Exception ex)
                {
                    sqlTransaction.Rollback();
                }

                CleanUpDatabase(sqlConn);
            }
            sqlConn.Close();
        }

        return bulkCopyTimeSpentMs;
    }

I have tried the different overloads of SqlBulkCopy.WriteToServer(): DataTable, DataReader and DataRow[].

On a machine with this specs: I3-2120 CPU @ 3.30GHz 8GB of RAM Seagate Barracuda 7200.12 ST3500413AS 500GB 7200 RPM

I am getting a throughput of ~150K-160K rows inserted per second using the different overloads.

Btw, I'm not including the creation of the DataTable in the measurement because I just wanted to get the actual performance of SQLBulkCopy as a baseline.

I am asking now, given our sample data and the sample table, is this the most we can get out of SQL Server SE? Or is there something we can do to make this even faster?

Let me know if there are more information you need about our setup

Answer 1

Please read The Data Loading Performance Guide. And also How to analyse SQL Server performance. You can't solve a performance problem without first identifying the bottleneck, the second link will help you properly measure where your bottleneck is. Right now it could be anywhere, for instance it could be a matter that your client is not sending data faster than 160 rows/sec (paging of a large DataTable object could easily explain such) .

As a general, non-specific and non-committal answer, you need to achieve minimally logged operations using reasonable sized batches. Which implies commit every N rows (usually something int he 10k-100k rows range). And make sure your database is properly set up for minimally logged operations, see Operations That Can Be Minimally Logged.

But again, the most important thing is to measure so you identify the bottleneck.

As a side note, I really do hope you use SPARSE columns because your real table has +1000 columns. As is posted here, the use of SPARSE is just detrimental and offers no benefits.

Answer 2

Both SSIS and bcp utility will perform better.

If you have to use SqlBulkCopy, make sure that you've covered all the basics: disabling indexes and triggers, switching to simple or bulk load recovery model to minimize tlog, x-lock on the table and so on. Also fine-tune the BatchSize property (depending on yur data, it could be a value between 100 and 5000) and maybe UseInternalTransaction as well.

Here's a link to MSDN you'll probably find helpful:

http://msdn.microsoft.com/en-us/library/ms190421.aspx