"Hey, I got a question for ya..."

I work with many different teams, all utilizing SQL Server technologies in one way or another, and I hear this phrase at least 4 times a day. The question that follows is generally a high-level enquiry regarding "best practices" or "what ifs" or "How can we make it faster" and I can usually respond in kind with a satisfactory answer, “you should be using config files for SSIS packages” or "have you tried switching if off and on again" (the last one is a joke, of course).

Inevitably, though, questions arise to which I do not have a definitive answer. Fortunately, when dumbfounded, stumped or stymied, I can turn to Google, Bing or BOL. Instead of DBAs, we become hunter-gatherers, stealthily picking fruit from unknown trees. But how do we always know what is edible and what is poisonous? How can we be sure that information, posted anonymously, counter pointed anonymously, and corrected semi-officially before finally being recanted, is a good and trustworthy source?

A while ago, I started working with 64-bit SQL Server in earnest and needed some advice on the best memory configuration for SQL Server on each platform, 32 and 64 bit. Specifically, I wanted a definitive guide to how options and variables such as AWE Enabled, Lock Pages in Memory, /3Gb, /PAE switches, and Min and Max Server memory, should be configured differently between SQL Server editions and platforms. Almost immediately, I struck gold in BOL: Memory Architecture

The table therein contains many useful nuggets. It confirms, for example that, for 64 bit SQL Server 2005, the AWE Enabled option is present but ignored. There was a lot of good advice here, but I still had a few questions on AWE and the /PAE switch, and also on possible different behavior on the various Windows Editions. So, I turn to Google, and a search returns 43 pages of links and on the first page is a link to Lock Pages in Memory for x64 SQL Server. It essentially summarizes the advice from the BOL reference but also links to two other articles on Lock Pages in Memory and 64 bit SQL Server. The first one is a Microsoft KB article that temporarily stops me in my tracks with talk of a potential issue with paging out of the buffer pool in 64-bit versions. That does not sound good.

The second recounts Tony Rogerson's experience with this issue, Using 64 bit SQL Server 2005? Lock Pages in memory! In the ensuing discussion, it is suggested that BOL might be wrong about a detail regarding AWE usage when Lock Pages in Memory is turned on. WAIT! Books Online is wrong?! Unfortunately, the discussion doesn't always make it clear which editions are being referred to, and I find myself suddenly unsure whether AWE really is ignored in 64-bit SQL Server Enterprise Edition.

A bit more research, and wading through interesting comments about configurations people have tried, with mixed results, and I stumble across Why "lock pages in memory" may increase I/O performance, which states stridently, "You all know that the AWE API is used to lock pages in memory and this is true for 32-bit as well as 64-bit SQL Server installations"

At this point, I just wanted to scream out the acronym phonetically, "AAAAWWWWWEEEEE!!"

I am now unsure if memory is dynamically allocated or not, if I should enable AWE on 32 SQL Server 2005 Standard when the OS only had 4G available, or whether I should set the Min and Mac server memory values to the same number. I foresaw a few long weekends stretching ahead of me, digging into SQL Server Memory internals, before daring to change any settings at all. I took some solace in a quote from "Joe" on one of the SQL newsgroups:

"I have seen a positive impact by enabling AWE and lock pages in memory in SQL Server 2005 STD edition. Perhaps it is the placebo effect."

I uncovered many other posts during my search that I've not listed here, but the general pattern should be familiar to many DBAs. It leads many of them to dismiss any advice that is not on BOL, or does not come directly from an MS engineer. Personally, though, I would not give up the wealth of knowledge that exists out there in the wilds. If people did not take the time to ask and answer questions, we would have to learn everything by trial-and-error, and that is time consuming and frustrating. Amongst the millions of news groups, blogs, articles, tweets, instructional videos, workbenches, tips, tricks and cheat sheets there is contradiction, misdirection and and uncertainty. But somewhere amongst the sprawl is exactly what you seek. So, keep posting Anonymous! And I will keep filtering through grinning, scratching my head and nodding. However, if anyone wants to share any good "hunter-gatherer" tips for the DBA, I'd love to hear them.