THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance impact of controller cache: SQL Server large operations

In my previous post on the performance impact of controller cache configurations, I presented some empirical results showing the performance impact of configuring the controller cache to various read/write settings on large sequential I/Os.


Why did I single out large sequential I/Os? That's because large sequential I/Os are heavily used by SQL Server in performing such critical operations as bulk loads, table scans, and checkpoints, to name a few. Since you are most likely a SQL Server professional, a natural question is: do the results in the previous post really translate into anything relevant to SQL Server performance? Conceptually, the answer is of course yes.


But nothing beats seeing it directly. So I ran the following four SQL Server tests:


  1. Bulk Load
    A C# program was written to load data into the customer table using the System.Data.SqlClient.SqlBulkCopy class. The batch size for the bulk load was set to 50,000. The data was generated in the program per the TPC-C specifications for the customer table.

  2. Full table scan -- SELECT

    After the clean buffer pages were dropped, the following query was run against the customer table:

        SELECT avg(c_balance) FROM customer;

    Note that there is no index on the column c_balance.

  3. Full table scan -- UPDATE

    After the clean buffer pages were dropped, the following update was run against the customer table:

      UPDATE customer

         SET c_data = 'a' + substring(c_data, 2, len(c_data))

    There is no index on the c_data column.

  4. Checkpoint
    The 'recovery interval' sp_configure option was set to 32767 for the all tests mentioned in this post, effectively disabling automatic checkpoints. A manual checkpoint is issued after the UPDATE statement described above. This test measures the duration of that checkpoint.

All the tests were performed on the customer table in the TPC-C benchmark, whose DDL is included in the appendix. The customer table was populated with 3,000,000 rows, that took up about 2GB of storage space.


The tests were repeated multiple times for each of the following three test scenarios:

  1. 0MB/512MB
    Out of the 512MB total cache in the disk controller, 0MB was allocated for reads and 512MB for writes.

  2. 128MB/384MB
    Out of the 512MB total cache in the disk controller, 128MB was allocated for reads and 384MB for writes.

  3. 512MB/0MB
    Out of the 512MB total cache in the disk controller, 512MB was allocated for reads and 0MB for writes.

The test results are shown in the following four charts.


Figure 1 shows that for the bulk load benefited dramatically from controller write cache. When 512MB or 384MB was allocated to the write cache, the bulk load took about 53% of the time it took to load the same data when no write cache was allocated. From the perfmon, the bulk load was observed to cause ~64KB writes on both the data file and the log file of the test database.


Figure 2 shows that the full table scan query ran faster without any read cache in the controller. Per the perfmon counters, the table scan was doing >300KB reads.


Figure 3 shows that the full table scan update ran faster with write cache. Per the perfmon counters, the update was doing ~64KB writes to the log file. Figure 4 again shows that large sequential writes performed better with write cache because checkpoints were observed to be doing >128KB writes.


Overall, the results from these SQL Server operations are consistent with the results from pure I/Os tests described in the previous post.


Appendix: DDL for the test table customer


    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)

    on customer(c_w_id, c_d_id, c_id);

    on customer(c_w_id, c_d_id, c_last, c_first, c_id);

Published Thursday, March 27, 2008 12:23 AM by Linchi Shea
Filed under: , ,

Attachment(s): sql_queries.gif



Scott R. said:


Great work and results in this series.

I was especially interested in the select table scan being faster without any read cache than with read cache.  Although these test cases were not covered, I suspect that similar results may apply to DB backups (full DB backups for sure, and possibly to file-level / file group level DB backups, differential DB backups, and transaction log backups, although VLF cycling may make shorter bursts of sequential activity between skips to the next VLF - depending on how well the transaction logs are setup, preferably to use fewer versus many VLFs).  These might make for interesting test cases to confirm.

I looked through the article to see if the write caching policy used during the tests was stated (write-through or write-back), but I didn't find any reference.  I assume that the write cache was setup as write-through, to allow write requests to return control to the requestor as soon as possible (without waiting for the write to actually complete on the disk, relying on the RAID controller and battery-backed cache to reliably complete the write on an asynchronous basis).  Is that assumption correct?

Thanks again for your efforts and well-documented results.

Scott R.

March 27, 2008 11:25 AM

Linchi Shea said:


Good question on the physical drive write cache. In all the tests, physical drive write cache was disabled, which is the default setting on these HP smart array controllers. Per HP, physical drive write cache should be disabled because it is not battery-backed. See for more info. Note that the controller cache was batery-backed.

March 27, 2008 12:05 PM

Saggi Neumann said:

Hi Linchi,

Great posts on SQL Server & IO performance!

I wanted to ask what exactly you used to get your statistics? If by any chance, it was performance monitor, which counters did you use?


S. Neumann

March 28, 2008 3:08 PM

Linchi Shea said:

For this particular post, the metrics were all just durations of query executions. So no need for any perfmon counters. For the previous post on pure I/O results, the I/O benchmark tool (in this case sqlio.exe) produced the metrics (e.g. I/Os per secoond or MBs per second). No need for any perfmon counter either.

March 28, 2008 3:46 PM

Linchi Shea said:

Through empirical data presented in my two previous posts on the performance impact of controller cache

March 29, 2008 8:46 PM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement