Buffer Pool Extension (BPE) in SQL 2014 - A brief introduction

What is Buffer Pool (BP)?

The Buffer Pool (also known as the buffer cache) is a shared memory area which consists of data/index pages. A buffer page is 8KB in size and the Max Server Memory determines the total size of the Buffer Pool. For example, when we set max server memory=16GB, it means we are allocating 16GB of memory to the Buffer Pool. The plan cache is also the part of the Buffer Pool.

What is Buffer Pool Extension (BPE)?

This is a new feature in SQL 2014 where we can extend the Buffer Pool beyond the limitation of the maximum physical memory. This means that we can use a persistent disk file system as part of the buffer. The file can be placed in any disk including the network shared folder.

Suitability of BPE:

The primary goal of BPE implementation is to improve Read performance of an OLTP application where physical memory is less than 64GB (according MSDN blogs). The application which is more READ intensive and less WRITE prone, implementing this feature will enhance I/O performance greatly. However, this feature is not suitable for data warehouse and WRITE intensive type workload.

Where to place the file?

The SSD provides very low random and read access latency, thus the best place to put the BPE file in a SSD. If it is placed in a normal disk system, there will be a negative impact on I/O.

BPE size:

The BPE size can be 32 times greater than Max Server’s Memory. So if we set Max Server Memory to 4 GB, then BPE can be 4X32 GB = 128 GB. Microsoft recommends 4 to 8 times of the amount of Max Server Memory which will be an optimal size for overall I/O performance.

What will be written in a BPE file?

The BPE file only holds “clean pages” in it. A “clean page” (aka un-modified page) is a data or index page which is exact and equivalent to a disk page residing in the data file. A BPE file will not hold any “dirty pages” at any time.

Good to know and good practices:

Before taking advantage of the features of BPE, we should be sure that adding extra physical memory is not an option due to hardware limitation. In addition to this, we should have optimal T-SQL code and we should not have I/O overhead index schema such as overlapping, duplicating and multi-column indexes.

1.      Placing the BPE file in a high performance SSD is recommended.

2.      Make sure the disk alignment is correct.

3.      Consider formatting the disk with 64K block (cluster) size.

4.      Set the Max Server Memory and leave enough memory for the OS.

5.      “Lock pages in Memory” has no interaction with BPE.

6.      Before modifying the BPE, the Buffer Pool Extension feature should be disabled first.

7.      When the BPE is disabled, all related configuration settings are removed automatically and the memory used to support the feature is not reclaimed until the instance of SQL Server is restarted. The Buffer Pool Extension file will be deleted once the instance of SQL Server is shutdown.

8.      BPE can be configured and disabled at any time while the instance of SQL Server is running. If the feature is re-enabled, the memory will be reused without restarting the instance.

9.      As BPE contains only clean pages, data loss is out of question.

How to configure BPE:

Configuring BPE is very straight forward and simple. Following are the steps we need to follow to configure a 16GB BPE file when the Max Server Memory is set to 4GB.

Set the “max server memory” to 4GB:

EXEC sp_configure 'max server memory (MB)', 4096

GO

RECONFIGURE

Configure the Buffer Pool Extention to 16GB:

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION ON

    (FILENAME = 'E:\SQLBuffer\ExtendedBuffer.BUF', SIZE = 16 GB);

To see the configuration, execute following DMV query:

SELECT * FROM   sys.dm_os_buffer_pool_extension_configuration

To turn off the BPE feature, simply execute the following: 

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION OFF

Querying BPE utilization:

The following query can be used to identify utilization of BPE:

SELECT CASE

            WHEN database_id = 32767 THEN 'ms_resource_db'

            ELSE DB_NAME(database_id)

       END       AS database_name,

       COUNT(*)  AS cached_pages_count,

       CONVERT(NUMERIC(25, 4), COUNT(row_count) * 8.00 / 1024.00) AS size_mb,

       CONVERT(

           NUMERIC(25, 4),

           COUNT(row_count) * 8.00 / 1024.00 / 1024.00

       )         AS size_gb

FROM   sys.dm_os_buffer_descriptors

WHERE  is_in_bpool_extension = 1

GROUP BY

       database_id

    

Performance measurement of BPE:

There are some performance counters that have been introduced in SQL 2014 to measure BPE performance. It can be monitored using Windows PerfMon or with T-SQL. Following is the T-SQL query:

SELECT [object_name], counter_name, instance_name, cntr_value

FROM   sys.dm_os_performance_counters

WHERE  [object_name] LIKE '%Buffer Manager%'

       AND [counter_name] LIKE '%Extension%'

References:

Buffer Pool Extension

http://msdn.microsoft.com/en-ca/library/dn133176.aspx

Buffer Pool Extension to SSDs in SQL Server 2014

http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/25/buffer-pool-extension-to-ssds-in-sql-server-2014.aspx

PowerShell Way: Automating SQL Server blocking capture, alert and notification

Capturing SQL Blocking is a challenge. Although there are lot of mechanisms out there to accomplish this task, not all of them are efficient and easy to implement. In an OLTP environment, database blocking is obvious, but excessive blocking reduces transaction throughput severely.  Often blockings become a nightmare and bring the server into a state where normal business activities suffer.

The goal of this PowerShell Script is to capture the blocking when it occurs along with all the details and to notify the concerned group immediately so that an appropriate corrective action can be taken quickly.  This script is lighting fast and very efficient in performing what it is supposed to do. The automation process has been developed by using PowerShell and DMVs. A windows task can be used to execute the script against any SQL Server from edition 2005 to 2014.

Download: http://bit.ly/1cZNScb.

The Script:

The main script (SQLBlocking-V2.ps1) contains a CSS style sheet, and two PowerShell functions:  “Send-EmailToSupport” and “Get-SQLBlocking”. There is one HTML formatting library function (written by Don Jones, MVP, www.powershell.org). At the bottom of the script, the main function“Get-SQLBlocking” has been called by providing different parameters and these are follows:

-Server = SQL Server instance

-CheckEverySeconds = How often script will check for blocking

-DurationToReport = Blocking duration to report.

-RunUntil = at what time it will stop.

How to execute?

We can create a Windows task to run the script. Once the script has started execution, it creates a connection to the database server and keep running in a loop for an allotted time while holding the connection. The script is using Windows Authentication while connecting to SQL Server.

Example:

The “SQLBlocking-V2.ps1” can be run directly from the PowerShell or we can run the script using Windows Tasks or SQL Agent Job. The following example will run for 24 hours checking every 20 seconds for blockings; if there is a blocking that occurs for more than 30 seconds, the script will catch the blocking and send an immediate email to the support group.

Steps:

1.      Create a Windows task from a local or remote machine.

2.      Schedule the task to start at 12:01AM.

3.      Call the “Get-SQLBlocking” as follows.

Get-SQLBlocking -Server 'SHB_MAIN\SQL2012' -CheckEverySeconds 20 -DurationToReport 30000 -RunUntil '11:55:00 PM'

Conclusion:

This article is on how to capture a SQL Server blocking for Alert/Notification purposes and this Automation (PowerShell Script) is well-tested against our heavy duty production server without any deficiencies. I am providing it “AS IS” and I hope that you will find it useful.

PowerShell Way: Automating Golden Gate Replication Monitoring on Windows

Recently I had the opportunity to work with our Tandem Team to implement an Automate Monitoring and Alerting solution while replicating data from Tandem Data source to SQL Server 2012 using Oracle Golden Gate Replication technology. The Golden Gate replication pushes data 24/7 basis to SQL Server and once in a while, the latency increases or sometimes one or more “REPLICAT” stops working. As this is a mission critical system, we need to learn any malfunction almost immediately.

Golden Gate on Windows:

As the GG (Golden Gate) replication sits on Windows Server and runs all its services, it is quite easy to investigate the GG replication status manually. There are sets of commands provided by the GG mechanism to understand the replication configuration, status and the health of each or all REPLICATs. For example, as we are interested on replication status we need to utilize the GG Command “STATUS ALL” to see the “ABENDED”, “STOPPED”, “LAG” or “CHKPT” status of each REPLICAT.

Say for example, the GG Replication is running Windows on D:\GGReplication, and to see the status we need to do the following.

1.      Use CMD.exe and go to the “D:\GGReplication” folder;

2.      Run “GGSCI” to get into the GG replication;

3.      Execute the Command “STATUS ALL” or “INFO ALL”.

PowerShell Way:

There is no direct command to grab status information of the Golden Gate replication. However, we can utilize PowerShell “Invoke-Expression” to perform the above task from inside PowerShell sessions. Following is the PowerShell function I have developed to monitor the Golden Gate replication from PowerShell.

PowerShell Function to monitor Golden Gate Replication:

<####################################

# Golden Gate Status Monitor

# Developed: Sarjen Haque

#####################################>

function Get-Replicat

{

     $string = "CMD /c echo Status All | D:\GGReplication\GGSCI"

     $result = Invoke-Expression $String

     $raw = $result -match 'REPLICAT'

    

     [StringSplitOptions]$Options = "RemoveEmptyEntries"

    

     # loop through each line and break

     foreach ($line in $raw)

     {

           $wrd = $line.Split(" ", $Options)

           $lg = $wrd[3].Split(":")

           $tm = $wrd[4].Split(":")

                    

           $result = [Ordered]@{

                    "Program" = $wrd[0];

                    "Status" = $wrd[1];

                    "Name" = $wrd[2];

                    "LagMin" = [int]$lg[0] * 60 + [int]$lg[1];

                    "Lag" = $wrd[3];

                    "ChkPt" = $wrd[4];

                    "ChkPtMin" = [int]$tm[0] * 60 + [int]$tm[1];

           }

           $obj = New-Object -TypeName PSObject -Property $result

           Write-Output $obj

          

     }

}

We have our PowerShell function, now we can use this function in various way to collect the Golden Gate replication status.  Following are some examples:

Example #1: Collect all “REPLICAT” status

Get-Replicat | ft -AutoSize

Example #2: Collect “REPLICAT” status if the LAG is greater than 15 minutes or if a REPLICAT is “ABENDED”

Get-Replicat | where-object { $_.LagMin -ge 15 -or $_.Status -eq 'ABENDED' }|ft -AutoSize

Example #3: Collect “REPLICAT” status if “ABENDED”

Get-Replicat | where-object { $_.Status -eq 'ABENDED' }|ft -AutoSize

Example #4: Collect “REPLICAT” status if stopped

Get-Replicat | where-object { $_.Status -eq 'STOPPED' }|ft -AutoSize

Automating Golden Gate Process Monitoring:

By utilizing the above “Get-Replicat” function, we can easily automate the process monitoring and send alerts if a performance condition exists based on provided criteria. A Windows Schedule task can be created to execute that PowerShell script every x minutes to check the “REPLICAT” status.

You can download this entire scripted Golden Gate monitoring solution on Windows from my shared Dropbox folder. The output of the script is similar to the one below.

Steps to follow:

1. Download the script from http://bit.ly/1cZNScb.
2. Create or import a windows task (provided along with the script).
3. Change the Location of the GG binaries location in the function “Get-Replicat”.
4. Change the smtp server name and email address for mailing functionality.

Conclusion: I have used one my favorite PowerShell Guru’s (Don Jones, MVP, www.powershell.org) library function for HTML reporting. I believe that someone will benefit from this simple function. Let me know if you find it useful.

SQL Server installation and initial configuration – points to consider



The journey starts with the SQL Server installation. Installing a SQL Server is key for a stabilized and healthy database system. A properly installed and configured SQL Server rarely shows any issues. So let’s get started.

Service Account recommendation:

Although a single DOMAIN\ACCOUNT can be used for the SQL Server and the SQL Agent service, it is not desirable and not recommended as both services have different execution context. Use separate domain accounts for each service.

Installation recommendation:

If a DOMAIN\ACCOUNT is used for the SQL Server service account, then

(a)   Add the DOMAIN\ACCOUNT to the local administrator group.

(b)   Log on to the intended server with this account (optional).

(c)    Run the SQL Server setup executable as “run as administrator” privileges.

(d)   Installation media can be resided in a network share or in a DVD.

Once installation is done, you need to remove the SQL Server account from the local admin group; this is recommended for security reasons. However, please note that you may still prefer the SQL Server Service account to be a part of the local admin group to avoid permission related issues.

Local policy rights to SQL Server Service Account:

SQL Server service account rights varies from edition to edition. Do not assign any local policy rights to SQL Server and SQL Agent service account in advance which are not recommended. Each edition of the SQL Server installation process will determine the required local policy rights and will be added automatically during the installation of SQL Server. To validate appropriate service rights, review the MSDN recommendations from the following link:

http://msdn.microsoft.com/en-us/library/ms143504.aspx

Windows consideration:

(a)   Network Binding order:

1.      Make sure that the domain network is the first bound network. The miss-ordering issue usually reports in SQL Server fail-over cluster installation.

2.      Usually, TCP/IP will be the most used protocol. Consider changing the order of “network protocol bindings” to make network communications faster.

(b)   Disk alignment and block size:

1.      NTFS Allocation Unit (formerly known as cluster size) must be 64K. When formatting the partition that will be used for SQL Server data files, it is recommended that you use a 64-KB allocation unit size for data, logs, and tempdb.

2.      Ensure that Volume Alignment (also known as disk, partition or sector alignment) has been set correctly. Review and align the sector to 1024KB (or 2048KB max) before formatting the disk.

3.      HBA Disk Queue Depth: In the SAN, a queue depth of 64 is generally accepted as a good starting point in the absence of any specific vendor recommendations.

(c)    Windows Power Policy option: In Windows 2008, there are three power consumption options (power plan), where “Balanced” is set to default. Make sure that the “High Performance” has been selected. To make “power plan” configuration effective, the server BIOS power management needs to be changed to “OS Control” mode.

(d)   TCP Chimney setting: Disable the TCP Chimney offload in Windows level. “TCP Chimney Offloads” functionality does not work with virtualized Windows OS (VMware and hyper-v), therefore it can be disabled permanently.

(e)   SQL Server Port: Create an appropriate inbound rule in Windows firewall for SQL Server ports (e.g. 1433, 5022) instead of disabling the firewall service.

Instant file initialization: To take advantage of the instant file initialization feature, assign the “Perform volume maintenance tasks” windows right to the SQL Server Service Account. When it is enabled, the data file (mdf and ndf, but not ldf) will be initialized instantaneously without filling any empty spaces with zeros.

Tempdb consideration: It is not necessary to create multiple tempdb data files without understanding the workload nature and concurrency in a Server. If the OLTP server experiences high concurrencies, latch contention, row versioning, a high number of sort/hash operations, then adding multiple data files will be helpful.

(a)   In a SMP based system, 8 tempdb data files can be added initially. If more data files are required, then add two tempdb data files at a time.

(b)   In a NUMA based system, add data files based on the number of NUMA nodes detected by the SQL Server Engine. For a 4-NUMA system, the tempdb might have 8 or 12 data files. If more data files are needed, then increase the tempdb data files by the number of NUMA nodes.

(c)    Make sure that all tempdb data files are equal in size.

(d)   Tempdb data files and log must be isolated and resided in a single dedicated drive. Log files of tempdb can be placed in the Log drive, but it is not necessary.

MAXDOP: The default value of MAXDOP is 0 (zero). It works best for a well written OLTP application and if the database is maintained properly. However, in some workloads, MAXDOP = 1 works best, such as SharePoint database. There is no appropriate settings for MAXDOP and it varies from server to server, and sometimes even in the same server time to time (review http://support.microsoft.com/kb/2806535). Following are some considerations:

(a)   In a SharePoint implementation, it is highly recommended by Microsoft to use MAXDOP=1.

(b)   If parallelism introduces blocking on CXPACKET, lowering the MAXDOP setting may help.

(c)    In a system, if there is an enormous amount of spinlock and latch contention occurring, lowering the MAXDOP value will help to improve the performance.

(d)   Consider increasing “cost threshold for parallelism” to fine tune the parallelism along with or instead of MAXDOP.

(e)   In case of high CAXPACKET, consider redesigning indexes, removing fragmentations and updating statistics instead of changing MAXDOP.

(f)     Please remember that creating or rebuilding an index will suffer from lower values of MAXDOP.

Transaction Log:  Following are the recommendations for the Transaction Log:

(a)   Place the transaction log in a dedicated drive.

(b)   Do not create multiple transaction logs for a single database.

(c)    Pre-size the log file well in advance; use 4G or 8GB increments per growth.

Database Integrity: Make sure that the “Page Verify” option for all databases is set to “CHECKSUM”. Running DBCC CHECKDB in production server is resource intensive and it does not help to protect the database from corruption. Instead of running DBCC CHECKDB, consider configuring SQL Server “Event Alert Service” for database fatal error (Severity from 022 to 025) and get notifications right away.

Memory allocation for SQL Server - points to consider

Memory Allocation:

When we are talking about memory allocation for SQL Server, we usually mean memory allocation for the buffer pool. The two well-known parameters - the “max server memory” and the “min server memory” - always refer to the data buffer only.

These options do not include the memory that is allocated for other components within the SQL Server process. These components include the following:

(a)    The SQL Server worker threads.

(b)   The Multipage Allocator of SQL Server Memory Manager.

(c)    Various DLLs and components that the SQL Server process loads within the address space of the SQL Server process.

(d)   The backup and restore operations.

(e)   SQL Server Agent process.

Windows 2003 vs. Windows 2008/2008R2/2012:

In Windows 2003, OS manages its memory aggressively and always tries to free up physical memory; this generally introduces “paging out” issues in SQL Server. Windows 2008 and later versions are more educated and can manage memory dynamically and non-intrusively. Therefore a SQL Server running on a Windows 2008 or later edition has better experience and needs extra consideration for configuring SQL Server memory.

Windows 32-bit vs. 64-bit:

Windows 32-bit edition can’t use more than 4GB of physical memory while 64-bit Windows has varying limitations based on the edition.

(a)    In a 32-bit Windows Server, the /PAE switch is required to access more than 4GB of physical memory if installed. Enabling AWE in SQL Server is also required.

(b)   In a 64-bit environment, /PAE and /AWE are not required to be configured.

PAE and AWE Switch (physical memory <= 4GB):

If a 32-bit environment has less than or equal to 4GB of memory, the /3GB switch can be used to allocate more memory to the SQL Server. Once a /3GB switch is used, the total virtual memory will be splitting since1GB is for kernel mode and 3GB is for user mode usages.

PAE and AWE Switch (physical memory > 4GB):

In a 32-bit Windows Server and SQL Server implementation, if more than 4GB of memory is installed then both the PAE in Windows and AWE in SQL Server need to be turned on. In a 32-bit physical environment, the following configurations must be configured should the physical memory be used beyond 4GB.

(a)    LPIM – Assign this local right to the SQL Server service account.

(b)   Max Server Memory – use a fixed amount of memory for SQL Server.

(c)    /PAE – Enable switch in Windows if it is not done yet.

(d)   /AWE – Enable this switch in SQL Server.

If this is a Virtual implementation, then the provisioned Guest Memory must be reserved in VMWare ESXi or in MS Hyper-V to prevent memory swapping (balloon driver effect).

Memory configuration in 32-bit environment:

Follow the guidelines below when configuring memory for 32-bit SQL Server in a 32-bit environment.

Physical RAM	AWE	Boot.ini	Maximum “Max Server Memory”
4 GB	0 (Disabled)	/3GB	Dynamic (default)
8 GB	1 (Enabled)	/3GB, /PAE	6.5 GB
16 GB	1 (Enabled)	/3GB, /PAE	14.5 GB
32 GB	1 (Enabled)	/PAE	29.5 GB

Max Server Memory Allocation Guideline:

Although Microsoft recommends using dynamic memory configuration in a 64-bit environment, this recommendation does not consider suboptimal SQL database design, and other executing applications and processes. So the “Max Server Memory” setting is strongly recommended to limit the buffer pool of SQL Server. Following is the guideline for configuring Max server memory in a 64-bit SQL Server. Always consider leaving some additional memory for Windows and other applications based on workload.

Physical Memory	Minimum Memory for Windows	Maximum “Max SQL Server Memory”
<16	2	Dynamic / best judgment
16	4	12
32	6	26
64	8	56
128	16	112
256	16	240
512	32	480
1TB	48	976

Max and Min Server memory:

Consider allocating an equal amount of memory to the “Min Server memory” and “Max Server Memory” option. This will eliminate internal page allocation and de-allocation overhead.

NUMA and Memory Configuration:

When SQL Server detects NUMA in a physical or virtual server, it calculates memory allocation from “Max Server Memory” and assigns an equal amount of memory to each NUMA node. In case of dynamic memory configuration, SQL Server Engine assumes an arbitrary value which may not be sufficient for the current workload or over estimation may introduce OS memory issues.

Please note that SQL Server 2000 SP4 and later editions support NUMA memory.

Lock Pages in Memory (LPIM) and SQL Server Editions:

By default, Enterprise and Developer 64-bit editions of SQL Server 2005 and later versions support Locked Pages.

(a)    The standard edition of SQL 2005, SQL 2008 and SQL 2008 R2 support Lock Pages in Memory if the startup trace flag 845 is set.

(b)   The standard edition of SQL 2012 and SQL 2014 supports Lock Pages in Memory natively and does not require a 845 startup trace flag.

(c)    All 32-bit versions support Lock Pages in Memory with /PAE and /AWE switch.

Lock Pages in Memory (LPIM):

In 64-bit Windows 2008 or later editions, LPIM is not required as Windows manages memory dynamically in a non-intrusive way. However, certain workloads may need a generous allocation of guaranteed fixed memory to operate smoothly. In this case, LPIM can be assigned explicitly to the SQL Server Service account.

Following is the guideline for LPIM:

(a)    When Windows sits on a physical Machine:

(1)    Make sure that the “Max Server Memory” has been assigned.

(2)    For standard editions of SQL Server, add startup trace flag 845.

(b)   When Windows sits on a virtual Machine:

(1)    Make sure that the “Max Server Memory” has been assigned.

(2)    For standard editions, add startup trace flag 845.

(3)    Make sure that the provisioned guest memory has reservation in VMWare or in Hyper-V.

The trace flag 845 has no effect on the SQL Server Enterprise edition.

Index Creation Memory:

The ‘index creation memory’ option is self-configuring and usually works without requiring adjustment. A larger value might be helpful to improve index creation performance but there is no specific recommended value. Please note that the run value for this parameter will not exceed the actual amount of memory that can be used for the operating system and hardware platform on which SQL Server is running. On 32-bit operating systems, the run value will be less than 3 GB.

The run value needs to be equal or greater than the “Min memory per query” when set.

Min memory per query:

The default value of 1024 KB works well for almost all cases. If some queries experience excessive sort and hash operation, and if optimizing the query and refactoring associated indexes out of scope then this value can be bumped up gradually to improve performance. If changing the setting helps then consider adding more physical memory and allocating more memory to the data buffer pool. The maximum limit is 2GB for this setting.

-g Switch (mostly applicable for SQL 2000 and 2005):

The –g switch reserves additional memory outside SQL Server’s buffer pool in the SQL Server process for extended stored procedures, executing distributed queries, loading DLLs of extended stored procedures and calling OLE automation objects from Transact-SQL. Consider using this switch if the following message is logged in the Error Log.

WARNING: Failed to reserve <n> bytes of contiguous memory

WARNING: Failed to reserve contiguous memory of Size= <allocation_size>

More reading:

http://www.vmware.com/files/pdf/perf-vsphere-memory_management.pdf

http://support.microsoft.com/kb/918483

http://blogs.technet.com/b/beatrice/archive/2008/11/26/x64-lockpages-in-memory-and-awe-should-i-or-should-i-not.aspx