Server Administration

Question

Server Administration

Swarndeep

SSCrazy

Points: 2347
More actions
January 7, 2010 at 10:45 pm

#208718

Comments posted to this topic are about the item Server Administration
Swarndeep
http://talksql.blogspot.com

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Andrew G SSChampion Points: 12809 More actions · Answer 1

I selected "nothing" however I've had issues in the past with the default "max server memory" setting being set at 2147483647 MB on 64 bit systems. Setting the max to a level less than the total OS memory has improved performance on a number of boxes.

Muhammad Haseeb Farhan Right there with Babe Points: 728 More actions · Answer 2

Muhammad Haseeb Farhan

Right there with Babe

Points: 728

January 8, 2010 at 2:26 am

#1100749

Hi foxxo

Nice TIP.

Any idea Why this improve performance ??

Dietmar Weickert SSCrazy Points: 2398 More actions · Answer 3

3 GB Switch is supported for 32 bit editions. It tell operating system to allocate 2 GB RAM to OS and 2 GB RAM to other program such as SQL Or Exchange, if 4 GB RAM is installed.

Well, almost. MSDN (http://msdn.microsoft.com/en-us/library/ms791558.aspx) states

On 32-bit versions of Windows, the /3GB parameter enables 4 GT RAM Tuning, a feature that enlarges the user-mode virtual address space to 3 GB and restricts the kernel-mode components to the remaining 1 GB.

Anyway, thanks for the good question. These are parameters considered rather seldomly which may heavily affect the performance of your system.

Best regards,
Dietmar Weickert.

Cliff Jones SSChampion Points: 10547 More actions · Answer 4

foxxo (1/7/2010)
I selected "nothing" however I've had issues in the past with the default "max server memory" setting being set at 2147483647 MB on 64 bit systems. Setting the max to a level less than the total OS memory has improved performance on a number of boxes.

That has been my experience as well. Thanks for bringing that up.

sjimmo SSChampion Points: 11139 More actions · Answer 5

It has been ours aw well. It appears that if one reserves the minimum memory necessary to make the OS happy, and anything else that may be running - then take the rest for SQL Server, things work mor harmoniously. It will also redice to some point some of the system performance issues as it works with what it is told to use. As long as the OS has enough memory to not utilize paging it is happy. This is actually a throwback to the early years when SQL Server was Sybase. After MS took over at the version they did, they made a lot of rule changes to try to distance themselves. MS and I had numerous arguments over best practices and they finally conformed:hehe:

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Cliff Jones SSChampion Points: 10547 More actions · Answer 6

Glenn Berry has a good article on the subject on this site.

http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx

Alex Rosa Mr or Mrs. 500 Points: 562 More actions · Answer 7

yes, the question would be better if talked about: what do you need to do for sql server recognize more than 2GB of memory in an 64bit environment?

to allocate the max memory available on the windows server is a suicide.. :w00t:

---------------------
Alex Rosa
http://www.keep-learning.com/blog

mojo-168709 SSCommitted Points: 1825 More actions · Answer 8

There should be a 4th option: set the maximum Memory SQL can use.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Thanks for the question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

bvbellomo SSChasing Mays Points: 643 More actions · Answer 10

I was correct, but did not like this question. None of the options do anything, so they are all equivalent.

Alex Rosa Mr or Mrs. 500 Points: 562 More actions · Answer 11

actually this was a trick for who don't how 64bit env works.

the first 2 options are valid in 32bit env, and I bet that a lot of people will choice AWE settings 😀

---------------------
Alex Rosa
http://www.keep-learning.com/blog

Swarndeep SSCrazy Points: 2347 More actions · Answer 12

Thanks for all of your comments.

Setting Maximum Memory Parameter is also a good option and nothing against this. However, I personally experienced that Maximum Memory Parameter is most useful when lock pages option has been enabled from Windows.

Thanks

Swarndeep

http://talksql.blogspot.com

Robert Frasca SSCertifiable Points: 6026 More actions · Answer 13

The answer is incorrect. AWE can be used in 64-bit and some people recommend that it should be set because it helps SQL Server manage memory by not allowing the working set to be trimmed. (Note: lock pages in memory must also be set.) This blog entry explains it pretty well.

http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx

"Beliefs" get in the way of learning.

Cliff Jones SSChampion Points: 10547 More actions · Answer 14

Robert Frasca (1/11/2010)
The answer is incorrect. AWE can be used in 64-bit and some people recommend that it should be set because it helps SQL Server manage memory by not allowing the working set to be trimmed. (Note: lock pages in memory must also be set.) This blog entry explains it pretty well.
http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx

I agree with the Lock Pages in memory, not what the question asked, but the article clearly suggests that:

This is why for 64bit systems, you don't need to set the 'awe enabled' option to 1 (it is actually ignored on 64bit systems).