According to AWS Documentation The first time a DB instance is started and accesses an area of disk for the first time, the process can take longer than all subsequent accesses to the same disk area. This is known as the “first touch penalty.” Once an area of disk has incurred the first touch penalty, that area of disk does not incur the penalty again for the life of the instance, even if the DB instance is rebooted, restarted, or the DB instance class changes. Note that a DB instance created from a snapshot, a point-in-time restore, or a read replica is a new instance and does incur this first touch penalty.
Reference : http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html.
I captured number of cached pages per database on our SQL Server RDS Instance and rebooted the instance and captured cached pages again. Based on the documentation, the cached pages should be available and shouldn’t be affected by Reboot process. Its such a neat feature which makes life lot easier for DBA’s to respond to unexpected situations. But I noticed significant differences between the number of cached pages before and after reboot.
Cached Pages Before and After SQL Server RDS Reboot
DBName | Bef_Buf_Pages | Size_MB | Aft_Buf_Pages | Size_MB |
DatabaseOne | 558482 | 4363 | 596 | 4 |
DatabaseTwo | 1017 | 7 | 487 | 3 |
DatabaseThree | 609 | 4 | 201 | 1 |
master | 190 | 1 | 107 | 0 |
model | 18 | 0 | 37 | 0 |
msdb | 882 | 6 | 284 | 2 |
rdsadmin | 1253 | 9 | 87 | 0 |
DatabaseFour | 133 | 1 | 59 | 0 |
Resource Database | 1877 | 14 | 319 | 2 |
tempdb | 779280 | 6088 | 123 | 0 |
For DatabaseOne , Cached Pages dropped from 558482 to 596. I am not sure whether others have encountered the same issue. Not sure what to think of the First Touch Penalty Promise to keep the cache intact. Maybe its not true for SQL Server RDS. 🙂