Write latecy increased when changed SQL Server to Enterprise on AWS We have been using AWS - Rds SQL Server 2016 Standard Edition with db.m4.4xlarge db instance (16 CPU, 64GB mem). Today, we needed to upgrade db instance to a large capacity machine, (db.r3.8xlarge - 32 CPU, 244GB mem). 

To do so:


we had to take snapshot of the db instance
restored the snapshot as a new db instance with that large instance.


We have done everything perfectly. After we upgrade the instance, we realised that sql queries are really really slow. I know its because the buffer cache and plan cache cleared bcs of this upgrade, but this is really a bad situation for a production db. Every single query, when first executed, reads from disk, not from buffer. We have thousands of queries coming from tens of applications, so should I need to wait for our latency goes down?
I know its because the buffer cache and plan cache cleared bcs of this upgrade


SQL Server is not my specialty, so I'll assume those things are true, but there's probably an even more significant issue involved, any time you create an RDS instance from a snapshot.

RDS is backed by EBS,&sup1; and EBS snapshots are backed by S3.  Instances spin up quickly because they don't wait for the entire snapshot to be loaded into S3 onto the volume.  All of the data is logically on the volume but not yet physically on the volume -- accessible, but not instantaneously accessible. It's lazily loaded in the background.  Reads of data that have not yet been loaded onto the volume have to first be read from S3 -- so you have a first-touch penalty when reading from the disk, any time the disk block being read is read for the first time.  It is not well-documented whether EBS is only reactive with the background loading, or would also go ahead and restore everything from S3 even for a volume not being accessed.

Either way, as you can imagine, this is suboptimal for a busy database.

The volume should eventually warm up and performance should rise to the level you expect, but the timing of this warmup is not documented.  

Anecdotally, I have observed it to be in the 5 to 10 MiB/s range... or 18 to 36 GiB/hour... however, these results may have been subject to the observer effect, since I was deliberately trying to stimulate the recovery with sequential reads, not random reads.

A solution I have created for MySQL, which I recognize is not directly useful to you but may give you an idea, iterates all the tables and indexes, writing and running queries that attempt to scan each table and each index (writing queries that trigger the optimizer to use the index as a covering index), thus forcing them all to be read from disk, warming it up as quickly as possible.  The generated queries look similar to this:

SELECT DISTINCT LEFT(MD5(CONCAT(c1,c2,c3, ... cn)),1) FROM t1;


A query in this form will read all the rows from the table, but returns a result set of 16 (0-9, a-f) or 17 (+null) rows.  The resultset is unimportant, but passing the concatenated data to MD5() ensures no shortcuts for the optimizer -- a full table scan is forced.

See Initializing EBS Volumes for a description of the snapshot problem, but note that the techniques for warmup here won't help you since RDS does not provide access to the command line of the server.

See also this reddit discussion quoting AWS support which appears to confirm that RDS snapshots are indeed EBS snapshots, thus prone to this condition.

It'a also possible that you could hasten this warmup process by making a database backup by some native mechanism -- not an RDS shapshot.



&sup1; RDS is backed by EBS -- The probable exception to this is Aurora, which uses a proprietary storage system to create the replicated "Aurora cluster volume," which apparently doesn't use EBS.