Warren Peace (8/6/2009)
So are you also saying that letting SQL dynamically self allocate RAM pages/addresses on a per needed bassis more efficient than telling SQL this is your starting huge pool of memory but you can grab up to 2 GB more if needed?
SQL 2005 does not pre-allocate the minimum memory. All the minimum memory setting controls is the memory amount that SQL will not drop below, once it has reached it. That's why Jeffrey's saying that min memory is unnecessary on a dedicated server.
Segment of original post: "I installed windows 2003 enterprise edition 64 bit ". This is what I was referring to. Should've more specific. Personally I thought that this was strictly an OS function and not a SQL function. It is only a setting in the OS and there is nothing in SQL to set this. One would think that if the credentials SQL was running on would still lock. Can you elaborate in this? I would like to know as I have never heard of this.
SQL Standard edition cannot use the lock pages in memory feature. It's immaterial that it's an OS setting, the SQL engine cannot and will not use locked pages in anything other than Enterprise edition.
Google for Locked Pages SQL Standard Edition, you should find a wealth of resources that explain how it's (not) used.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability