How Important is the buffer pool really?

Question

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

Answer 1

The test query that you ran to measure the difference a cold and warm cache is going to spend most of its execution time waiting on ASYNC_NETWORK_IO. Sending 14 million rows to SSMS likely takes about 450 seconds in your case. You will probably find that the cold cache query takes about twice as long as the warm cache query if you subtract that wait time out.

SQL Server also has various tricks for reducing I/O wait time. One of those trick is read-ahead reads. The type of query that you ran is likely to benefit quite a bit from read-ahead. Nearly all of the I/O was done through the read-ahead mechanism:

read-ahead reads 250200

Not all queries will be able to gain such a large advantage from read-ahead reads. I happened to write a recent blog post that has an example of very poor query performance due to I/O waits. Reproducing the relevant sections:

Executing the stored procedure [with the FAST_FORWARD option] takes about 2 ms on my machine.

...

Regrettably, the code takes 50 seconds to execute on my machine if I remove the FAST_FORWARD option. What is responsible for the dramatic difference in runtime?

...

For the cursor execution, we don’t get read ahead reads for 95% of the I/O needed for the query. Even a sub-ms I/O latency can be painful when you have to do 240000 I/Os with a DOP 1 query. In summary, the FAST_FORWARD cursor is able to use an index to efficiently seek to the 20 matching rows. The cursor with default options does about 15 GB of I/O that’s not eligible for read-ahead reads.

Going back to your system, you could consider issuing a COUNT_BIG(*) against the table. That will reduce the client wait time to nearly nothing and will more directly show you the difference between a warm and cold cache. If you want to test I/O on the new server without the benefit of read-ahead reads you could try running the demo code mentioned in this blog post:

use tempdb;
 
CREATE TABLE #outer (
    ID BIGINT IDENTITY(1, 1) NOT NULL,
    DUMMY BIGINT NOT NULL,
    PRIMARY KEY (ID)
);
INSERT INTO #outer (DUMMY)
SELECT TOP (130000) 0
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
CREATE TABLE #inner (
    ID BIGINT IDENTITY(1, 1) NOT NULL,
    BIG_DUMMY CHAR(5000) NOT NULL,
    PRIMARY KEY (ID)
);
INSERT INTO #inner (BIG_DUMMY)
SELECT TOP (130000) '0'
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
 
SELECT COUNT_BIG(*)
FROM #outer o
WHERE o.DUMMY = 1
OR EXISTS (
    SELECT 1
    FROM #inner i
    WHERE i.ID = o.ID
)
OPTION (MAXDOP 1, QueryRuleOff BuildSpool);

Answer 2

In addition to Joe's post:

Backup and restore doesn't use the Buffer Pool (BP). Checkdb is likely read so much data so potential for cache re-use isn't high.

As for your SELECT: How much data does it read compared to how much you fit in cache?

• If a decent amount of less data, then that data should be in cache and for instance waiting for client to consume the data (ASYNC_NETWORK_IO as Joe mentions) is likely to dwarf the other numbers.
• if the mount is approaching the size of the BP, then re-reading the data will do as much physical IO as the first SELECT.

Also, what is your load pattern on this SQL Server?

• Few queries where each query reads large amounts of data? "DW", "Analytical", "OLAP" or whatever you want to call it.
• Frequent queries, each reading less data (pages in the 10's, 100's, 1000' or 10000's)? "OLTP", if we want to use that term.
• Or something in between.

What you likely want to do if to have a load where you read a realistic amount of data, with a chance for cache-reuse. You can play with max server memory, tune it up and down to tune the amount of data you can have in the BP and based on that see the difference. Run this on both servers, with both a relively high and low max server memory.

Above will take far more time than what you already did. Perhaps a day or two to create the workload. So in the end, it boils down to whether you find it is worth that time.

If you want a starting point with a load that does index searches where you have both wide and narrow tables and also queries with high and low selectivity, then you can use the load I developed for my "does fragmentation matter" blog post series. You would have to tweak it to fit your needs, but it might save you some time: http://sqlblog.karaszi.com/fragmentation-the-final-installment/

Answer 3

For an OLAP workload having a large bufferpool per se may not give improved performance. A 1TB table will never fit in 200GB RAM no matter how you look at it. So some part of such a table will have to be pulled from disk on each scan. But having all that RAM is still useful. With big tables come big hash join probe builds, big sorts, big work space for windowing functions and big everything else. The more of those that stay in memory and don't spill to disk the faster the overall workload will complete.

Your observation about the architectural roots is also valid. Back in the day not only was everything so much slower, there was a lot less of it. Data lived on disk. It was brought into memory to process, and a little was kept around for a little while in case it proved useful. Mostly disk is where data lived. Nowadays a respectable OLTP server has memory comparable to the size of the DB. Data lives in memory. Disk is only there for backup, in case power fails. The old architecture does not well serve the new hardware. Michael Stonebreaker has many reasoned, opinionated articles on this topic.