SQL 2014 Standard Edition Improvements

Good News Everyone! SQL Server 2014 Standard Edition has new features for 2014.

1. Increase in supported RAM to 128GB.

2. Addition of Buffer Pool Extension (BPE). Imagine BPE as a warm cache for the SQL Server buffer pool. As clean pages are aged out of the buffer pool due to memory pressure, having to re-read them from storage causes an IO. Many servers still have spinning disk supporting them be it internal drives, direct attached storage, SAN or network attached storage, an IO can become an expensive operation. BPE will allow pages to be aged out from the buffer pool to a BPE file on SSD or Flash, thus creating a warm pool.

I will say, it is possible to create a BPE file on non SSD or Flash storage, SQL does not check, so it is possible to degrade the performance of your system by creating your BPE file on slow storage, in short, don’t do that. BPE is only clean pages, so if the server loses power or goes off line for any reason, the BPE file will be cleared but no data is lost since these were all clean pages that exists on disk.

A SQL Server Standard Edition BPE file can be up to 4X the amount of RAM allocated to the SQL Server using Max Server Memory.

3. Addition of Delayed Durability. Delayed durability is essentially a lazy commit. There is a lot to consider when deciding to use this. The books online cover all the scenarios, but in a paragraph here are the things you need to know;

Delayed Durability can be implemented at the Database level, or the transaction level. Writes to the log are asynchronous, that means your transaction can complete before the log record has been hardened. This means more concurrent log IOs and less contention on the database log file, so if you are waiting on WRITELOG delayed durability may help. However, your transaction has completed before your data has been hardened to disk, this means that you could suffer from data loss for any uncommitted data during a catastrophic event such as power loss. So, think very carefully before you implement this. Please review the section “When can I lose data” before implementing this feature at the database level or the transaction level.

Check the books online before implementing this and review the section on Delayed Durability and other SQL Server features. For Standard Edition log backup, log shipping, transactional replication, crash recovery all may have behavior changes once delayed durability is enabled.

4. The Cardinality Estimator in Standard Edition has been updated as well, see my prior blog on the new CE for more details.