Part 2 Buffer pool extension SQL 2014

In this second part (read the first part here) of the series we continue testing the limits of our RAM and try to understand SQLs behaviour when it encounters memory pressure. To this end we have created a table that is little more than 2 GB in size and have set out SQL server buffer pool to be 2 GB in size.

Once I start fetching data from the table notice how the RAM in the buffer pool is utilized.

Buffer Pool                                         Value

—————————————-             ———–

Database                                             244050

Simulated                                           44800

Target                                                   524288

Dirty                                      8344

In IO                                      0

Latched                                                0

Page Life Expectancy                                                      77

Also notice how in the resource manager of the OS disk activity is now happening on tempdb as well.

This is an indication that SQL is running out of memory and having to store intermediate results in the tempdb. A good indicator for memory pressure is the low value of Page life expectancy. Also you will notice that the OS page file also gets used significantly as a result. This indicates that when SQL runs out of memory it turns to the next best thing the page file and tempdb. Both these files reside on disk and as a result any read / write IO on these files are subject to disk latency which typically is around 5 to 15 ms.

Enter the game changing SSD. Typical read/write latency is in the range of .3ms- 1ms!!!!

I remember reading a blog few years back were a DBA predicted that soon all companies will start storing their data in SSD which are excellent for Random IO compared to traditional disks. With buffer pool extension I think we are finally at the stage where databases can now move to faster and more reliable storage hardware. In the next post I will explain how to setup buffer pool extension and how it will improve IO performance.