Performance Impact: Setting a Database to Read Only

It is widely quoted that if a SQL Server database is set to Read Only (using ALTER DATABASE ... SET READ_ONLY), your queries may run faster because no locking occurs in a read-only database. This all makes sense conceptually. But I have not seen any empirical data to get a feel for the extent of the impact. And it's always good to have some solid numbers to show either (1) yes, it does make a difference, or (2) no, I can't see any difference.

Now, it may be difficult to see the impact of setting a database to read only if you are looking at a single individual query. With many queries executing concurrently, I hope to see something conclusive, less so in terms of individual query performance but more so in terms of transaction throughput.

The test database was populated with the generated TPC-C data (scaled for 100 warehouses).

Two read-only stored procedures corresponding to the two read-only TPC-C transactions (Order Status and Stock Level) were run against the database. The calls were evenly distributed between these two procedures.

A series of tests with different number of concurrent users were run. The number of concurrent users simulated were 5, 10, 20, 50, 100, 200, 400, and 600.

Note that the stored procedure calls were randomly distributed among the database pages, and after an initial ramp up, all the pages were cached in memory. This eliminated disk I/O as a performance factor.

The same tests were repeated for the following two scenarios:

Scenario 1: Database set to READ_WRITEScenario 2: Database set to READ_ONLY

Since we are interested only in the relative performance difference instead of absolute performance numbers, the other details of the test server (e.g. the server model) are less relevant, and I have left them out.

The following chart summarizes the test results:

These tests were repeated many times, and the result pattern was consistent in that at a sufficiently high level of load, the read-only database achieved about 8% higher transaction throughput than did the read-write database.

Three observations are worth noting. First, looking at the usual performance metrics such as perfmon counters, waits, and DMVs, I didn't find any direct way to unmistakably attribute the performance difference in the transaction throughput to the fact that no locking occurs in the read-only database.

The second observation is that although the difference in the transaction throughput is consistent and significant, it is less telling if we look at the transaction response time. For instance, at the 200-user load level, the average response time of the Stock Level transaction was 26 milliseconds with the read-write database and 24~25 milliseconds with the read-only database. I would not feel comfortable to draw any conclusion based on the response time alone.

Finally, at the lower load levels (e.g. with users 5~50), the performance difference between the read-only database and the read-write database was not significant. For instance, with 50 users, the transaction throughput was ~2921/sec with the read-write database, compared to about ~3039/sec with the read-only database.

No, I did test different isolation levels. Note that the test queries were read only so hopefully the ioslation level shouldn't matter.

I didn't have any wait time between the calls. The calls were basically being fired to the server non-stop. So with more users than the number of cores (16 in this case), the CPUs were driven to 100% regardless whether the database was set to read only or not. This was a bit extreme, but was done on purpose.

I was focusing on whether setting a database to Read Only would have any impact on the transaction throughput of a specific workload instead of the impact of different isolation levels. But the question you raised makes me wonder whether the impact of a Read-Only database is significantly influenced by the isolation level setting. For the tests that produced the data points in the chart, the isolation level was fixed to Read Committed. I'll check what, for instance, Repeatable Read may bring. Thanks!

I was doing similar test using Quest BenchMark Factory tool, but between READ COMMITTED SNAPSHOT ON and OFF settings. My test shows that with READ COMMITTED SNAPSHOT ON, the Transactions / Sec flat lines after 20 users, where as with this setting OFF, the number increases linearly. I ramped up users upto 100 users.

The test runs pure SELECT statement, so there is no row version traversing. I am trying to get a reasonable explanation for this behavior but can't find anything.