Top 13 SQL Server Mistakes and Missteps – #8 – Failing to Configure for 64-Bit Systems Properly.
A Little Patience
It’s been a long time since I rock and rolled since I addressed this series on common mistakes in SQL Server database administration. When it comes to this topic I’m glad I held off because the recommendations have changed from when I was originally working on this series when it comes to locking pages in memory. But we’ll get to that in a bit.
Thanks for your patience! When we last met on this set of topics we were in the midst of the summer of 2010. The first of my SQL Cruises had yet to take place. The only Kardashian we knew about what the one that got O.J. Simpson off the hook. We still didn’t know how LOST ended.
Now look at us all grown up and full of Kardashians 24 hours a day and four SQL Cruises in our wake. Of course we still have no idea how LOST ended, but that is beside the point.
We’re back though. Still looking at all those little things we tend to do wrong (and you’ll note the use of the pronoun “we” in that statement) when it comes to our daily dalliance with Microsoft SQL Server by either hobby or profession. Sitting at number 8 we come to the topic of 64-bit instances of SQL Server.
64-32-64? Only if She’s R2 5’3″
64 bit environments have become the standard rather than the cutting-edge they were back when I started this series. It only makes sense. SQL Server feeds off of memory like Demi Moore feeds off of discarded aerosol whip cream canisters and the broken and satisfied bodies of men half her age, SQL takes every bit of memory you throw at it and craves more if the usage demands such. Therefore 64-bit environments are an ideal breeding ground for SQL Server instances because this platform is capable of handling larger amounts of RAM found in modern database servers. What do I mean by 64 bit? How does it differ from 32 bit environments?
When discussing the topic, we’re referring to the chipset for the server or workstation and the size of the “chunks” of information that can be processed in one operation by the CPU (32 bits or 64 bits). There are limitations built into how the CPUs and motherboards cooperated to allow for large amounts of installed RAM to be utilized in 32-bit environments. Typically the max amount of RAM that could be addressed without tricks like Address Windowing Extensions (or AWE as it’s commonly called) was in the neighborhood of 2.9 3.5 GB. Using AWE, most later-life 32-bit systems could address up to 64 GB of RAM. In the case of SQL Server however, that additional RAM over the 3GB threshold can only be used for buffer pool – not procedure cache or any of the lesser-well-know caches Ringo Cache, Rutsey Cache, and Every-Who-Drummer-After-Keith-Moon-Cache for instance – and you have all that natty paging slowness to contend with!
64-bit platforms have the capability to handle large amounts of installed RAM in easier and more direct fashion than was an option with 32-bit systems. No need to set up AWE to obtain use of RAM that may climb into the terabyte range.
When I’m 64
What steps need to be handled differently in a 64-bit SQL Server instance than a 32-bit instance? It boils down to memory as you should suspect by now in this post. I break this down into two items you’ll need to (ahem) address…
Setting Min and Max Memory on your SQL Server Instance
It’s important to alter these values from the default regardless of whether you’re operating on a 32 or a 64 bit instance of SQL Server. Let me correct that a bit: it’s important to at least set the Max Server Memory to an acceptable, non-default value regardless of the platform. By default, SQL Server will set this value to use all the installed RAM on a server if given its druthers. My rule-of-thumb is to set this max value as follows:
Total Installed RAM
Less: 2 or 3 GB for O/S and other ancillary applications like anti-virus and backup software
Less: Bad Decision RAM
What, you may ask, is Bad Decision RAM? That’s the RAM you need to allocate for applications installed alongside your SQL Server application files that don’t fall into the category of database support (O/S, backup software, anti-virus programs already mentioned.) These are the applications from independent software vendors (ISVs) that tell you that their application must be installed on the same server as the SQL databases. These are the applications that exist in your environment because of failure of the ISV to either have staff with a decent understanding of distributed computing or a mandate from Management to not take the time and effort required to properly architect and distributed product. These are quite frequently also the ISVs that love to use the SA login for accessing the database. We feel bad for these ISVs. They had a poor upbringing. We try to do our best to re-educate them one at a time, but sometimes a bad seed grows into a Bad Romance we must live with.
Once you have this value set it as your Max Server Memory setting for the instance. If you’re running more than one instance on the server you’ll need to take that into consideration as well, but this discussion assumes one instance per host.
As for Min Server Memory there is debate about setting this value to equal Max Server Memory or to be a more reasonable starting value. I tend to fall into the school of setting it to a reasonable value on larger systems – say a starting (Min) value of 4Gb. On smaller systems where I’m forced to work with installed RAM of only 4GB (do NOT get me started) I typically find myself setting both Min Server Memory and Max Server Memory at 2 GB each and monitoring to see if I even need to take that down to an ungodly 1 GB each.
Locking Pages In Memory
This is the debate and change in policy I mentioned at the beginning of this article. When I was originally writing the first four parts of this series the recommendation was to grant the service account for SQL Server the ability to Lock Pages in Memory via a setting in the Windows local security policy of the same name. This effectively prevents Windows from trimming the SQL Working Set when memory is needed elsewhere. Since then we’ve seen this policy shift to only granting this right if we see evidence of large amounts of buffer being flushed. (You should see something like the following error message in the active SQL Server error log: “A significant part of SQLserver process memory has been paged out…blather…blather…dogs and cats living together…end of the world kind of stuff…“.) Though you still see advocates with some various levels of differing opinions on whether to configure LPIM or not. There are some that believe it’s only an issue on older versions of SQL hosted on Windows 2003 while others state that the issue still occurs on even the latest versions of SQL Server (2008 and 2008R2 at the time of this article) hosted on Windows 2008R2. I still advocate not setting the rights for LPIM to the SQL service account unless you’re seeing signs of the SQL Server Working Set being trimmed.
Keep in mind that by default, Local System is granted the Lock Pages in Memory local policy. This means if you’re using Local System as the service account for SQL Server (please don’t use Local System as the service account for SQL Server) you will have implicitly granted this policy right for the SQL service account.
There is no need on a 64 bit instance of SQL Server to enable AWE to gain access to the installed RAM over the 3GB limit – even if you do by mistake SQL Server will ignore your misdeeds. Also the RAM above the 3GB limit can be utilized by the Procedure Cache and all the other minor caches that make up the full memory footprint for SQL Server; and no, you still have no control over the size of the individual caches. That is simply an internal calculation based upon the amount of RAM allocated via the Min and Max Server Memory settings.
Bob Ward of the Microsoft CSS Team has a great post on all kinds of fun with AWE, PAE, and locking pages in memory and I would be remiss if I didn’t stand on my desk and shout at the top of my lungs READ HIS POST ON THE TOPIC! I would rather point your attention to it here than suffer a visit from the SOPA Police. Likewise, Jonathan Kehayias has a very well-researched article on Simple-Talk about the reasons to set LPIM from the start and without symptoms of working set trimming.
Next up…