Help!... Memory Use on Windows 2008 R2, SQL Server 2005 x64. The physical is 98%

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Help!... Memory Use on Windows 2008 R2, SQL...

24 posts, Page 1 of 3

1 2 3 »»»

Help!... Memory Use on Windows 2008 R2, SQL Server 2005 x64. The physical is 98%

Rate Topic

Display Mode

Topic Options

Author

Message

juanc.aguirre

Posted Thursday, March 15, 2012 10:05 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 17, Visits: 92

Hello to everyone, first... sorry for my english... I'm from Colombia.

I have a situation that i can't understand, a SQL Server 2005 x64 standard edition is installed on a Windows 2008 R2 Standard edition.

select @@version
Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7600: )

Actually this server have installed 8 GB of RAM, and the settings for the instance is the next:

select name, value
from sys.configurations
where name like '%memory (MB)%'

min server memory (MB) 0
max server memory (MB) 4096

The problem is... that the use of the Physical Memory for the server has come to 97% or 98%... but the sqlserver.exe process on the task manager show only 2.3 GB used.

Now... the DBCC MEMORYSTATUS command has the next results

dbcc memorystatus

VM Reserved 8475640
VM Committed 2392600
AWE Allocated 0
Reserved Memory 1024

My question is... Why if I have the max memory limited to 4 GB... the VM Reserved shows 8 GB... and the physical memory used by the process is just 2.3 GB ????... and... why the physical memory of the server is used to 98%....

NOTE: No others aplications or services are installed on this server... this server is dedicated for SQL Server.

Now... all the Servers Administrators are calling to me... they are worried for this consume... if this server is restarted, just take a few minutes for that the physical memory comes to 98% of use.

Thanks!!!!!! for the help....

Post #1267628

GSquared

Posted Thursday, March 15, 2012 11:30 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

The max memory setting is just for the buffer pool. If SQL Server needs more resources for other things, it will use it.

Do your databases have any CLR objects in them? Those can eat a lot of RAM if not managed correctly.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1267704

opc.three

Posted Thursday, March 15, 2012 1:29 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 6,737, Visits: 11,791

What does this return on your instance:

-- top 10 consumers of memory
SELECT TOP 10
        type,
        CAST(SUM(single_pages_kb) / 1024.0 AS DECIMAL(10, 2)) AS [SPA Mem, Mb]
FROM    sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC ;

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1267796

juanc.aguirre

Posted Thursday, March 15, 2012 1:32 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 17, Visits: 92

Hello... thanks for you quick answer, so... I really don't know if any CLR object exist on the DataBase, the application was not designed by me.

The question is... is this relationated with the version of the OS and the SQL Server, or if they are 32 or 64 bits. I already have another servers with the same hardware configuration, but the problem with the memory is not present on those.

Post #1267798

juanc.aguirre

Posted Thursday, March 15, 2012 1:34 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 17, Visits: 92

[b]opc.three
What does this return on your instance:

-- top 10 consumers of memory
SELECT TOP 10
type,
CAST(SUM(single_pages_kb) / 1024.0 AS DECIMAL(10, 2)) AS [SPA Mem, Mb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC ;

Thanks... this is the result...

CACHESTORE_SQLCP 1049.37
CACHESTORE_PHDR 30.23
CACHESTORE_OBJCP 20.23
MEMORYCLERK_SQLGENERAL 14.54
MEMORYCLERK_SOSNODE 8.77
USERSTORE_SCHEMAMGR 5.14
OBJECTSTORE_LOCK_MANAGER 3.54
MEMORYCLERK_SQLSTORENG 2.97
OBJECTSTORE_SNI_PACKET 2.54
USERSTORE_DBMETADATA 2.32

Post #1267802

opc.three

Posted Thursday, March 15, 2012 1:42 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 6,737, Visits: 11,791

Do you use Linked Servers heavily on this particular instance?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1267816

opc.three

Posted Thursday, March 15, 2012 1:46 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 6,737, Visits: 11,791

Run this one too please and post the results, this one includes multi-page allocations:

-- top 10 consumers of memory
SELECT TOP 10
        type,
        CAST(SUM(single_pages_kb+multi_pages_kb) / 1024.0 AS DECIMAL(10, 2)) AS [Mem, Mb]
FROM    sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb+multi_pages_kb) DESC ;

Post #1267820

juanc.aguirre

Posted Thursday, March 15, 2012 1:54 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 17, Visits: 92

opc.three (3/15/2012)

Run this one too please and post the results, this one includes multi-page allocations:

-- top 10 consumers of memory
SELECT TOP 10
        type,
        CAST(SUM(single_pages_kb+multi_pages_kb) / 1024.0 AS DECIMAL(10, 2)) AS [Mem, Mb]
FROM    sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb+multi_pages_kb) DESC ;

Hello... there's no server link on that instance.

and... the result of the query is

MEMORYCLERK_SQLOPTIMIZER 1.95
CACHESTORE_PHDR 30.23
CACHESTORE_XMLDBTYPE 0.01
CACHESTORE_EVENTS 0.02
USERSTORE_OBJPERM 0.40
USERSTORE_TOKENPERM 0.37
MEMORYCLERK_SQLSTORENG 15.59
CACHESTORE_XPROC 0.05
OBJECTSTORE_SNI_PACKET 2.66
CACHESTORE_BROKERRSB 0.01

Thanks...

Post #1267831

opc.three

Posted Thursday, March 15, 2012 2:01 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 6,737, Visits: 11,791

juanc.aguirre (3/15/2012)

opc.three (3/15/2012)

Run this one too please and post the results, this one includes multi-page allocations:

-- top 10 consumers of memory
SELECT TOP 10
        type,
        CAST(SUM(single_pages_kb+multi_pages_kb) / 1024.0 AS DECIMAL(10, 2)) AS [Mem, Mb]
FROM    sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb+multi_pages_kb) DESC ;

That does not look right, did you include the ORDER BY when you ran it?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1267837

juanc.aguirre

Posted Thursday, March 15, 2012 2:14 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 17, Visits: 92

opc.three (3/15/2012)

juanc.aguirre (3/15/2012)

opc.three (3/15/2012)

Run this one too please and post the results, this one includes multi-page allocations:

-- top 10 consumers of memory
SELECT TOP 10
        type,
        CAST(SUM(single_pages_kb+multi_pages_kb) / 1024.0 AS DECIMAL(10, 2)) AS [Mem, Mb]
FROM    sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb+multi_pages_kb) DESC ;

That does not look right, did you include the ORDER BY when you ran it?

Yea... you're right

the correct result is...

CACHESTORE_SQLCP 1029.78
CACHESTORE_PHDR 30.23
MEMORYCLERK_SOSNODE 24.59
CACHESTORE_OBJCP 19.91
MEMORYCLERK_SQLGENERAL 18.13
MEMORYCLERK_SQLSTORENG 15.63
OBJECTSTORE_LOCK_MANAGER 5.75
USERSTORE_SCHEMAMGR 5.00
OBJECTSTORE_SNI_PACKET 3.04
USERSTORE_DBMETADATA 2.26

Thanks!!!!

Post #1267848

« Prev Topic | Next Topic »

24 posts, Page 1 of 3

1 2 3 »»»

Permissions