February 28, 2012 at 2:35 am
Hi,
I am using SQL Server R2. I set the sp_configure 'max server memory', 12288; But still it is not taking more than 2.52 gb and my sql server performance is very slow. Please let me know what should i do to consume more memory.
February 28, 2012 at 4:30 am
If SQL Server is not using all the available memory, probably it doesn't need to.
I suppose you increased the max memory because you clearly identified a memory bottleneck, right?
-- Gianluca Sartori
February 28, 2012 at 4:42 am
How are you monitoring memory usage?
You've checked and confirmed that the slowness is due to memory bottlenecks?
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
February 28, 2012 at 4:52 am
I have measured the memory consumption with the Task Manager, 'Performance' tab.
Exacly i dont know how to measure memory bottlenecks.
What i did was
1. executed an script (more complex) and it consumes around 2 GB.
2. parallelly i have executed another script (complex one) then the consumed memeory went to 2.54 gb
3. again parallelly i have executed another script (again complex one) but consumed memeory doesnt go beyond 2.54 gb
February 28, 2012 at 5:00 am
rvasanth (2/28/2012)
I have measured the memory consumption with the Task Manager, 'Performance' tab.
Don't use task manager for monitoring SQL's memory, there are scenarios where it is completely and totally inaccurate. Use performance monitor, total server memory is one counter.
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
March 13, 2012 at 4:48 pm
2.54 is a number I recognize. Are you by any chance on a 32-bit OS with the /3GB switch enabled but without AWE enabled?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 13, 2012 at 4:56 pm
opc.three (3/13/2012)
2.54 is a number I recognize. Are you by any chance on a 32-bit OS with the /3GB switch enabled but without AWE enabled?
32 bit with /3GB and AWE would show the same thing iirc, because Task Manager does not display AWE memory usage, only the 'normal' memory usage
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
March 13, 2012 at 5:03 pm
GilaMonster (3/13/2012)
opc.three (3/13/2012)
2.54 is a number I recognize. Are you by any chance on a 32-bit OS with the /3GB switch enabled but without AWE enabled?32 bit with /3GB and AWE would show the same thing iirc, because Task Manager does not display AWE memory usage, only the 'normal' memory usage
Bah! That's right, the OP already said they were using Task Manager to measure memory...
Please run this on your instance and post the results:
SELECT SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
(
SELECT CEILING(CAST(physical_memory_in_bytes / (1024.0 * 1024.0) AS DECIMAL(12, 2)))
FROM sys.dm_os_sys_info
) AS [Physical Memory_MB],
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'min server memory (MB)'
) AS [min server memory (GB)],
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'max server memory (MB)'
) AS [max server memory (GB)],
(
SELECT CAST(virtual_memory_in_bytes / (1024.0 * 1024 * 1024) AS DECIMAL(20, 2))
FROM sys.dm_os_sys_info
) AS VAS_GB,
(
SELECT value_in_use
FROM sys.configurations
WHERE name = 'awe enabled'
) AS [awe enabled]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply