A D
asked on
SQL Server memory sizing - reallocation
We have 4 SQL database servers with 8 core processors each and below are their memory allocation.
Server1
Memory Total: 69.9 GB
SQL: 48.8 GB
OS: 21.1 GB
Server 2
Memory Total: 69.9 GB
SQL : 58.5 GB
OS: 11.3 GB
Server3
Memory Total: 69.9 GB
SQL : 48.8 GB
OS: 21.1 GB
Server4
Memory Total: 69.9 GB
SQL : 63 GB
OS: 6.9 GB
Customer wants to reallocate the memory optimally and consistent on each server. Could you please suggest proper allocation of memory as per the standards and also points needs to be considered while reallocating memory? Appreciate any additional information.
Server1
Memory Total: 69.9 GB
SQL: 48.8 GB
OS: 21.1 GB
Server 2
Memory Total: 69.9 GB
SQL : 58.5 GB
OS: 11.3 GB
Server3
Memory Total: 69.9 GB
SQL : 48.8 GB
OS: 21.1 GB
Server4
Memory Total: 69.9 GB
SQL : 63 GB
OS: 6.9 GB
Customer wants to reallocate the memory optimally and consistent on each server. Could you please suggest proper allocation of memory as per the standards and also points needs to be considered while reallocating memory? Appreciate any additional information.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As per my experience we normally keep around 50-60% of RAM (as MAX Server Memory) for SQL Server and remaining is for OS.WOW. What the OS will do with 35GB in his case?
can u show me your db
ASKER
Hi
SQL version - SQL Server 2008 and Windows 2008 R2
They are standalone servers and no clustering.
SQL version - SQL Server 2008 and Windows 2008 R2
They are standalone servers and no clustering.
Ok. Then I would recommend you to set the MAX server memory for SQL Server in all machines (assuming that you only have one SQL Server instance per machine) to 62GB and let the rest of Windows Server.
ASKER
Thanks Vitor
Any special recommendations or cautions you suggest while doing the change?
Any special recommendations or cautions you suggest while doing the change?
ASKER
Also please comment on why 62gb (Just in case it need to justify to customer) :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It takes a lot of additional information to give you anything other than a very general answer.
In general, the more memory SQL has, the better it will perform. Therefore, set SQL memory as high as you can while still preventing OS RAM paging, and leave, say, 1G or so free at all times for exceptional situations.
Don't have time for all qs on these servers, but here's a quick 2:
1) What besides SQL uses significant amounts of RAM?
2) Do any SQL instances indicate low (physical) memory conditions? For example, by checking the results of this query:
;WITH RingBuffer
AS (SELECT CAST(dorb.record AS XML) AS xRecord,
dorb.TIMESTAMP
FROM sys.dm_os_ring_buffers AS dorb
WHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONI TOR'
),
RingBuffer2 AS (
SELECT xr.value('(ResourceMonitor /Notificat ion)[1]', 'varchar(75)') AS RmNotification,
xr.value('(ResourceMonitor /Indicator sProcess)[ 1]', 'tinyint') AS IndicatorsProcess,
xr.value('(ResourceMonitor /Indicator sSystem)[1 ]', 'tinyint') AS IndicatorsSystem,
xr.value('(//Record/Resour ceMonitor/ Indicators )[1]', 'bigint') AS [Indicators_SQL2005],
xr.value('(//Record/@time) [1]', 'bigint') AS [RecordTime],
xr.value('(MemoryNode/Targ etMemory)[ 1]', 'bigint') AS TargetMemory,
xr.value('(MemoryNode/Rese rveMemory) [1]', 'bigint') AS ReserveMemory,
xr.value('(MemoryNode/Comm ittedMemor y)[1]', 'bigint') AS CommitedMemory,
xr.value('(MemoryNode/Shar edMemory)[ 1]', 'bigint') AS SharedMemory,
xr.value('(MemoryNode/Page sMemory)[1 ]', 'bigint') AS PagesMemory,
xr.value('(MemoryRecord/Me moryUtiliz ation)[1]' , 'bigint') AS MemoryUtilization,
xr.value('(MemoryRecord/To talPhysica lMemory)[1 ]', 'bigint') AS TotalPhysicalMemory,
xr.value('(MemoryRecord/Av ailablePhy sicalMemor y)[1]', 'bigint') AS AvailablePhysicalMemory,
xr.value('(MemoryRecord/To talPageFil e)[1]', 'bigint') AS TotalPageFile,
xr.value('(MemoryRecord/Av ailablePag eFile)[1]' , 'bigint') AS AvailablePageFile,
xr.value('(MemoryRecord/To talVirtual AddressSpa ce)[1]', 'bigint') AS TotalVirtualAddressSpace,
xr.value('(MemoryRecord/Av ailableVir tualAddres sSpace)[1] ',
'bigint') AS AvailableVirtualAddressSpa ce,
xr.value('(MemoryRecord/Av ailableExt endedVirtu alAddressS pace)[1]',
'bigint') AS AvailableExtendedVirtualAd dressSpace ,
dosi.ms_ticks
FROM RingBuffer AS rb
CROSS JOIN sys.dm_os_sys_info AS dosi
CROSS APPLY rb.xRecord.nodes('Record') record (xr)
)
SELECT
DATEADD (ms, -1 * (rb2.ms_ticks - rb2.RecordTime), GETDATE()) AS NotificationTime,
rb2.*
FROM RingBuffer2 rb2
--WHERE
--RmNotification LIKE '%MEM%_LOW%' AND (IndicatorsProcess > 0 OR IndicatorsSystem > 0)
--WHERE --Indicator Values: 1=high(ok) phys mem; 2=low(potential issue of not enough ram) phys mem;4=low virt mem.
--( IndicatorsSystem >= 2 OR Indicators_SQL2005 >=2 )
ORDER BY [NotificationTime] DESC
In general, the more memory SQL has, the better it will perform. Therefore, set SQL memory as high as you can while still preventing OS RAM paging, and leave, say, 1G or so free at all times for exceptional situations.
Don't have time for all qs on these servers, but here's a quick 2:
1) What besides SQL uses significant amounts of RAM?
2) Do any SQL instances indicate low (physical) memory conditions? For example, by checking the results of this query:
;WITH RingBuffer
AS (SELECT CAST(dorb.record AS XML) AS xRecord,
dorb.TIMESTAMP
FROM sys.dm_os_ring_buffers AS dorb
WHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONI
),
RingBuffer2 AS (
SELECT xr.value('(ResourceMonitor
xr.value('(ResourceMonitor
xr.value('(ResourceMonitor
xr.value('(//Record/Resour
xr.value('(//Record/@time)
xr.value('(MemoryNode/Targ
xr.value('(MemoryNode/Rese
xr.value('(MemoryNode/Comm
xr.value('(MemoryNode/Shar
xr.value('(MemoryNode/Page
xr.value('(MemoryRecord/Me
xr.value('(MemoryRecord/To
xr.value('(MemoryRecord/Av
xr.value('(MemoryRecord/To
xr.value('(MemoryRecord/Av
xr.value('(MemoryRecord/To
xr.value('(MemoryRecord/Av
'bigint') AS AvailableVirtualAddressSpa
xr.value('(MemoryRecord/Av
'bigint') AS AvailableExtendedVirtualAd
dosi.ms_ticks
FROM RingBuffer AS rb
CROSS JOIN sys.dm_os_sys_info AS dosi
CROSS APPLY rb.xRecord.nodes('Record')
)
SELECT
DATEADD (ms, -1 * (rb2.ms_ticks - rb2.RecordTime), GETDATE()) AS NotificationTime,
rb2.*
FROM RingBuffer2 rb2
--WHERE
--RmNotification LIKE '%MEM%_LOW%' AND (IndicatorsProcess > 0 OR IndicatorsSystem > 0)
--WHERE --Indicator Values: 1=high(ok) phys mem; 2=low(potential issue of not enough ram) phys mem;4=low virt mem.
--( IndicatorsSystem >= 2 OR Indicators_SQL2005 >=2 )
ORDER BY [NotificationTime] DESC
ASKER
1) Nothing other than SQL has been hosted on any of the SQL server. Each server have one SQL instance.
2) An output file of above SQL query has been uploaded.
What is recommended amount of memory sharing?
Output.xlsx
2) An output file of above SQL query has been uploaded.
What is recommended amount of memory sharing?
Output.xlsx
ASKER
Any further comments on output please? :)
Those results don't look that bad. There are occasional low-memory conditions in a given process, but not in the system overall.
Check the other instances, keeping an eye out for "IndicatorsSystem" > 1.
Also, be sure to explicitly limit each SQL instance to a max amount such that you will always have memory available to each instance and some left over for Windows and other tasks.
Check the other instances, keeping an eye out for "IndicatorsSystem" > 1.
Also, be sure to explicitly limit each SQL instance to a max amount such that you will always have memory available to each instance and some left over for Windows and other tasks.
Did you at least request your client to configure the memory of the SQL Server instances as I recommended?
IMHO you should do that and then use Scott's script to try to tune if necessary.
IMHO you should do that and then use Scott's script to try to tune if necessary.
ASKER
Yes. the recommendation has been given and let me also mention that it is exactly in-line with our technical person who suggested to allocate 8gig (maximum) to OS. It is currently with client's tech team for approval.
Thanks for the help on this :)
Thanks for the help on this :)
it is exactly in-line with our technical person who suggested to allocate 8gig (maximum) to OS.Good. I just don't understand why you also chose a comment that told you to reserve almost 50% of the RAM for the OS and that's totally wrong and also is something your client wisely didn't apply in their environment.
Anyway, I would start to let only 8GB for the Windows and the rest to SQL Server.
Some information is missing so we can't give you a better answer. If you can tell us the Windows and SQL Server versions and also if any of those server are nodes of a Cluster or they are all Stand Alone servers, it might help.