How to view memory consumption in 64-bit O/S ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to view memory consumption in 64-bit O/S ?

Author

Topic

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2008-02-06 : 17:15:10

I know I can run DBCC MEMORYSTATUS to view how much memory SQL Server is using on a 64-bit server, but how do I view how much memory the other processes are consuming?

I do not believe the Perfmon counters are accurate when using AWE or 64-bit memory, at least in terms of memory consumption per process so how do we see this information. I have a feeling it is not possible.

Here is the problem. We have a 64-bit server with 32GB of memory. SQL Server is configured to use no more the 20GB. It currently is using only 15GB. The O/S is reporting 28.3GB used out of 32GB, which means some other processes are consuming 28.3GB - 15GB = 13.3GB. How can I determine what is using the 13.3GB of memory?

Thanks, Dave

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2008-02-06 : 18:02:37

monitor working set and private bytes in perfmon. These are accurate in x64 environments. it is funkier in AWE environments, but you should not be using AWE if you are running full 64bit.

i use process explorer from sysinternals for this type of thing. It is easier to look at all the running processes.

Also, if you have enabled the lock pages in memory configuration for your sql server then some perfmon counters will be inaccurate. the perfmon count for mssql:total server memory will be accurate and reflect the entire buffer cache.

-ec

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2008-02-07 : 12:38:50

Thanks

I started to think the rules that apply to 32-bit AWE memory monitoring applied to 64-bit (no AWE) memory monitoring. With AWE most of the perfmon counters are not valid and Task Manager is unable to report AWE memory accurately. I realized shortly after submitting this request the server I was looking at had 2 instances of SQL Server installed, not 1. Once I added 10GB for the memory consumed by the second instance that left about 3GB of memory being used by OS-related processes, which sounds normal.

Thanks, Dave

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-03-09 : 18:34:54

Private Bytes, Working Set.
Could you help explain a little im trying to see what to view also.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-09 : 21:06:14

Perfmon gives you definition.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-03-10 : 08:26:54

Private Bytes measures amount of memory that is currently committed
Working set measures amout of physical memory

After performing dbcc checkdb

Private 1286 Mgs
Working 768 Mgs

Physical 67102976
Available 25647480
System 2796956

Commit 40096424
170296632
54993832

I shutdown server 64 GIG and rebooted.
Available memory was around 67.

Ran dbcc checkdb on 40 gig database.

The page file was 1.34 gig went up to 32 gig
and then the available went to 25 gig from 67 gig

Is thats what happens the available gigs is reduced and goes to page file.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-10 : 22:01:19

Sql2k5 comes with memory consumption report.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-03-11 : 09:43:37

I just a bit curious on the PF usage going so high after the DBCC CHECK.

I have seen the memory report.
Largest is MEMORYCLERK_SQLBUFFERPOOL
Virtual Memory Reservered 67,190,784
Committed 65,536
AWE 38,469,128

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-11 : 22:48:37

You got those number from sql2k5 memory report?

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-03-12 : 09:30:46

Yes from the memory reports in SQL.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-03-12 : 09:31:36

DBCC MEMORYSTATUS

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-12 : 22:58:05

DBCC MEMORYSTATUS is different from memory consumption report.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-03-13 : 10:10:10

OBJECTSTORE LOCK MANAGER 262168
CACHESTORE_SQLCP 67152
USESTORE-DBMETADATA 50944

STOLEN 371
FREE 4698335
CACHED 17600

PAGE LIFE 724476

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources