February 17, 2015 at 11:08 am
Hi ,
My environment:
Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Below is what I see on my production box and that concerns me a lot. Can some one help me on how to proceed further and bring the Physical memory usuage down ? CPU Usage % varies from 0 - 15 % . I am limiting my server not to go beyond 25 GB but its not the case. It is using up 99 % of available memory. Upon some research, most of them said it could be due to metafile. So, I installed RamMap and saw that Metafile is not the culprit in my case but AWE is.
Thanks,
Ramesh
February 17, 2015 at 12:25 pm
why not focus what is chewing up memory. I have the same scenario. Run this against the server and look at memory and logical reads, you may have crap sql or bad indexes
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time
February 27, 2015 at 6:19 am
Dear Ramesh,
Did you looked into task manager that what service is taking more memory.
As per screen shot attached you have set max memory 25GB for sql server, please look into that taslmanager that sql server service is using its 25 GB or not. If it is not using its 25Gb then look at what other system services are using a lot of memory.
Run the following query to see does there any memory pressure at sql level.
SELECT total_physical_memory_kb / 1024 AS total_physical_memory_mb , available_physical_memory_kb / 1024 AS available_physical_memory_mb , total_page_file_kb / 1024 AS total_page_file_mb , available_page_file_kb / 1024 AS available_page_file_mb , system_memory_state_desc FROM sys.dm_os_sys_memory
if system_memory_state_deesc has "Available physical memory is low" is an indicator of external memory pressure, since it means that the OS has little available memory and so may require SQL Server to reduce its memory usage.
-----------------
Aditya Rathour
SQL DBA
Not Everything that is faced can be changed,
but nothing can be changed until it is faced.
February 27, 2015 at 7:23 am
Now when you say SQL Server is taking all the available memory do you mean it's actually using all the memory and running into performance issues? SQL Server will grab all the memory it can and not release it back to the OS that doesn't mean SQL Server itself is actually running out of memory.
February 27, 2015 at 7:27 am
also check your page life expectancy
April 21, 2015 at 11:05 am
Every single SQL Server I manage when I install it I set a max memory that SQL Server can consume to less than what is in the server. That way SQL Server cannot starve windows or any other service that wants memory.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply