Increasing Buffer Pool in SQL Server 2014

Problem

Under heavy load your SQL Server may not work as intended due to lack of resources. To handle this situation SQL Server 2014 gives us the possibility to use an SSD or Ram device to dynamically increase the Buffer Pool. Keep reading to see how you can take advantage of this new wonderful feature.

Solution

Every day in the life of a DBA is a challenge. You don’t know what you will face until you are seated at your desk. Sometimes there are nightly ETL processes that didn’t run and you have to re-run them during work hours when the database is under more pressure.

As another example, suppose that you are a DBA at a company like Amazon. It is expected that this kind of business has seasonal demand. For example, before holidays such as Mother’s Day, Christmas, etc. there will be more transactions than on normal days.

Now, considering Murphy’s Law who states “anything that can go wrong will go wrong”; just imagine that all of this happens at the same time. Will you be ready?

What is the SQL Server Buffer Pool?

Starting from square one, if you are new to SQL Server you may be asking yourself:  What is the buffer pool?

To make it simple, the Buffer Pool is the place in system memory that holds data and index pages read from disk. This has two purposes:

  • To modify pages according to INSERT and UPDATE statements. Those pages are marked as “dirty” and are flushed to disk when a checkpoint is performed.
  • To increase response time when retrieving the same data.

Buffer Pool size is determined amongst other things by server memory and the target server memory specified in the “Max Server Memory” parameter. When that threshold is reached and SQL Server needs to read more pages, previous cached pages are discarded.

With this in mind, we can deduce that the purpose of the Buffer Pool is to improve performance by reducing IO operations. As such, the Buffer Pool size can correlate to improved performance during a heavy workload.

Advantages of the SQL Server Buffer Pool on SSDs

Remember that SQL Server is designed to use the maximum available memory regardless of other system processes and that includes other instances you may have on the same server. You don’t want to have an instance using for example, 80% of RAM and leaving the rest to other instances.

Here is when Buffer Pool Extension comes into play. You can limit the instance’s memory by setting “Max Server Memory” parameter and enable Buffer Pool Extension to compensate from a performance perspective.

If you have configured an instance per sever also you can take advantage of this feature by enabling Buffer Pool Extension during heavy load without restarting the instance.

How does the SQL Server Buffer Pool Extension work?

This feature uses a Flash or DRAM memory device like an SSD disk as a cache to the Buffer Pool by creating a file on disk. At first I thought that the disk would be accessed as a RAW device but no, it has to be formatted like any other Windows drive. Considering this, I noticed that even a standard disk can be used to enable Buffer Pool Extension, but this won’t be as useful from a performance perspective.

Since enabling this feature creates a file and relies on the file system, for best benefits you should use a non-fragmented drive so Windows doesn’t split IO requests. You can monitor this with the following performance counter: “\LogicalDisk\Split IO/sec”.

Microsoft Recommendations for the SQL Server Buffer Pool Extension

According to Books Online you can set the Buffer Pool Extension size up to 32 times the size of RAM, but they recommend adjusting its value in concordance with the “Max Server Memory” parameter. They say that a ratio of 1:16 or less, I mean 16 times “Max Server Memory” size would be enough. But even a lower ratio could be beneficial.

Amongst their recommendations is the proper testing prior to implementation on production environments and to avoid performing configuration changes to the file.

Steps to Enable the SQL Server Buffer Pool Extension

Now I will guide you though the process of setting up the SQL Server Buffer Pool Extension.

First, as Microsoft recommends we should check the value of “Max Server Memory” parameter.

USE master
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'max server memory (MB)'
GO

/*
EXEC sp_configure 'max server memory (MB)', [VALUE (MB)]
RECONFIGURE WITH OVERRIDE
GO

*/

Finally here is the script to enable Buffer Pool Extension. I created a 10GB file in this example, but you can change this value as needed.

USE master 
GO

ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION ON
    (FILENAME = 'E:\DB_Cache\SQL2014.BPE', SIZE = 10 GB);
GO

In order to specify a filename for the Buffer Pool Extension we must ensure that the path exists, otherwise you will face the following error:

Incorrect Path

Disable the SQL Server 2014 Buffer Pool Extension

To disable this feature, simply execute the following script.

USE master
GO

ALTER SERVER CONFIGURATION 
    SET BUFFER POOL EXTENSION OFF;
GO

Altering the SQL Server 2014 Buffer Pool Extension Size

In order to modify Buffer Pool Extension Size, first you need to disable and then re-enable this option with its new size.

USE master
GO

ALTER SERVER CONFIGURATION 
    SET BUFFER POOL EXTENSION OFF;
GO

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
    (FILENAME = 'F:\SSDCACHE\Example.BPE', SIZE = 10 GB);
GO
Next Steps
Advertisements

Find The Last Restart of SQL Server

Problem

How can I tell when my SQL Server instance was last started?  In this tip, we look at various methods of how to get the start time for a SQL Server instance.

Solution

Here are a few different ways that you can tell when SQL Server was last started.


Method 1: sys.dm_os_sys_info

This DMV, sys.dm_os_sys_info, contains a column that holds the startup time for SQL Server. You can run the following command to find the start time.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

sys.dm_os_sys_info


Method 2: SQL Server Error Log

If you open the current SQL Server error log in SSMS under Management > SQL Server Logs and scroll to the bottom of the error log, you can see when the server was started. Note the highlighted text below “SQL Server is starting”.  You will want to look for this to make sure the error log was not cycled and give you a false impression of the start time.

sql server error log start up time

Here is an example of a cycled error log and note the highlighted text.   If you see this you will need to look at a previous error log until you find the log that contains the information in the image above.

sql server log initialized

Note: if you are running the Express edition of SQL Server you cannot read the error logs directly from SSMS, but you can use xp_readerrorlog or navigate to the folder where the log files are stored and use a text editor to read the SQL Server error log files.


Method 3: Dashboard Reports

Another option is to use the SQL Server Dashboard Report.  Right click on the server name in SSMS and select Reports > Server Reports > Server Dashboard and you will get a report similar to the following.

sql server dashboard report


Method 4: Windows Event Viewer

Another option is to use Windows Event Viewer to look for the startup time.  If you open Windows Event Viewer and filter your Event Sources (in our case MSSQL$SQL2008) for the SQL Server instance and Event ID 17162 you can find all of the startup times that are still in the Event Viewer log.

windows event viewer start time

Here is a partial listing of the startup times for this instance.

windows event viewer sql startup log

Next Steps
  • Hopefully, this information comes in handy the next time you want to find out when SQL Server was started.
  • I think the DMV is the easiest way, but this wasn’t introduced until SQL Server 2008.  Also, the Dashboard Report is probably new for SQL 2008 as well.
  • The error log method can be used for pretty much any version of SQL Server.

Share a Folder on Mac OS X

Share a Folder on Mac OS X

You’ll need to enable network file sharing to share folders on your Mac. Open System Preferences by clicking the Apple logo and selecting System Preferences. Click the Sharing icon and enable File Sharing. Click the Options button here and ensure “Share files and folders using SMB” is enabled.

Use the Shared Folders column to choose additional folders to share. Use the Users column to choose which users and groups can access and write to them.

share-folder-with-windows-from-mac-os-x

Access a Shared Folder from Mac OS X

Open the Finder, click Go on the menu at the top of the screen, and select Connect to Server. Enter the following address, replacing COMPUTERNAME with the Windows computer’s name: smb://COMPUTERNAME. You can also enter the other computer’s local IP address instead of its name.

directly-connect-to-windows-file-share-on-a-mac

You’ll be prompted to authenticate with the appropriate credentials or log in as a guest. After you’ve connected, the computer will appear under the Shared column in the Finder’s sidebar.

To automatically connect to the shared folder each time you log in, open the System Preferences window and navigate to Users and Groups > Login Items. Drag and drop the network share from under the Shared column in Finder to the list of Login Items.

windows-shared-folder-on-mac

Share a Folder on Linux

Use your desktop’s file manager to share a folder on Linux. We used the Nautilus file manager on Ubuntu 14.04 here, but the process should be similar with other file managers.

Open the file manager, right-click a folder you want to share, and select Properties. Click the Local Network Share tab and enable sharing for that folder. If this is the first time you’ve enabled sharing, you’ll be prompted to download and install the Samba software — this happens automatically when you provide your password.

Configure your sharing settings after installing the Samba software — be sure to click the Create Share button to start sharing the folder.

share-folder-from-ubuntu-linux-with-windows-pc-over-local-network

Access a Shared Folder from Linux

Your Linux deksotp’s file manager probably includes a network browser you can use to locate and access shared folders on the local network.

Click the Browse Network option in the file manager’s sidebar. You can then double-click the Windows Network option, double-click your workgroup (WORKGROUP by default), and double-click a nearby computer to view its shared files.

browse-windows-network-shares-on-ubuntu

To connect directly to a computer, select the Connect to Server option in Nautilus instead and enter the path to the remote computer like so: smb://COMPUTERNAME

directly-connect-to-windows-shared-folder-on-ubuntu


However you connect, you may need to authenticate with a user account name and password that has access to the files on the remote machine. This depends on whether you enabled guest access and how you set up your folder sharing permissions.

“This message could not be sent” NDR when an approved user sends mail Exchange

PROBLEM

When a user who has Send As permission to a distribution group tries to send an email message to other users by using Microsoft Outlook in Office 365, the user receives a non-delivery report (NDR) that contains the following error message:

This message could not be sent. Try sending the message again later, or contact your network administrator.
Error is [0x80070005-00000000-00000000]

CAUSE

This issue may occur if the address book in Outlook isn’t updated.

SOLUTION

To resolve this issue, follow these steps:

  1. Update the address book in Outlook. To do this, follow these steps.
    1. On the Office ribbon, click the Send/Receive tab, click the down arrow next to Send/Receive Groups, and then click Download Address Book.

      Screen shot of the Download Address Book command on the Send/Receive tab of the Office ribbon in Outlook

    2. In the Offline Address Book dialog box, make sure that the Download changes since last Send/Receive check box is selected and that Offline Global Address List is selected as the address book, and then click OK.

      Screen shot of the Offline Address Book dialog box, showing the

    3. Test to see whether the issue is resolved by sending an email message as the distribution group. When you create the message, use the global address list (GAL) to populate the From field.

      If doing this doesn’t resolve the issue, go to step 2.

  2. Delete all files and folders in the Offline Address Books folder. To do this, follow these steps:
    1. Exit Outlook.
    2. Locate the Offline Address Books folder. In Windows 8 and Windows 7, the folder is located along the following path:
      \Users\<UserName>\AppData\Local\Microsoft\Outlook\Offline Address Books

      Screen shot of Windows Explorer, showing the path to the Offline Address Books folder

      Note The folder may be hidden. To unhide it, click Start, type folder options in the search box, click Folder Options, click the View tab, click Show hidden files, folders, and drives, and then click OK.

    3. Delete the contents of the Offline Address Books folder. (The contents of the folder will be re-created when you restart Outlook).
    4. Start Outlook, and then test to see whether the issue is resolved by sending an email message as the distribution group.

Add multiple sites to the Enterprise Mode site list using a file and the Enterprise Mode Site List Manager (schema v.2)

Applies to:

  • Windows 10
  • Windows 8.1
  • Windows 7

You can add multiple sites to your Enterprise Mode site list by creating a custom text (TXT) or Extensible Markup Language (XML) file of problematic sites and then adding it in the Bulk add from file area of the Enterprise Mode Site List Manager (schema v.2). You can only add specific URLs, not Internet or Intranet Zones.

To add your websites one at a time, see Add sites to the Enterprise Mode site list using the Enterprise Mode Site List Manager (schema v.2).

Create an Enterprise Mode site list (TXT) file

You can create and use a custom text file with the Enterprise Mode Site List Manager (schema v.2) to add multiple sites to your Enterprise Mode site list at the same time.

Important:
This text file is only lets you add multiple sites at the same time. You can’t use this file to deploy Enterprise Mode into your company.

You must separate each site using commas or carriage returns. For example:

microsoft.com, bing.com, bing.com/images

-OR-

microsoft.com
bing.com
bing.com/images

Create an Enterprise Mode site list (XML) file using the v.2 version of the Enterprise Mode schema

You can create and use a custom XML file with the Enterprise Mode Site List Manager (schema v.2) to add multiple sites to your Enterprise Mode site list at the same time.

Each XML file must include:

  • site-list version number. This number must be incremented with each version of the Enterprise Mode site list, letting Internet Explorer know whether the list is new. Approximately 65 seconds after Internet Explorer 11 starts, it compares your site list version to the stored version number. If your file has a higher number, the newer version is loaded.

    Important
    After this check, IE11 won’t look for an updated list again until you restart the browser.

  • <compat-mode> tag. This tag specifies what compatibility setting are used for specific sites or domains.
  • <open-in> tag. This tag specifies what browser opens for each sites or domain.

Enterprise Mode v.2 XML schema example

The following is an example of what your XML file should look like when you’re done adding your sites. For more info about how to create your XML file, see Enterprise Mode schema v.2 guidance.

In the above example, the following is true:

  • cpandl.com, as the main domain, must use IE8 Enterprise Mode. However, bing.com/images must use IE7 Enterprise Mode.
  • contoso.com, and all of its domain paths, can use the default compatibility mode for the site.

To make sure your site list is up-to-date; wait 65 seconds after opening IE and then check that the CurrentVersion value in the HKEY\CURRENT\USER\Software\Microsoft\Internet Explorer\Main\EnterpriseMode\ registry key matches the version number in your file.

Important
If CurrentVersion is not set or is wrong, it means that the XML parsing failed. This can mean that the XML file isn’t there, that there are access problems, or that the XML file format is wrong. Don’t manually change the CurrentVersionregistry setting. You must make your changes to your site list and then update the list using the import function in the Enterprise Mode Site List Manager (schema v.2).

Add multiple sites to the Enterprise Mode Site List Manager (schema v.2)

After you create your .xml or .txt file, you can bulk add the sites to the Enterprise Mode Site List Manager (schema v.2).

To add multiple sites

  1. In the Enterprise Mode Site List Manager (schema v.2), click Bulk add from file.
  2. Go to your site list (either .txt or .xml) to add the included sites to the tool, and then click Open.

    Each site is validated and if successful, added to the global site list when you click OK to close the menu. If a site doesn’t pass validation, you can try to fix the issues or pick the site and click Add to list to ignore the validation problem. For more information about fixing validation problems, see Fix validation problems using the Enterprise Mode Site List Manager.

  3. Click OK to close the Bulk add sites to the list menu.
  4. On the File menu, click Save to XML, and save your file.

    You can save the file locally or to a network share. However, you must make sure you deploy it to the location specified in your registry key. For more information about the registry key, see Turn on Enterprise Mode and use a site list.

Next steps

After you’ve added all of your sites to the tool and saved the file to XML, you can configure the rest of the Enterprise Mode functionality to use it. You can also turn Enterprise Mode on locally, so your users have the option to use Enterprise Mode on individual websites from the Tools menu. For more information, see Turn on local control and logging for Enterprise Mode.

Guide: How to force specific sites to always run in Compatibility View using Group Policy

If you have specific sites which do not work in newer versions of Internet Explorer, you can force them to always run in Compatibility View, meaning that they will run as if they were run in Internet Explorer 7. There are GPO settings to force specific IE Zones to always run in Compatibility View, but here’s how you set it for specific sites.

Open Group Policy Management

Group Policy Management GPMC

Create or edit the GPO that will be used to force the new setting.

To create a new GPO right-click the container Group Policy Objects and select New.

New GPO object

New GPO object

To edit an existing GPO, select it, right-click and press Edit.

Edit GPO

Browse to User Configuration -> Policies -> Administrative Templates -> Windows Components -> Internet Explorer -> Compatibility View and select the setting “Use Policy List of Internet Explorer 7 sites”

Edit Group Policy  (GPO) Setting

Enable the setting and then press show

Enable Group Policy setting - GPO

Add the site that you want to force to run in Compatibility View, in this case http://www.contoso.com

Add a site to Group Policy setting - Compatibility view GPO

The new policy should now work after the user has either logged off and logged in again or if the command gpupdate /force is entered in CMD.

dump is being generated

WARNING: An unexpected error has occurred and a Watson dump is being generated: Failed to find the mailbox.

In this post I’m going to address a very common problem many new Exchange 2010 Administrators face.

When running commands such as Test-OutlookWebServices on an Exchange 2010 CAS you will receive the following error:

[PS] C:\>Test-OutlookWebServices
WARNING: An unexpected error has occurred and a Watson dump is being generated: Failed to find the mailbox. Mailbox =’extest_0f9a03d82a6d4@destination.local’.
Failed to find the mailbox. Mailbox = ‘extest_0f9a03d82a6d4@destination.local’.
+ CategoryInfo : NotSpecified: (:) [Test-OutlookWebServices], MailboxNotFoundException
+ FullyQualifiedErrorId : Microsoft.Exchange.Monitoring.MailboxNotFoundException,Microsoft.Exchange.Management.SystemConfigurationTasks.TestOutlookWebServicesTask

This is probally the first time you have run the Test command isn’t it? In Exchange 2010 you need a test mailbox to perform tests with. This can be created using the new-TestCasConnectivityUser.ps1 powershell script Microsoft provides us with Exchange 2010.

All you need to do is navigate to the Scripts directory:

C:\Program Files\Microsoft\Exchange Server\V14\Scripts

Then run .\new-TestCasConnectivityUser.ps1

Provide the test mailbox a password when prompted…

Easy?