April 9, 2014 at 10:33 am
A short piece on what the BPE is in SQL Server 2014 and how to implement it.
April 9, 2014 at 10:40 am
Steve Jones - SSC Editor (4/9/2014)
A short piece on what the BPE is in SQL Server 2014 and how to implement it.
Haven't started looking at SQL Server 2014 so I have no idea what BPE even is.
April 11, 2014 at 3:18 am
Buffer Pool Extension is a really useful feature of SQL2014 that can reduce most IO latency, but it does have a few foibles.
Implementing it is easy but you need to put it on a SSD drive or similar. To get it going use the following command with your own file name and size:
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'D:\SQLFiles\BPE\BPE200GB.BPE',SIZE = 200GB)
In our installation we have been using BPE since February 2014 using CTP2 on our Production BI servers. We are seeing the BPE file take about 90% of the read IO for our BI servers, and we see the SSD peaking at just over 70MB/sec IO. It has made a big improvement to performance. We have SQL Server set to use 45GB max memory and a 400GB BPE file.
There are some things to beware of if you use a BPE:
a) You can turn the BPE feature on or off without stopping SQL Server
b) You can increase the BPE file size (via turning BPE off and on) at any time, but you need a SQL restart to reduce the BPE file size
c) If you turn BPE off this operation can take some time due to flushing pages out of the BPE
d) If you have enough memory, it is better to let SQL Server use this for its main bufferpool - do not be tempted to set up a RAM disk for BPE
e) If you do a TRUNCATE TABLE, this operation will take longer than before due to flushing pages out of the BPE
f) If you do a database restore, this operation will take longer than before due to flushing pages out of the BPE
g) If you are log shipping to a database, this operation will be affected by flushing pages out of the BPE. We think this is a bug and are gathering some details to raise a Connect item on this
In summary, a BPE can give a significant performance boost to a server that is a bit memory-constrained but has some SSD space. However, it is not a pure good news story and you need to be aware of some performance hits in unexpected areas.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 11, 2014 at 8:51 am
You guys are killing me.
This forum isn't for general questions or discussion. It's a call for articles. If you want to write one, please feel free to post or ask for clarification. If you aren't writing on the topic, please post elsewhere.
April 11, 2014 at 10:12 am
Steve Jones - SSC Editor (4/11/2014)
You guys are killing me.This forum isn't for general questions or discussion. It's a call for articles. If you want to write one, please feel free to post or ask for clarification. If you aren't writing on the topic, please post elsewhere.
Well, it does provide some background for someone who knows nothing. Good place to start for researching such an article. FYI, one of my pet peeves is the use of acronyms without telling people what the acronym means.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply