Search results matching tags 'Performance' and 'Data Load'

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: 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.

T-SQL Exercise: Simplest data-loading script to produce worst query performance – May 29th Update

Linchi Shea — Fri, 29 May 2009 16:23:00 GMT

This is another follow-up on the T-SQL exercise.

So the test query below is rather simple:

DBCC DROPCLEANBUFFERS

SELECT COUNT(*) FROM dbo.test;

But beneath its simple appearance, many factors are at play and interact in a complex way to influence the query performance. In other words, trying to predict its performance is not trivial. You may do better than predicting the stock market performance. But still it can be a rather unreliable business.

So in the May 25^th update, I reported on the results of running the test queries with the data file on a RAID-1 set with two internal disks. Yesterday, I triple checked the test result and the result could be re-produced at will.

To see whether or what impact a different storage system may have on the performance, I placed the data file on a departmental disk array behind a SAN. Unlike a typical enterprise class disk array, this departmental disk array does not have a huge amount of cache. In addition, the data block placement is effectively virtualized in that it uses a mapping table to place data across all the drives, making it effectively impossible to know or control where the data is placed at the OS level.

With the data file on this disk array, I repeated the same tests. Each time before the data was loaded, the test table was dropped and the database was empty with no user data.

Again, after the data was loaded by each script, the test query (including DBCC DROPCLEANBUFFERS) was run three times, and each time the query elapsed time was logged. Additional validation test runs were performed to ensure that the test results were re-producible, and they are re-producible.

The following table shows the recorded elapsed times.

Test run	Elapsed time (second)
Adam’s script – test run 1	74
Adam’s script – test run 2	82
Adam’s script – test run 3	81
Tibor’s script – test run 1	248
Tibor’s script – test run 2	235
Tibor’s script – test run 3	239
Linchi’s script – test run 1	155
Linchi’s script – test run 2	146
Linchi’s script – test run 3	148

First thing to note from the above table is that the ranking of the test query performance changed significantly. In this setup, Tibor’s data set caused the test query to have the worst performance.

But what is most striking is that while the test query performed significantly better with Adam’s and my data sets on the disk array than they did on the internal disks, the test query performed noticeably worse with Tibor’s data set on the disk array than it did on the internal disks.

This is getting more interesting. And I might add that this is yet another piece of evidence on the conspiracy of the every-table-must-have-a-clustered-index crowd :-)

I’ll come back to explore what might be the cause later.