August 6, 2009 at 5:08 pm
Somehow we found a way to obtain a server with 8 cpu's and 16 gig of ram. I installed windows 2003 enterprise edition 64 bit and sql server 2005 std edition 64 bit. What's the best way to allocate the memory and CPU resources on this blessing? Thanks in advance.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
August 6, 2009 at 5:18 pm
Because you installed x64 - all you really need to do is set the max memory for SQL Server. Since you installed the standard edition, you probably should not set it any higher than 12GB as there are issues when you allocate more.
There is going to be a post SP3 hotfix available soon that will allow Standard Edition the ability to lock pages in memory. When you have that - then you could increase memory above 12GB to 14GB and grant the lock pages in memory right to the account running SQL Server.
On x64 - if you don't set the max memory SQL Server will consume all memory on the server and starve the OS (eventually, unless your system never needs all of the memory).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 6, 2009 at 5:31 pm
Carve out in SQL to start at 12GB and max out at 14GB. Then set your locked pages policy to the account SQL is running under. Make sure to put a check in the check box Use AWE to allocate memory in the "Memory" page and in the "Processors" page put a check box in the "Boost SQL Server priority".
In some cases you may need to use the /PAE in the boot.ini. They say it's not needed but I found in some cases, even on Ent version SQL won't address upper memory.
Remember, you can't see the memory usage in "Task Manager". If you really want to see the memory SQL is using run this...
SELECT
--Amount of physical memory on server
physical_memory_in_bytes
, physical_memory_in_bytes / 1024 / 1024.00 as physical_memory_in_MB
,virtual_memory_in_bytes
, virtual_memory_in_bytes / 1024 / 1024.00 as virtual_memory_in_MB
--Committed physical memory in buffer pool
--Does not include MemToLeave memory area
,bpool_committed AS 'Number of 8KB buffers in buffer pool'
, bpool_committed * 8 / 1024.00 as bpool_committed_in_MB
, bpool_commit_target AS 'Number of 8KB buffers needed by the buffer pool'
, bpool_commit_target * 8 / 1024.00 as bpool_commit_target_in_MB
,CASE
WHEN bpool_commit_target > bpool_committed THEN 'Extra memory needed from OS for Buffer Pool'
WHEN bpool_commit_target < bpool_committed THEN 'Memory may be released from Buffer Pool to OS'
END AS 'Status of Dynamic Memory'
, bpool_visible AS 'Number of 8KB Buffers in Buffer Pool that are directly accessible in the processes VAS.'
, bpool_visible * 8 / 1024.00 as bpool_directly_accessible_VAS_in_MB
FROM sys.dm_os_sys_info
August 6, 2009 at 7:36 pm
Warren Peace (8/6/2009)
Carve out in SQL to start at 12GB and max out at 14GB. Then set your locked pages policy to the account SQL is running under. Make sure to put a check in the check box Use AWE to allocate memory in the "Memory" page and in the "Processors" page put a check box in the "Boost SQL Server priority".
First, AWE is not an option on x64 hardware because it is not needed. You don't have to check the box to enable it - it is not recognized on x64 systems.
Second, for a dedicated database server there is no reason to set a minimum memory amount. Setting the minimum to 12GB and the maximum to 14GB running SQL Server 2005 Standard Edition (x64) will almost certainly cause memory pressure on the system.
Third, SQL Server 2005 Standard Edition (x64) cannot lock pages in memory. Setting the policy is a good idea, but it will not make any difference using this edition. There is a patch on the way that will change this behavior but it is not out yet.
And finally, DO NOT 'Boost SQL Server Priority' unless you are working directly with MS (PSS Engineers). And only then if you are addressing a specific issue with a specific version of an application that has been tested and shown to correct a problem.
Again, using SQL Server 2005 Standard Edition (x64) you don't want to set a max memory greater than 12GB. You can try upping that to 13GB but be prepared to monitor the server for memory pressure.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 6, 2009 at 9:39 pm
First, AWE is not an option on x64 hardware because it is not needed. You don't have to check the box to enable it - it is not recognized on x64 systems.
Yes, there is an option build a Win x64 server with SQL x64. It's even available in SQL 2008 x64. Heres the setting of a win2008 x64 with SQL x64:
http://207.231.94.134/x64AWE.jpg
If it's there and selectable, one has to ask the question why is it even there if you say it's not. Do you know something we dont? If so let us know.
Second, for a dedicated database server there is no reason to set a minimum memory amount. Setting the minimum to 12GB and the maximum to 14GB running SQL Server 2005 Standard Edition (x64) will almost certainly cause memory pressure on the system.
Exactly what memory pressures are you referring to? Other OS Apps? If it's just a dedicated DB server there's nothing more that should be running. 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?
Third, SQL Server 2005 Standard Edition (x64) cannot lock pages in memory. Setting the policy is a good idea, but it will not make any difference using this edition. There is a patch on the way that will change this behavior but it is not out yet.
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.
As for the Bosst SQL, you're right...lol, WTF was I thinking. :w00t:
August 7, 2009 at 8:59 am
Warren Peace (8/6/2009)
First, AWE is not an option on x64 hardware because it is not needed. You don't have to check the box to enable it - it is not recognized on x64 systems.
Yes, there is an option build a Win x64 server with SQL x64. It's even available in SQL 2008 x64. Heres the setting of a win2008 x64 with SQL x64:
http://207.231.94.134/x64AWE.jpg
If it's there and selectable, one has to ask the question why is it even there if you say it's not. Do you know something we dont? If so let us know.
Well, it's there, but not used/taken in consideration. read here it specifically states:
Support for AWE is available only in the SQL Server 2005 Enterprise, Standard, and Developer editions and only applies to 32-bit operating systems
and
Note that the sp_configure awe enabled option is present on 64-bit SQL Server, but it is ignored. It is subject to removal in future releases or service packs of 64-bit SQL Server.
I know... there's a lot of confusion on this topic... 🙂
August 7, 2009 at 9:29 am
Richard, thanks for the vlidation. I know this has always been cunfusing, that's why I raised the question of it.
lol, why the hell does MS leave in there then?......lol :hehe:
August 7, 2009 at 9:35 am
August 7, 2009 at 9:37 am
August 7, 2009 at 9:45 am
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
August 7, 2009 at 9:50 am
Jeffrey & GilaMonster, thanks for the info!!!
fizzlme, sorry for hijacking your post bro.:-)
August 7, 2009 at 9:52 am
Second, for a dedicated database server there is no reason to set a minimum memory amount. Setting the minimum to 12GB and the maximum to 14GB running SQL Server 2005 Standard Edition (x64) will almost certainly cause memory pressure on the system.
Out of curiosity, what is the reason for not setting the minimum memory amount?
Edit: Nevermind... missed Gail's post.
- Jeff
August 7, 2009 at 10:07 am
GilaMonster (8/7/2009)
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.
Though it is worth noting that there's a hotfix available (either currently or soon) for both 2005 and 2008, that removes this limitation.
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
August 7, 2009 at 12:36 pm
Warren Peace (8/7/2009)
Richard, thanks for the vlidation. I know this has always been cunfusing, that's why I raised the question of it.lol, why the hell does MS leave in there then?......lol :hehe:
They leave it in there because that is part of the client-tool which is, of course, used to manage both 64 bit and 32bit systems.
There have been many reports about SQL Server 2005 Standard Edition on x64 paging out memory and memory pressure. What most people have found is that you have to drop the max memory setting quite a bit to relieve these issues. Some have been able to set it as high as 13GB (on 16GB machines) with no problems. Most, however, have not been able to go above 12GB without issues.
I was running Standard Edition on a machine with 32GB of memory. I could not go above 24GB allocated to SQL Server without having memory issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 10, 2009 at 10:37 am
Just a quick thank you. I will set the maximum at 12 gig with no minimum. This has been extremely informative.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply
