Database Administration buffer page extension

Published on January 18th, 2015 | by Alejandro Cordero

0

SQL Server 2014 New Feature: Buffer Pool Extensions – Dynamic allocation

In a previous post we have discussed about the new sql server feature called buffer pool extensions. A reader Matt sends a very interesting question

 

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'[email protected]@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]*@ratio

            
                    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 = N'@ErrorMsg nvarchar(4000) OUTPUT';

                    
                    --PRINT @command
                    EXECUTE sp_executesql
                            @command
                            ,@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
                        @profile_name = 'user',
                        @recipients = '[email protected]',
                        @body = @MailSubject,
                        @subject = @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

Conclusion

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.

 


About the Author

SQL Server DBA with over 8 years of experience, developer for .Net , Certified Scrum Master and Entrepreneur.



Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Back to Top ↑
  • Sign Up For Our Newsletter

    Stay up to date on the latest from SQL Server products and Features.

    Sign Up Now

    Sign up for mailing list and receive new posts directly to your inbox.

    We promise never to share or sell any of your personal information.

  • Recent Posts

  • Categories

  • Archives

  • Tags

  • Topics

  • Subscribe to our RSS Feed


    sqlturbo.com A SQL Server Web Blog
    Canada, Ontario, Ottawa.
    http://sqlturbo.com

    The Authors