SQL Server 2005 memory usage is 12GB

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)

SQL Server 2005 memory usage is 12GB

Author

Topic

d3ng
Yak Posting Veteran

83 Posts

Posted - 2008-11-25 : 03:34:24

Hi expert!

I would like to ask how this things happens that the memory usage of my SQL server is rise up to 12GB usage? while I set the maximum usage is up to 8GB? What are the possible causes or memory leaks..?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-25 : 09:32:21

Did you check Performance counters for it?Is it 64-bit SQL Server? Run DBCC Memoryusage.

d3ng
Yak Posting Veteran

83 Posts

Posted - 2008-11-25 : 19:55:49

Did you check Performance counters for it?
- Not yet, I will try

Is it 64-bit SQL Server?
- Yes, SQL Server 64 bit standard edition

Run DBCC Memoryusage
- Not yet. But I will try.

As far as I observed, when I checked the activity monitoring there is no blocking.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-25 : 20:02:48

64-bit SQL Server 2005 Standard Edition can't get 12GB memory.Do you see ' Memory paged out in Event Log'?Check Memory counters in Perf mon and post results so we can assist?

d3ng
Yak Posting Veteran

83 Posts

Posted - 2008-11-25 : 20:06:45

BTW, we are also using the Terminal Services setup for the client PC from the other branch to access our system.

d3ng
Yak Posting Veteran

83 Posts

Posted - 2008-11-25 : 21:19:14

Physical Memory
Total : 16775864
Available: 360248
System Cache : 400056

Kernel Memory:
Total: 103316
Paged: 54900
Nonpaged: 48416

d3ng
Yak Posting Veteran

83 Posts

Posted - 2008-11-26 : 19:50:06

I hope someone could help me with my concern =(

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-26 : 19:52:36

You are not showing Memory counters and output of DBCC Memoryusage .We need see those.

d3ng
Yak Posting Veteran

83 Posts

Posted - 2008-11-26 : 19:59:00

Ok I will get the memory counters and the output of DBCC Memorystatus.

d3ng
Yak Posting Veteran

83 Posts

Posted - 2008-11-26 : 22:16:54

Hi SODEEP,

here is the memory status results using DBCC MEMORYSTATUS, I dont know what do you mean of memory counters? Can you be more specific? Please forgave my ignorance.

Memory Manager KB
VM Reserved 16918776
VM Committed 15170232
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0

----------------------------------------------------------------------

Memory node Id = 0 KB
VM Reserved 16913016
VM Committed 15164560
AWE Allocated 0
MultiPage Allocator 27648
SinglePage Allocator 1192872

----------------------------------------------------------------------

MEMORYCLERK_SQLGENERAL (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 7600
MultiPage Allocator 4368

----------------------------------------------------------------------

MEMORYCLERK_SQLBUFFERPOOL (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 7600
MultiPage Allocator 4368

----------------------------------------------------------------------

MEMORYCLERK_SQLOPTIMIZER (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 7600
MultiPage Allocator 4368

----------------------------------------------------------------------

MEMORYCLERK_SQLUTILITIES (Total) KB
VM Reserved 120
VM Committed 120
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 88
MultiPage Allocator 0

----------------------------------------------------------------------

MEMORYCLERK_SQLSTORENG (Total) KB
VM Reserved 8384
VM Committed 8384
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 7912
MultiPage Allocator 600

----------------------------------------------------------------------

MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 19680
MultiPage Allocator 0

----------------------------------------------------------------------

MEMORYCLERK_SQLCLR (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

----------------------------------------------------------------------

MEMORYCLERK_SQLSERVICEBROKER (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 64
MultiPage Allocator 304

----------------------------------------------------------------------

MEMORYCLERK_SQLHTTP (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

----------------------------------------------------------------------

MEMORYCLERK_SNI (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2392
MultiPage Allocator 16

----------------------------------------------------------------------

MEMORYCLERK_FULLTEXT (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

----------------------------------------------------------------------

MEMORYCLERK_SQLXP (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

----------------------------------------------------------------------

MEMORYCLERK_BHF (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3472
MultiPage Allocator 0

----------------------------------------------------------------------

MEMORYCLERK_HOST (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 80
MultiPage Allocator 64

----------------------------------------------------------------------

MEMORYCLERK_SOSNODE (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 80
MultiPage Allocator 64

----------------------------------------------------------------------

MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 48
MultiPage Allocator 0

----------------------------------------------------------------------

CACHESTORE_OBJCP (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 21808
MultiPage Allocator 200

----------------------------------------------------------------------

CACHESTORE_SQLCP (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1016248
MultiPage Allocator 2960

----------------------------------------------------------------------

CACHESTORE_PHDR (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1016248
MultiPage Allocator 2960

----------------------------------------------------------------------

CACHESTORE_XPROC (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 112
MultiPage Allocator 0

----------------------------------------------------------------------

CACHESTORE_TEMPTABLES (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

-----------------------------------------------------------------------

CACHESTORE_NOTIF (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

-----------------------------------------------------------------------

CACHESTORE_VIEWDEFINITIONS (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16

-----------------------------------------------------------------------

CACHESTORE_XMLDBTYPE (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

-----------------------------------------------------------------------

CACHESTORE_XMLDBELEMENT (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
MultiPage Allocator 0

-----------------------------------------------------------------------

CACHESTORE_XMLDBATTRIBUTE (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

-----------------------------------------------------------------------

CACHESTORE_STACKFRAMES (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8

-----------------------------------------------------------------------

CACHESTORE_BROKERTBLACS (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 80
MultiPage Allocator 0

-----------------------------------------------------------------------

CACHESTORE_BROKERKEK (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 80
MultiPage Allocator 0

-----------------------------------------------------------------------

CACHESTORE_BROKERDSH (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

-----------------------------------------------------------------------

CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

-----------------------------------------------------------------------

CACHESTORE_BROKERRSB (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

-----------------------------------------------------------------------

CACHESTORE_BROKERREADONLY (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

-----------------------------------------------------------------------

CACHESTORE_BROKERTO (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

-----------------------------------------------------------------------

CACHESTORE_EVENTS (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

------------------------------------------------------------------------

CACHESTORE_SYSTEMROWSET (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1264
MultiPage Allocator 0

------------------------------------------------------------------------

USERSTORE_SCHEMAMGR (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8752
MultiPage Allocator 184

------------------------------------------------------------------------

USERSTORE_DBMETADATA (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 10584
MultiPage Allocator 0

-------------------------------------------------------------------------

USERSTORE_TOKENPERM (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1848
MultiPage Allocator 0

-------------------------------------------------------------------------

USERSTORE_OBJPERM (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0

-------------------------------------------------------------------------

USERSTORE_SXC (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 9832
MultiPage Allocator 0

-------------------------------------------------------------------------

OBJECTSTORE_LBSS (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 168
MultiPage Allocator 4160

-------------------------------------------------------------------------

OBJECTSTORE_SNI_PACKET (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 30984
MultiPage Allocator 48

--------------------------------------------------------------------------

OBJECTSTORE_SERVICE_BROKER (Total) KB
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 272
MultiPage Allocator 0

--------------------------------------------------------------------------

OBJECTSTORE_LOCK_MANAGER (Total) KB
VM Reserved 65536
VM Committed 65536
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24952
MultiPage Allocator 0

--------------------------------------------------------------------------

Buffer Distribution Buffers
Stolen 7027
Free 265626
Cached 142076
Database (clean) 1434324
Database (dirty) 2112
I/O 0
Latched 0

---------------------------------------------------------------------------

Buffer Counts Buffers
Committed 1851165
Target 1851165
Hashed 1436436
Stolen Potential 1841761
External Reservation 0
Min Free 256
Visible 1851165
Available Paging File 244217

---------------------------------------------------------------------------

Procedure Cache Value
TotalProcs 6114
TotalPages 131472
InUsePages 267

---------------------------------------------------------------------------

Global Memory Objects Buffers
Resource 366
Locks 3122
XDES 617
SETLS 8
SE Dataset Allocators 16
SubpDesc Allocators 8
SE SchemaManager 1116
SQLCache 709
Replication 2
ServerGlobal 48
XP Global 2
SortTables 2

----------------------------------------------------------------------------

Query Memory Objects Buffers
Grants 0
Waiting 0
Available (Buffers) 1378270
Maximum (Buffers) 1378270
Limit 1378270
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 1391070

-----------------------------------------------------------------------------

Small Query Memory Objects Buffers
Grants 0
Waiting 0
Available (Buffers) 12800
Maximum (Buffers) 12800
Limit 12800

-----------------------------------------------------------------------------

Optimization Queue Value
Overall Memory 12155387904
Target Memory 10416078848
Last Notification 1
Timeout 6
Early Termination Factor 5

-----------------------------------------------------------------------------

Small Gateway Value
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000

-----------------------------------------------------------------------------

Medium Gateway Value
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 12

-----------------------------------------------------------------------------

Big Gateway Value
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8

------------------------------------------------------------------------------

MEMORYBROKER_FOR_CACHE Value
Allocations 142156
Rate 389
Target Allocations 1407333
Future Allocations 0
Last Notification 1

------------------------------------------------------------------------------

MEMORYBROKER_FOR_STEAL Value
Allocations 6733
Rate -27
Target Allocations 1271494
Future Allocations 0
Last Notification 1

------------------------------------------------------------------------------

MEMORYBROKER_FOR_RESERVE Value
Allocations 0
Rate -96
Target Allocations 1483812
Future Allocations 344567
Last Notification 1

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-26 : 22:59:15

Looks like Stored procedure Cache is eating up memory.There are unnecessary plans in your Cache. Are all your stored procedures parameterized?

Can you run Performance monitors?
Give average values for counters:

1)Memory pages/sec
2)Buffer cache hit ratio
3)Total server memory
4)Total server available memory
5)page life expectancy
6)Cache hit ratio
7) Lazy writes/sec

---------------------------------------------------------------------

CACHESTORE_SQLCP (Total) KB
SinglePage Allocator 1016248
Your procedure Cache is .97 GB. ---------------------------------------------------------------------

Buffer Distribution Buffers
Stolen 7027
Free 265626
Cached 142076
Database (clean) 1434324
Database (dirty) 2112
I/O 0
Latched 0

---------------------------------------------------------------------
Procedure Cache Value
TotalProcs 6114
TotalPages 131472
InUsePages 267---------------------------------------------------------------------

There are 6114 plan in cache.

d3ng
Yak Posting Veteran

83 Posts

Posted - 2008-11-27 : 00:58:59

"There are 6114 plan in cache."

How to avoid this? What is the possible reason of this? Currently getting the average values for counters:

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-27 : 08:13:50

quote:
Originally posted by d3ng

"There are 6114 plan in cache."

How to avoid this? What is the possible reason of this? Currently getting the average values for counters:

Means your procedure cache is high and there are unnecessary plans that haven't flushed to disk by Lazywriters due to adhoc unparameterized query. Try to parameterized your query.

ALso post the output of above memory counters so we can analyze .

d3ng
Yak Posting Veteran

83 Posts

Posted - 2008-11-27 : 20:06:24

Here are the counters:

1. Memory pages/sec - 0.010
2. Buffer cache hit ratio - 6473
3. Page life Expectancy - 92883
4. Cache:
Lazy Write Pages/sec - 3.364
Lazy Write Flushes/sec - 0.970

SQLServer:Buffer Manager
1. Buffer cache hit ratio - 99.871
2. Page life expectancy - 38709
3. Lazy writes/sec - 0.000

SQLServer:Memory Manager
1. Total server memory (KB) - 14843008
2. Total server available memory -
3. Connection Memory (KB) - 19912

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-28 : 13:28:47

quote:
Originally posted by d3ng

Here are the counters:

1. Memory pages/sec - 0.010
2. Buffer cache hit ratio - 6473
3. Page life Expectancy - 92883( Really good)
4. Cache:
Lazy Write Pages/sec - 3.364
Lazy Write Flushes/sec - 0.970 good

SQLServer:Buffer Manager
1. Buffer cache hit ratio - 99.871 good
2. Page life expectancy - 38709
3. Lazy writes/sec - 0.000

SQLServer:Memory Manager
1. Total server memory (KB) - 14843008
2. Total server available memory -
3. Connection Memory (KB) - 19912

What about Target server Memory and Working sets in SQLServer:Memory Manager?
All counters look good . Only issue I see is your procedure cache is little high and unneeded plans are stored in cache.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-28 : 13:58:36

quote:
Originally posted by sodeep

64-bit SQL Server 2005 Standard Edition can't get 12GB memory.Do you see ' Memory paged out in Event Log'?Check Memory counters in Perf mon and post results so we can assist?

Both the 32-bit and 64-bit versions of SQL Server 2005 Standard Edition can use up to the maximum amount of memory available from the operating system.

CODO ERGO SUM

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-28 : 14:05:40

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by sodeep

64-bit SQL Server 2005 Standard Edition can't get 12GB memory.Do you see ' Memory paged out in Event Log'?Check Memory counters in Perf mon and post results so we can assist?

Both the 32-bit and 64-bit versions of SQL Server 2005 Standard Edition can use up to the maximum amount of memory available from the operating system.

CODO ERGO SUM

Not 32-bit . Only 64-bit can use it. But there is some limitations regarding 64-bit Standard edition as you can't enable 'Lock in pages in memory' as in Enterprise Edition. That is what i am trying to say.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-28 : 15:51:37

quote:
Originally posted by sodeep

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by sodeep

64-bit SQL Server 2005 Standard Edition can't get 12GB memory.Do you see ' Memory paged out in Event Log'?Check Memory counters in Perf mon and post results so we can assist?

Both the 32-bit and 64-bit versions of SQL Server 2005 Standard Edition can use up to the maximum amount of memory available from the operating system.

CODO ERGO SUM

Not 32-bit . Only 64-bit can use it. But there is some limitations regarding 64-bit Standard edition as you can't enable 'Lock in pages in memory' as in Enterprise Edition. That is what i am trying to say.

Even the OPs posts show that is is using 14 Gb of memory:
1. Total server memory (KB) - 14843008

SQL Server 2005 Features Comparison
http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx
What is says for 2005 Standard Edition is:
"Memory limited to maximum supported by operating system."

CODO ERGO SUM

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-28 : 19:01:16

http://support.microsoft.com/kb/918483

d3ng
Yak Posting Veteran

83 Posts

Posted - 2008-12-01 : 19:42:26

So what would be the possible reason of the memory leak issue and how can I resolve it?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-12-01 : 19:58:41

There is no reason to assume that you have a memory leak. It looks like SQL Server is operating normally.

CODO ERGO SUM