Lock or not to Lock Pages in Memory, that is the quesiton!


We all are aware of the “standard practice” of enabling the Security Policy “Lock Page in Memory” for your SQL Server service account.  Just about everywhere you read, it is pretty much standard practice, and so like an red-blooded “Accidental DBA”, I have always used it.

Until today…, well at least for one particular machine!

Let me preface this blog post by saying: I can’t prove nor do I know how to prove that my solutions was the answer to the problem; but it hasn’t been a problem since the change.

 

Problem:

I have a physical 32 core, 448 GB RAM, SQL Server 2012 Enterprise Edition, her after referred to as “The Beast”!  Unfortunately the Beast got it’s legs chopped off at the knees.  Sometime ago, my company bought a new SAN for this server and other shared servers as well.  This new SAN, would be 10K drives with loads of storage. The Beast was used to 15K drives which would hide a myriad of query problems. This new SAN also had what the vendor called “tiered storage”.  A SSD that would be refreshed every night with ~100GB of the most used files actually read from the SAN since the previous 24 hour check to allow quick access.  I personally don’t believe this is a wise configuration for a SQL server with multiple MDF files greater than 400 GB, just sayin’! We have disabled this feature for now.

Anyway, since the new SAN went online, every so often, especially on larger READ queries, the SQL Server would crash and burn. OS RAM was maxed out to the point RDP was not accessible, SQL Server would create a SQL Mini dump referencing page latch issues and the only way to revive it was to execute a remote SHUTDOWN /r command.  This went on for a couple of months.

Solution:

Then I read, Jonathan Kehayias’ article Great SQL Server Debates: Lock Pages In Memory.  It is a little old, but it is still a good read.  Now this got me thinking, quite possibly this policy may not be for every server.  So I researched and researched the whole concept of storing data in memory, dirty pages, and everything I could about how the SQL Engine manage memory, drives and data.

So, with the thought process that my SQL server engine was trying to take more memory from the OS and literally strangle the Beast out of any life by consuming and trying to add more data pages to the Buffer and not releasing older pages, I disabled the policy Lock Pages In Memory. 

Since then, my Beast has been happy Smile.  I have noticed more writes to the MDF file in the last three weeks, not a steady increase just bursts here and there.  I am thinking that is SQL writing dirty pages back to disk, which wasn’t happening before. The Buffer cache is being swapped out with data being used vs. old data at a more consistent rate now.

At least that’s my story and I’m sticking to it!

Advertisements

Posted on February 20, 2015, in SQL. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

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

Humor Me a T-SQL Princess

DBAs really do have a sense of humor

Andrea Allred presents RoyalSQL

Bringing happy endings to all your data stories.

IDERA Community

A community site for users of all IDERA product and SQL community members.

Strate SQL

Data Adventures with a Architect

SQL Sanctum

A Join on SQL And Everything Else

Grant Fritchey

Intimidating Databases and Code

rhondastephens

To Catch A Falling Cactus

SQLSwimmer

Swimming through the Sea of SQL

SQL Studies

 Live, Learn, Share

Aunt Kathi's SQL Server Home

Learn SQL Server from your favorite aunt!

Jen's Blog: We Owls

...stuff. Also, things.

Journey to SQL Authority with Pinal Dave

SQL, SQL Server, MySQL, Big Data and NoSQL

Voice of the DBA

Writings from Steve Jones, the Voice of the DBA

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance

%d bloggers like this: