Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

memory and CPU allocation on a monster server? Expand / Collapse
Author
Message
Posted Thursday, August 6, 2009 5:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:46 PM
Points: 276, Visits: 215
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.
Post #766625
Posted Thursday, August 6, 2009 5:18 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 4,363, Visits: 9,545
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #766628
Posted Thursday, August 6, 2009 5:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:54 PM
Points: 212, Visits: 466
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

Post #766631
Posted Thursday, August 6, 2009 7:36 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 4,363, Visits: 9,545
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #766648
Posted Thursday, August 6, 2009 9:39 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:54 PM
Points: 212, Visits: 466
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.
Post #766681
Posted Friday, August 7, 2009 8:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 4:59 AM
Points: 1,093, Visits: 2,619
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... :)




_______________________________________________________________________
For better assistance in answering your questions, click here
Post #767010
Posted Friday, August 7, 2009 9:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:54 PM
Points: 212, Visits: 466
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
Post #767035
Posted Friday, August 7, 2009 9:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 4:59 AM
Points: 1,093, Visits: 2,619
... probably because of their practice of "It is subject to removal in future releases or service packs of 64-bit SQL Server."



_______________________________________________________________________
For better assistance in answering your questions, click here
Post #767040
Posted Friday, August 7, 2009 9:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 4:59 AM
Points: 1,093, Visits: 2,619
.



_______________________________________________________________________
For better assistance in answering your questions, click here
Post #767042
Posted Friday, August 7, 2009 9:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 40,411, Visits: 36,861
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #767053
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse