SQL Server – v2012 – In-Memory Database and Max Server Memory

Background

I upgraded one of my Lab Databases from v2008-R2 to v2012 a day or so ago.

As I started a Performance Test run against the database I noticed that I could no longer connect to the In-Memory database.

The SQL Instance itself was online, but just the lone database that has the In-Memory table.

 

Reviewed MS SQL Server Error Log

Review MS SQL Server Error Log and noticed quite a few helpful relevant error lines.

 

Error Log Entries
[INFO] The SQL Server service does not have the SE_MANAGE_VOLUME_NAME privilege. Memory optimized checkpoint file operations may be slower, resulting in significant performance degradation.
Disallowing page allocations for database ‘DBLabInMemory’ due to insufficient memory in the resource pool ‘default’. See ‘http://go.microsoft.com/fwlink/?LinkId=330673’ for more information.
Failed allocate page due to database memory pressure: FAIL_PAGE_ALLOCATION 8
[ERROR] Database ID: [42] ‘DBLabInMemory’. Failed to redo log record at LSN 000000A2:00003940:0005. Error code: 0x83000000. (e:\sql12_main_t\sql\ntdbms\hekaton\sqlhost\sqlmin\hkhostdb.cpp : 1996 – ‘RecoverHkDatabaseApplyTailOfTheLog’)

 

 

Remediation Steps

 

Using Local Policy, Grant SE_MANAGE_VOLUME_NAME

What is Service Account?

Launched Services Applet and determine which Account SQL Server is “running as”:

ServiceAccount

 

… and it is “NT Service\MSSQLSERVER”.

 

Review Local Security Policies

LocalSecurityPolicy

 

We are interested in the following Policies:

  • Lock pages in memory
  • Perform volume maintenance tasks

 

Grant permissions to Service Account

 

Looked on the Net for how to grant Local Policies via Command Line, and found good from who else Kendra Little:

http://www.littlekendra.com/2009/11/12/automating-sql-local-security-policy-rights-posh-and-ntrights/

I do not even have to vent, as I know Kendra is smart and she shares the truth.

 

Command Sample:

ntrights -u "NT Service\MSSQLSERVER" +r SeLockMemoryPrivilege

ntrights -u "NT Service\MSSQLSERVER" +r SeManageVolumePrivilege

Output:

ntrights -u "NT Service\MSSQLSERVER" +r SeLockMemoryPrivilege
     Granting SeLockMemoryPrivilege to NT Service\MSSQLSERVER ... successful

ntrights -u "NT Service\MSSQLSERVER" +r SeManageVolumePrivilege
    Granting SeManageVolumePrivilege to NT Service\MSSQLSERVER ... successful

… restart MS SQL Server and dependent services.

net stop mssqlserver /y
net start mssqlserver 

 

Changed Microsoft SQL Server – Max Server Memory

As I am not yet quite yet ready to dig into resource pool allocation and management, I took the easy route per reviewing if I have in place a “max server memory”.  And, I do at a measly 400 MB.

As we are now using in-memory, I need at minimum enough memory to cover the in-memory infrastructure and database objects.

Let us start @ 1 GB.

exec sp_configure 'max server memory (MB)', 1000
go

reconfigure
go

 

Brought Database Online

ALTER DATABASE [DBLabInMemory] SET ONLINE;
go

 

 

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s