THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 read only workloads

In my previous post, I looked at how a typical OLTP workload may be affected by various controller cache configurations. And the conclusion was that giving too much cache (say all 512MB) to reads hurt the OLTP performance. The primary reason was that the writes from the OLTP workloads were starved of cache. Now, let's take a look at how the controller cache configurations may impact on the performance of read queries whose execution plans are dominated by index seeks (both clustered and nonclustered). The TPC-C read-only transactions happen to be queries dominated by index seeks.

 

As you'll see below, the index-seek dominated reads are not sensitive to controller cache configurations, at least for the test configurations described below.

 

The test configuration was exactly the same as what was described in the previous post except that the transaction mix was changed to the following to include only the two read-only transactions (i.e. Order Status and Stock Level).

  • New Order:        0%

  • Payment:          0%

  • Order Status:     50%

  • Delivery:         0%

  • Stock Level:      50%

To measure the transaction throughput, I had to use a different metric because the New Order transaction was no longer executed in the tests. Instead, the transaction throughput was measured as the number of the Stock Level transaction executed per second.

 

Again, the three test scenarios differed only in how the controller cache was configured:

  • 0 MB was allocated to reads and 512MB was allocated to writes

  • 128MB was allocated to reads and 384MB was allocated to writes

  • 512MB was allocated to reads and 0 MB was allocated to writes

The test results are shown in the following chart:

The chart shows little difference in the transaction throughput among the three test scenarios. You could argue that the scenario where 128MB was allocated to read performed the best. But the transaction throughput difference was not significant enough to justify that conclusion, although the pattern was consistent throughout the repeated test runs,

 

How was the I/O subsystem exercised by the read-only workloads? Let me again focus on the tests with 100 users. From a disk I/O perspective, the read-only workloads during the steady state can be characterized as follows:

  • On the F drive with the data file, the key LoggicalDisk perfmon counter values were as follows:

    * Avg. Disk Bytes/Read = ~8366 bytes
    * Avg. Disk sec/Read = 0.347
    * Current Disk Queue Length = 122
    * Disk Reads/sec = 446
     

  • The key Buffer Manager counter values were as follows:

    * Page life expectancy = ~60
    * Page reads/sec = 478
    * Readahead pages/sec = 236
    * Target pages = 193176
    * Total pages = 193176
     

  • The Wait Statistics were heavily concentrated on Page IO latch waits.

Published Tuesday, April 01, 2008 12:45 AM by Linchi Shea
Filed under: , ,

Attachment(s): sql_reads.gif

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jchang said:

the fact that too large a read cache degrades read only performance says that there is a cost to the read cache check. Bear in mind the proposed configuration is large system memory, much of which is data buffer, which is the way it is supposed to be. The expectation is that there is very little in the storage controller cache that is not already in memory. So the cost of the read cache inquiry on the controller is not too expensive at 128M, but is at 512M.

If the storage cache was larger than system memory data buffers, then the controller cache would see benefit, but this is not the right approach for databases. Put the memory on system.

April 1, 2008 9:59 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement