Published on January 18th, 2015 | by Alejandro Cordero0
SQL Server 2014 New Feature: Buffer Pool Extensions – Dynamic allocation
Do you know if it’s possible to have the BPE size be dynamic? For instance, I have VM’s that I want to scale down over the weekend, but SQL won’t start because the newly allocated amount of RAM throw’s off the “rules”
This was a very good question, that much that I decided to write about this in detail, so Matt thanks for the post idea!. As Warner replied is not possible to do this out of the box, basically SQL Server does not provide the ability to dynamically adjust the buffer pool. This is by design, adding a buffer pool have it´s implications on performance, so I guess Microsoft want the DBA to manually change this value according to business needs and characteristics of the system, which can vary so much from one server to the other.
So before I get deeper on how to do solve this puzzle, please take into account this disclaimer: the suggested solution is an initial script and haven´t being tested on every possible scenario, like any automation process, it will work beautifully until an scenario that you didn´t think of happens. If you are going to use it please test first.
Having said that lets start creating a solution!
First Step – Myth busting!
I want to clear out a myth, about SQL Server not being able to start due to the memory vrs bpe file size ratio, SQL Server is smart enough that when a BPE size restriction is reached, then it will just won´t complicate and disable BPE. So if you want to scale down a virtual machine over the weekend, there is actually no problem about that, if you actually get into a situation that the restriction (BPE needs to be larger that the allocated max memory) is no good, then SQL Server will disable your BPE and start SQL Services with no problem. You will see a message in your logs about this, check snapshot below:
Also if you are scaling it down, meaning removing memory from the VM, most likely you will have no issue and BPE will keep running, if you enabled your BPE in the first place, it should be larger than the current memory, if you actually remove RAM, the BPE file will still be larger and no issues should happen, at least on the availability of the SQL Service. However you can experience a performance degradation, a reminder here from our previous post:
Test the buffer pool extension thoroughly before implementing in a production environment. Once in production, avoid making configuration changes to the file or turning the feature off. These activities may have a negative impact on server performance because the buffer pool is significantly reduced in size when the feature is disabled. When disabled, the memory used to support the feature is not reclaimed until the instance of SQL Server is restarted. However, if the feature is re-enabled, the memory will be reused without restarting the instance.
VERY IMPORTANT NOTE!
After SQL Server startup, the time you setup a BPE it will also be considered as the minimum size allowed, so if you start with 4GB of BPE, then scale up to 5GB, you can NOT go back to 4GB, it will tell you that now the new minimum is 5GB. So if you ever want to decrease the size of your BPE, you will need to restart SQL Server first, after startup the minimum will be considered the MAX value in “max server memory (MB)” parameter.
See example below
Second Step – so What happens if you scale UP your VM?
This is an interesting scenario, if you do scale up your VM and your max memory is larger than the BPE file, then BPE will be disabled, if you are interested to automate a process that will be triggered on start-up and will check your BPE status, if it is disabled, then enable it in such way that BPE will be available, you can accomplish this with regular TSQL code. For this we will need to calculate the current memory amount and then create a BPE larger than that. Remember, your BPE should be pointing to a high performance device, so the main restriction here, is how much free space do we have to increase our BPE?
Third Step – The Code! [made with fresh ingredients… yum]
Create a stored procedure that will analyze your current max server memory, based on that it will calculate BPE size, it will multiply Ratio * current memory. So if you desire to have a 1:4 ratio, just change the value @ratio to 4. Also make sure to specify the BPE path in @BPEpath. The sproc will analyze 3 scenarios:
1-the new BPE was created successfully
2-The new BPE failed because there was a a BPE specified before, you need to use the same value or larger than that.
3-The new BPE can’t fit inside the disk, not enough disk space, then send an alert
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Alejandro Cordero -- Create date: Jan 18th 2015 -- Description: Dynamic BPE allocation -- ============================================= Create PROCEDURE usp_dynamic_bpe_allocation AS BEGIN SET NOCOUNT ON; Declare @BPEStatus int Declare @stringSize int Declare @MaxPhysicalServerMemory int Declare @MaxSQLMemory int Declare @ratio int Declare @FinalBPESize int Declare @BPEpath varchar(4000) Declare @command nvarchar(4000) DECLARE @ParmDefinition nvarchar(500); DECLARE @TheErrorMsg varchar(4000) Declare @MailSubject varchar(2000) set @MailSubject='BPE was not enabled at server'+@@SERVERNAME set @ratio=1 --- Best Practice is to set the ratio with 4, 8, 16, depending on your system set @BPEpath='C:\backup\SQL2014_newFeature.BPE' ----check if your BPE is running, if it is running then nothing else is needed ----if not, then enable it back, given the runtime parameters select @BPEStatus=state from sys.dm_os_buffer_pool_extension_configuration IF(@BPEStatus=0) BEGIN ---SELECT @MaxPhysicalServerMemory=physical_memory_kb/1024 FROM sys.dm_os_sys_info exec sp_configure 'show advanced options',1 reconfigure Declare @Configs table ( value varchar(2000), minimum int, maximum int, config_value int, run_value int ) Insert into @Configs exec sp_configure select @MaxSQLMemory=run_value from @Configs where value ='max server memory (MB)' set @[email protected][email protected] set @command=' BEGIN TRY ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = [email protected]+''', SIZE = '+convert(varchar(10),@FinalBPESize)+' MB); END TRY BEGIN CATCH select @ErrorMsg=ERROR_MESSAGE() END CATCH; ' SET @ParmDefinition = [email protected] nvarchar(4000) OUTPUT'; --PRINT @command EXECUTE sp_executesql [email protected] ,@ParmDefinition ,@ErrorMsg = @TheErrorMsg OUTPUT; ----There was a BPE enabled previously which is greater than the server max memory, set BPE size as per this new value IF(CHARINDEX ( 'Buffer pool extension size must be larger than the current memory allocation threshold' ,@TheErrorMsg,0)>0) BEGIN set @stringSize=CHARINDEX( 'MB',@TheErrorMsg,CHARINDEX( 'threshold',@TheErrorMsg,0))-(CHARINDEX( 'threshold',@TheErrorMsg,0)+9) select @FinalBPESize=convert(int,RTRIM(LTRIM(SUBSTRING (@TheErrorMsg,CHARINDEX( 'threshold',@TheErrorMsg,0)+9,@stringSize)))) set @command='ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = [email protected]+''', SIZE = '+convert(varchar(10),@FinalBPESize)+' MB);' --print @command exec (@command) END ----There is not enough space to allocate the BPE with the given ratio IF(CHARINDEX ( 'Attempting to allocate' ,@TheErrorMsg,0)>0 and CHARINDEX ( 'BUF for buffer pool extension for a maximum of' ,@TheErrorMsg,0)>0) BEGIN ---Send alert BPE was not enabled EXEC msdb.dbo.sp_send_dbmail [email protected]_name = 'user', [email protected] = [email protected]', [email protected] = @MailSubject, [email protected] = @MailSubject ; END END END GO
Fourth Step – Make it run from startup
In SQL 2014 you can configure a stored procedure so it will be executed on start-up with sp_procoption. To use it you will need to run below code:
EXEC sp_configure 'show advanced option', '1'; RECONFIGURE EXEC sp_configure 'scan for startup procs', '1'; RECONFIGURE exec sp_procoption N'usp_dynamic_bpe_allocation', 'startup', 'on'
Check the current BPE status with below query
select * from sys.dm_os_buffer_pool_extension_configuration
You can automate your BPE creation, there are lots of ways and parameters to take into account. If you decide to go this route please make sure you know this won’t impact your server performance. I would personally think to use this on development, staging, QA, environments. This is an initial solution, what else can you add to this script to make it even better? if you like the solution, please sign up into our newsletter to get the latest scripts and articles about SQL Server technologies.