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 setup I used is as follows:
- The SQL Server 2005 instance (build 9.00.3042) was allocated 12GB of physical memory to its buffer pool.
- The test database was about 9GB in size.
- 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_WRITE
Scenario 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.