Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Hunting and Gathering

By Rodney Landrum,

"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.

Total article views: 110 | Views in the last 30 days: 1
 
Related Articles
FORUM

sql server memory question

sql server memory question

FORUM

new 64-bit Sql 2005 Server - integrity check completes then memory spikes 75% and doesnt lower

new 64-bit Sql 2005 Server - integrity check completes then memory spikes 75% and doesnt lower

FORUM

64-bit topped out at 4 GB used

SQL Server 2005 std 64-bit on WinSrvEnt 2003 R2 64-bit is not using installed memory

FORUM

New Memory Issues on new 64-bit Server

After moving from 32-bit with 4GB ram to 64-bit with 32GB ram, I'm running into memory pressure?!

FORUM

How much memory should be allocated to SQL Server ?

How much memory should be allocated to SQL Server ?

Tags
editorial    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones