Search results matching tags 'performance' and 'SqlBulkCopy'

Performance impact: SqlBulkCopy.WriteToServer() and interesting space allocation behavior

Linchi Shea — Tue, 05 Jul 2011 02:35:00 GMT

After reading Adam Machanic’s comment to my previous post, I started to wonder if what I saw was all due to the fact that SQL Server blindly chooses to allocate at least one extent in response to each call to WriteToServer(), perhaps in its zeal to achieve minimal logging.

Such an algorithm sounds a bit crazy, or even dumb. But anything is possible.

To check if this is indeed the case, I wrote a simple test program below with most of the parameters hard coded. The only exception is the payload, i.e. the number of rows that WriteToServer() copies to the server. BTW, the target database needs to be in either bulk_logged or simple recovery mode.

using System;

using System.Collections;

using System.IO;

using System.Data;

using System.Data.SqlClient;

class Loader

{

static void Main(string[] args)

{

int payload = 0;

try

{

payload = int.Parse(args[0]);

}

catch

{

Console.WriteLine("***Err: The argument must be an integer.");

Environment.Exit(-1);

}

int row_counter = 0;

int write_2_server_counter = 0;

int extent_counter = 0;

using (SqlConnection bcp_conn = new SqlConnection("server=myServer;database=myDB;Integrated Security=SSPI;"),

conn = new SqlConnection("server=myServer;database=myDB;Integrated Security=SSPI;"))

{

try

{

bcp_conn.Open();

conn.Open();

try

{

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

cmd.CommandType = CommandType.Text;

cmd.CommandText = "if object_id('dbo.junk') is not NULL " +

" drop table junk";

cmd.ExecuteNonQuery();

cmd.CommandText = "CREATE TABLE dbo.junk( " +

" c_id int NOT NULL, " +

" c_data char(950) NOT NULL)";

cmd.ExecuteNonQuery();

}

catch(Exception e)

{

Console.WriteLine(e.ToString());

Environment.Exit(-1);

}

DataTable dt = new DataTable();

dt.MinimumCapacity = 20000;

DataColumn c_id = new DataColumn();

c_id.DataType = System.Type.GetType("System.Int32");

c_id.ColumnName = "c_id";

dt.Columns.Add(c_id);

DataColumn c_data = new DataColumn();

c_data.DataType = System.Type.GetType("System.String");

c_data.ColumnName = "c_data";

dt.Columns.Add(c_data);

using (SqlBulkCopy bcp = new SqlBulkCopy(bcp_conn, SqlBulkCopyOptions.TableLock, null))

{

bcp.DestinationTableName = "dbo.junk";

bcp.BatchSize = 20000;

bcp.BulkCopyTimeout = 30000000;

for (int i = 1; i <= 1000; i++)

{

row_counter++;

DataRow row = dt.NewRow();

row["c_id"] = i;

row["c_data"] = "12345667890abcdefgh";

dt.Rows.Add(row);

if (row_counter % payload == 0)

{

bcp.WriteToServer(dt);

write_2_server_counter++;

extent_counter = GetExtentCount(conn);

Console.WriteLine("{0},{1}", write_2_server_counter, extent_counter);

dt.Rows.Clear();

}

if (dt.Rows.Count > 0)

{

bcp.WriteToServer(dt);

dt.Rows.Clear();

}

} // using

}

catch(Exception e)

{

Console.WriteLine(e.ToString());

Environment.Exit(-1);

}

} // Main()

static int GetExtentCount(SqlConnection conn)

{

int extent_count = 0;

try

{

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

cmd.CommandType = CommandType.Text;

cmd.CommandText = "select cast(u.total_pages/8 as int) " +

" from sys.partitions p join sys.allocation_units u " +

" on p.partition_id = u.container_id " +

" where p.object_id = object_id('junk')";

extent_count = (int) cmd.ExecuteScalar();

}

catch(Exception e)

{

Console.WriteLine(e.ToString());

Environment.Exit(-1);

}

return extent_count;

}

} // class Loader

If you run this program (and I have run this against a SQL Server 2008 instance), you’ll find that as long as the payload parameter value you supply on the command line is 64 or less, SQL Server will allocate a new extent for every call to WriteToServer() no matter how little data is being written to the server and no matter how much free space is already allocated to the table

This behavior is utterly striking!!!

If the payload parameter value is 65, the amount of data no longer fits on one extent, and understandably SQL Server needs to allocate—and does allocate--two extents for each WriteToServer().

I’m all for reducing the amount of transaction logging whenever appropriate. Furthermore, if this space cost is the result of my explicitly configuring something to favor minimal logging, I’m willing to live with the consequence. But in this test nowhere did I say that I wanted minimal logging at any cost.

One may argue that this is an edge case because people are not expected to--or should not--call WriteToServer() with a small payload. But regardless, at least the potential space implication of doing so should have been clearly made known so that people can explicitly protect their code from falling into it. And in the software business, it’s often the edge cases that trip people up.

Performance impact: how to waste time and space with SqlBulkCopy.WriteToServer()?

Linchi Shea — Sun, 03 Jul 2011 02:00:00 GMT

If you have some time to kill, you could try to load a tiny amount of data, say ~3GB or 3,000,000 rows, with SqlBulkCopy, but do remember to give the WriteToServer method a very small payload every time it is called. The test results reported in my previous post suggest that once you have started loading, you might as well go for a lunch break.

It turns out wasting space is just as easy. In fact, wasting a great amount of space is made easy with calling SqlBulkCopy.WriteToServer with a small payload. All you need to do is direct that payload towards a heap table.

How much space can you waste? Well, how about SQL Server allocating more than 90GB of the storage space in order to import about 3GB of data?

I ran a series of data loading tests with SqlBulkCopy against the same customer table used in my previous post. Each test loaded 3,000,000 rows into the customer table without any index. The database was pre-allocated 200GB of space, and the customer table was the only user table. The test data was generated in a C# program and added to a DataTable object, which was then passed to the WriteToServer method to be copied to the customer table on the SQL Server 2008 instance. This data loader program can be configured to run the bulk importing either single threaded or in any number of threads concurrently into multiple non-overlapping ranges.

The tests were first divided into two groups. The first group of tests was to load the data in a single thread, and the second group of tests in 10 threads. In each group, tests were run with different levels of payload. More specifically, I tested populating the DataTable object with the following number of rows before calling the WriteToServer method with that object:

2, 5, 10, 20, 50, and 100

Before presenting the results, let me establish the frame of reference with the results from calling WriteToServer with a payload of 2,000 rows:

Payload (# of rows)	Threads	Duration (second)	Data (GB)	Reserved (GB)	Unused (GB)
2,000	1	289	2.4	3.8	1.4
2,000	10	94	2.4	3.8	1.4

Okay, there was a huge performance difference between loading the data in a single thread versus loading the data in 10 threads. But I’d like to turn your attention to the space usage. At the completion of loading the data, I looked at how much space was actually used by the table data, how much space was reserved by SQL Server for the table, and how much space was allocated to the table but not used.

The above table shows that with payload = 2,000 rows, there was 1.4GB of unused space allocated to the table in both cases.

Now take a look at the following table and pay attention to the highlighted cells:

Payload (# of rows)	Threads	Duration (second)	Data (GB)	Reserved (GB)	Unused (GB)
2	1	10,228	12	96	84
5	1	4,074	4.8	38	33.2
10	1	2,206	2.4	19.2	16.8
20	1	1,267	2.4	9.6	7.2
50	1	574	2.4	3.8	1.4
100	1	385	2.4	3.8	1.4
2	10	1,998	10	87	77
5	10	872	4.8	38	33.2
10	10	441	2.4	19	16.8
20	10	240	2.4	9.6	7.2
50	10	135	2.4	3.8	1.4
100	10	105	2.4	3.8	1.4

The amount of space wasted was staggering when the payload was 2 rows or 5 rows.

For instance, SQL Server had to allocate a whopping 96GB in order to load the 3,000,000 million rows or about 3GB worth of data into the customer table when WriteToServer had to copy 2 rows at a time. Good news is that this appears to be an anomaly only when the payload was extremely low. As shown in the above two tables, as soon as the payload level was greater than 50, SQL Server did not need to allocate more than 3.8GB of storage space.

Number of threads made little difference in terms of how much space was wasted.

The extra space is quite easy to reclaim by creating a clustered index. The potential problem, however, is that you could run out of disk space during the bulk load if you didn't know about this behavior. In addition, you may not have planned to pre-allocate a lot of free space and bulk loading with a small payload can cause the database file to autogrow, thus further degrading the performance.

So why would SQL Server waste so much space in the case of a very small payload for WriteToServer? I don’t know enough about the internals of the SQL Server space allocation algorithm and/or SqlBulkCopy to offer an answer. But it’s revealing to check how the pages were allocated and used in some of these outrageous cases. For instance, when the payload was 2 rows, each database extent only had a single used page on average, and in each used page only 17% was occupied with the customer table rows.

The space usage picture was slightly better when the payload was 5 rows. While it remained the same that one page was used per extent, the average density of the page was higher at about 42%.

When the payload was bumped up to 50, the used page count per extent increased to 5 and the page density was respectable at about 85%.

No matter what may be the rationale for this behavior, SQL Server does seem to not handle space allocation efficiently when the WriteToServer payload is extremely small.

The solution? Well, just avoid small payload when using SqlBulkCopy. In my own test environment, that means I don’t want the WriteToServer method to keep copying fewer than several hundred rows.

Performance impact: What is the optimal payload for SqlBulkCopy.WriteToServer()?

Linchi Shea — Fri, 01 Jul 2011 15:54:00 GMT

For many years, I have been using a C# program to generate the TPC-C compliant data for testing. The program relies on the SqlBulkCopy class to load the data generated as per the TPC-C specifications into the SQL Server tables. In general, the performance of this C# data loader is satisfactory. Lately however, I found myself in a situation where I needed to generate a much larger amount of data than I typically do and the data needed to be loaded within a confined time frame. So I was driven to look into the code more carefully to see if it could be significantly sped up.

Among other things, I became curious about the performance impact of the amount of data copied by the WriteToServer(DataTable) method. I’ll call this amount of data the WriteToServer payload in this post, or simply the payload.

I was a bit lazy when I first coded the C# data loader, and no knowing the full implication, I set the payload to the same value I assigned to the BatchSize property. That proves to be terribly inconvenient when it comes to tuning the loader’s performance.

By the way, in the Microsoft Windows SDK documentation, the descriptions for WriteToServer(DataTable) and BatchSize are as follows:

WriteToServer	Copies all rows in the supplied DataTable to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.
BatchSize	Number of rows in each batch. At the end of each batch, the rows in the batch are sent to the server. If UseExternalTransaction is not in effect, each batch of rows is inserted as a separate transaction.

Going off the topic a bit, in the BatchSize description, it says that, “At the end of each batch, the rows in the batch are sent to the server” This is at least confusing. I could be wrong, but the setting of BatchSize alone will not cause the SqlBulkCopy object to initiate any action of sending any rows over the wire to the server.

The question is how many rows should we have in the DataTable object before we send them over to the server with WriteToServer? In other words, how should we size the WriteToServer payload?

The most optimal setting no doubt depends on the configuration of the environment. However, intuitively there is overhead in sending data across the wire, and you would not want the payload to overhead ratio to be too low. On the other hand, you probably don’t want to wait for an excessive amount of time just for the DataTable object to be populated with a very large payload before you send its content. So there should be a happy medium somewhere, we hope.

Some tests should help give us a more precise idea

I used the TPC-C customer table for my tests. The customer table was defined as follows:

(

c_id int,

c_d_id tinyint,

c_w_id int,

c_first char(16),

c_middle char(2),

c_last char(16),

c_street_1 char(20),

c_street_2 char(20),

c_city char(20),

c_state char(2),

c_zip char(9),

c_phone char(16),

c_since datetime,

c_credit char(2),

c_credit_lim numeric(12,2),

c_discount numeric(4,4),

c_balance numeric(12,2),

c_ytd_payment numeric(12,2),

c_payment_cnt smallint,

c_delivery_cnt smallint,

c_data char(500)

)

For each of the tests, three million rows were loaded into the customer table without any index , and the following row numbers were tested for the WriteToServer method:

2, 20, 100, 200, 2,000, 20,000, and 3,000,000

Three million rows amounted to about 2.2GB of data after they were all loaded into the customer table. This amount of test data was small compared to the amount of data I needed to load, but was sufficient for the test purpose, and it helped reducing the test time. In addition, for all the tests, SqlBulkCopy.BatchSize was set to 20,000.

The other test parameter considered was the number of threads in loading the data concurrently into non-overlapping ranges. I looked at loading the three million rows single threaded versus in 10 threads.

The results are in the following table:

Threads	Payload (WriteToServer Row Number)	Load Duration (second)
1	2	>1440
1	20	~1440
1	100	397
1	200	300
1	2,000	289
1	20,000	278
1	3,000,000	270
10	2	1998
10	20	238
10	100	105
10	200	90
10	2,000	94
10	20,000	100
10	3,000,000	N/A

So, at least for my test environment, it does not look like a good idea for WriteToServer to send 20 or fewer rows to the server each time. We should stock up the payload a bit more before each trip.

The loader program was run on one of the nodes in a two-node cluster, and the SQL Server instance was on the other node. The network in between was 1Gbps. There was more than sufficient bandwidth and the latency was low. With extremely low payload (i.e. when the DataTable object only had 2~20 rows), the network bandwidth was barely used (4% or so).

A salient point to note from the test result is that once the payload crossed a certain point, for instance 200 rows in these tests, the exact payload size no longer mattered much. For instance, it made little difference whether it was 2000 rows or 20,000 rows.

Loading data concurrently did help to mask the penalty of a very low payload. For instance, when the data was loaded in a single thread, setting payload to 20 would cause the loader to take 1440 seconds to load the three million rows, whereas the same payload with 10 threads dramatically reduced the load time to 238 seconds.

In the next post, I’ll look at a few other implications of the payload setting more closely.