September 7, 2011 at 7:42 pm
Currently I am facing a SQL memory pressure issue.
SQL Server 2008 Enterprise Edition 64 bit ; 8 GB RAM
Env details : 10.50.1600.1RTMEnterprise Edition (64-bit)
Max Memory set to : 6 GB but it grabs more
Task Manager shows : 7.8 GB memory usage
we have 16 DBs and 13 are Mirrored. Reporting server.
If I check in sp_who2 , I see below for Mirror
Command CPU Time
DB MIRROR 1182640
DB MIRROR 1640
DB MIRROR 2046
DB MIRROR 2000
DB MIRROR 41828
DB MIRROR 113687
DB MIRROR 164312
BRKR TASK 924859
DB MIRROR 40796
DB MIRROR 87859
DB MIRROR 281
DB MIRROR 1182640
DB MIRROR 1046
DB MIRROR 2203
DB MIRROR 31
DB MIRROR 2218
DB MIRROR 3078
DB MIRROR 0
DB MIRROR 4187
DB MIRROR 24390
DB MIRROR 78640
DB MIRROR 122265
DB MIRROR 61406
DB MIRROR 35062
DB MIRROR 166609
DB MIRROR 55000
DB MIRROR 1562
-----------------------------------------------------------------------------
I ran a query to check the bufferpool : it shows high usage
SELECT type, virtual_memory_committed_kb, multi_pages_kb
FROM sys.dm_os_memory_clerks
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0
MEMORYCLERK_SQLBUFFERPOOL6143744456
MEMORYCLERK_SQLUTILITIES120112128
MEMORYCLERK_SQLSTORENG161284440
OBJECTSTORE_LOCK_MANAGER163840
is BufferPool the problem ?
other than these all are normal
-----------------------------------------------------------------------------
I ran query to get :-
Overall used w/o BPool, Kb : 201064
Used by BPool with AWE, Kb : 6108320
-----------------------------------------------------------------------------
Our application getting slow because of this memory crunch.
Unable to understand whats wrong with the instance.
If we restart the sql services it starts with 512 KB and with in couple of hours, it reaches 7 GB of Memory Utilization.
I am confused to sort this issue.
Can anyone please provide any suggestions and solution for this issue , any PATCH / HotFix to be applied for this to get sorted out?
link lists a patch to fix the memory leak on MSQL2008 on reporting servers - http://support.microsoft.com/kb/981355
Cheers,
- Win.
" Have a great day "
September 7, 2011 at 7:49 pm
-- top 10 consumers of memory from BPool
select
top 10 type,
sum(single_pages_kb) as [SPA Mem, Kb]
from
sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc
type SPA Mem, Kb
CACHESTORE_SQLCP 152080
CACHESTORE_OBJCP 43856
OBJECTSTORE_LOCK_MANAGER 40576
CACHESTORE_PHDR 18728
USERSTORE_SCHEMAMGR 9840
MEMORYCLERK_SOSNODE 9640
USERSTORE_DBMETADATA 6480
CACHESTORE_SYSTEMROWSET 4512
MEMORYCLERK_SQLSTORENG 3872
MEMORYCLERK_SQLGENERAL 2112
Cheers,
- Win.
" Have a great day "
September 7, 2011 at 7:56 pm
More information:
typeallocatedvirtual_resvirtual_comawetopFivegrand_total
CACHESTORE_SQLCP158888000CACHESTORE_SQLCP158888
MEMORYCLERK_SQLUTILITIES1125201201200MEMORYCLERK_SQLUTILITIES112640
OBJECTSTORE_LOCK_MANAGER4057616384163840OBJECTSTORE_LOCK_MANAGER56960
CACHESTORE_OBJCP45712000CACHESTORE_OBJCP45712
MEMORYCLERK_SOSNODE25984000MEMORYCLERK_SOSNODE25984
Cheers,
- Win.
" Have a great day "
September 7, 2011 at 7:59 pm
Please find the attached results.
Cheers,
- Win.
" Have a great day "
September 8, 2011 at 12:33 am
Can anyone provide suggestions ?
Cheers,
- Win.
" Have a great day "
September 8, 2011 at 7:52 pm
No one knows any suggestions here ???
Allright, thanks...
Cheers,
- Win.
" Have a great day "
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply