May 1, 2012 at 12:38 pm
Can you please provide the version, edition and bitness of your OS and SQL Server instance?
And the results of these queries:
-- memory overview
SELECT CAST(physical_memory_in_bytes / (1024.0 * 1024.0 * 1024.0) AS DECIMAL(20, 2)) AS PhysicalMemoryGB,
CAST(virtual_memory_in_bytes / (1024.0 * 1024 * 1024) AS DECIMAL(20, 2)) AS VasGB,
CAST((bpool_committed * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS BufferPoolCommittedMemoryGB,
CAST((bpool_commit_target * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS BufferPoolTargetMemoryGB,
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'min server memory (MB)'
) AS MinServerMemoryGB,
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'max server memory (MB)'
) AS MaxServerMemoryGB,
(
SELECT value_in_use
FROM sys.configurations
WHERE name = 'awe enabled'
) AS IsAweEnabled
FROM sys.dm_os_sys_info;
-- cpu overview
SELECT osi.cpu_count AS LogicalCpuCount,
osi.hyperthread_ratio AS HyperthreadCoreRatio,
osi.cpu_count / osi.hyperthread_ratio AS PhysicalCpuCount,
(
SELECT COUNT(*)
FROM sys.dm_os_schedulers
WHERE scheduler_id < osi.cpu_count
AND is_online = 1
) AS LogicalCpuOnline
FROM sys.dm_os_sys_info osi;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 1, 2012 at 1:14 pm
PhysicalMemoryGBVasGBBufferPoolCommittedMemoryGB BufferPoolTargetMemoryGB
5.99 8192.00 2.21 3.79
MinServerMemoryGBMaxServerMemoryGBIsAweEnabled
0.00 2097152.00 0
LogicalCpuCountHyperthreadCoreRatioPhysicalCpuCountLogicalCpuOnline
16 8 2 16
May 1, 2012 at 1:20 pm
Krasavita (5/1/2012)
PhysicalMemoryGBVasGBBufferPoolCommittedMemoryGB BufferPoolTargetMemoryGB5.99 8192.00 2.21 3.79
MinServerMemoryGBMaxServerMemoryGB IsAweEnabled
0.00 2097152.00 0
LogicalCpuCountHyperthreadCoreRatioPhysicalCpuCountLogicalCpuOnline
16 8 2 16
Most likely issue is bolded above. You need to set max memory immediately. It does not require a restart and takes effect immediately. Some guidance: Suggested Max Memory Settings for SQL Server 2005/2008[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 1, 2012 at 1:33 pm
Thank you very much, I will changed it,what is your recomendations is 1500 enough?
May 1, 2012 at 1:37 pm
With 6GB of RAM in your machine 1500MB would be very conservative. 1500MB may make sense for you though, it depends on what else you have running on your server as to how much memory to allocate to the buffer pool (max memory setting only restricts the buffer pool). You need to leave some memory for other programs (including SQL Server non-buffer pool activities) and the OS itself to do work. Have a look at Glenn's article, it's a good start.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 1, 2012 at 1:48 pm
sorry it is 15000 not 1500
May 1, 2012 at 1:51 pm
If you have 6GB RAM then how would setting max memory to 15000MB help you restrict the SQL Server from growing the buffer pool to the point where it affected server performance? The article...read the article 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 1, 2012 at 2:23 pm
I think I'd set max memory to say 4096MB. That would leave about 2GB for the OS.
May 1, 2012 at 2:27 pm
Thank you
May 2, 2012 at 6:46 am
Thank you everyone, but article recomends 6GB set to 4800, how did you come up with that number 4096 and plus 2 gb.
Thank you
May 2, 2012 at 6:51 am
Krasavita (5/2/2012)
Thank you everyone, but article recomends 6GB set to 4800, how did you come up with that number 4096 and plus 2 gb.Thank you
Simple math. Start with 1GB or 1024MB, multiple by 4, gives you 4096MB (4GB), which if you subtract that from 6GB leaves 2GB.
May 2, 2012 at 6:54 am
Thank you
May 2, 2012 at 7:23 am
I have another question, how would I know that I need to add more Ram?
Viewing 13 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply