How your Standard can become AWEsome

Having tried to make a fun play on words to illustrate that for Standard Editions of SQL Server 2005/2008 since the releases of these Cumulative Updates:

 SQL 2005 SP3 & CU4 / SQL 2008 SP1 & CU2 we can make real use of AWE!

Since (Mid 2009) when these CU’s where released, the ability to make use of required privilege “locking-pages-in-memory” which previously was only available in Enterprise Edition, allowing us to make use of those AWE APIs for resolving working set trim issues that resulted in non-optimum performance.

So naturally this raises a few questions That I would like to find some answers for:

What impact +/- will this have on my databases performance? (Clearly that’s not an easy question to answer succinctly or with any great accuracy), I will need to find some discreet questions that will allow me to determine its impact +/- on the databases performance with this functionality.

Some questions I have are:

·         What measure(s) would best indicate the impact?

·         What Trace flag(s) are required?

I’m going to try to answer those and others during the next few days/weeks and will post my findings on this as I find them, It’s not that new (it’s been around for ~9 months now), but none the less it should be able to provide some decent improvements if its implemented correctly.

 

Published 21 March 2010 15:41 by NeilHambly

Comments

21 March 2010 16:45 by NeilHambly

# re: How your Standard can become AWEsome

Forgot to ADD the following link: support.microsoft.com/.../918483

Also thier had been some good blogs already posted on this subject, so if you intrested in this just run some searches.

24 March 2010 22:45 by NeilHambly

# re: How your Standard can become AWEsome

Ah the joys of DBA work.. when a simple CU install turns ugly

After saying it had completed successfully, SQL wouldn't then start !!.

Lot's of the following in the Error log

FCB::Open: Operating system error 5(error not found) occurred while creating or opening file ....

a few quick tests later, decided to revert using -T3608 to get Service started, some switching accounts, folder permissions , databases offline / online..

Finally coaxed it into submission and all is now fine, see following in Logs

Using locked pages for buffer pool

(~Phew.. now what's next on the list)

07 April 2010 08:05 by GrumpyOldDBA

# re: How your Standard can become AWEsome

I find that many fail to fully understand the impact of lock pages in memory. Assuming that you have configured memory correctly on your server if memory is paged away from SQL Server then something else is using it - locking pages to deny memory will likely cause the next request for memory to use the page file which may have a detrimental effect on performance. My understanding is that lock pages only affects buffer cache - allocate too much to this and you run the risk of starving other processes. I run a full x64 enterprise system without lock pages and have no issues, I may at some point enable lock pages but for now I'm content to observe.