I have been doing some testing of a new (virtual) server which is to replace an existing production server. We suspect the current production server is overspec'ed and therefore are tweaking the virtual components of the new server (RAM, CPUs etc) from a low level, up until the performance is suitable to handle the current workload.
Whilst appreciating that the true test of the new server's ability to handle the workload of the existing server is to test that workload against the new server, I did some simple, arbitrary tests on existing and new server to start with out of interest:
- Restore database - read speed
- Backup Database - write speed
- DBCC CHECKDB on a large database - time taken
(the same database backup was used on both servers for all above tests to be consistent)
All the above were in favour of the new server (faster reads and writes, quicker CHECKDB)
The final basic test I did was to test the time taken to perform a simple
SELECT * on one of the largest tables on both a cold cache and a warm cache to get a further indication of read speed.
The code for the test is below which I ran on both servers
USE StackOverflow SET STATISTICS IO, TIME ON CHECKPOINT DBCC DROPCLEANBUFFERS SELECT * FROM Users /* cold cache run */ SELECT * FROM Users /* warm cache run */
The statistics IO is as follows:
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 12 ms. DBCC execution completed. If DBCC printed error messages, contact your system administrator. SQL Server Execution Times: CPU time = 62 ms, elapsed time = 266 ms. (14080580 rows affected) Table 'Users'. Scan count 1, logical reads 250190, physical reads 1, page server reads 0, read-ahead reads 250200, page server read-ahead reads 0, lob logical reads 1387, lob physical reads 58, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. SQL Server Execution Times: CPU time = 24047 ms, elapsed time = 508456 ms. (14080580 rows affected) Table 'Users'. Scan count 1, logical reads 250190, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 1385, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. SQL Server Execution Times: CPU time = 23297 ms, elapsed time = 478627 ms.
The results were similar on both my servers, however the thing that struck me was that the elapsed time was only 30 seconds slower when reading from disk, rather than RAM.
My understanding of why SQL Server reads everything into the buffer pool was that RAM is quicker than disk - in this test, RAM was only 6% quicker.
Based on the above, if my "real life" query is 2000 milliseconds from cache, then reading the data from RAM takes 3200 milliseconds. Arguably, this isn't a noticable difference.
Why then, do we go to the lengths to ensure data and that servers have as much RAM as possible to cache data when it only appears to be 6% faster?
My question is deliberately tongue in cheek as I feel there is something I am overlooking here and I appreciate my test is very simple in nature.
Thoughts on things I am overlooking:
- Disk contention on a busy server is reduced, if data is in RAM
- I appreciate RAM is used for other things in SQL Server rather than just the buffer pool
- SQL Server has presumably had this architecture from the early days when disks were far slower than modern SSDs