August 11, 2011 at 7:48 am
Hello,
I have SQL Server 2008 Standard Edition installed on Windows 2003 Enterprise Edition 32 bit with 8GB RAM and AWE enabled.
SQL memory configuration: min server memory set to 1024 and max server memory set to 4096. We do run SSIS on this server as well and will be running SSRS in the near future.
Our problem is that when viewing task manager I can see that the memory is close to max for the majority of the day. I am unable to fund out what is causing this.
There is not much paging until an SSIS package is run.
Hopefully someone can decipher this and help me out. Thanks!
DBCC MEMORYSTATUS
Memory Manager KB
---------------------------------------- -----------
VM Reserved 1715936
VM Committed 127568
AWE Allocated 3495936
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
---------------------------------------- -----------
VM Reserved 1713120
VM Committed 124864
AWE Allocated 3495936
MultiPage Allocator 61464
SinglePage Allocator 506960
(5 row(s) affected)
Memory node Id = 32 KB
---------------------------------------- -----------
VM Reserved 1728
VM Committed 1672
AWE Allocated 0
MultiPage Allocator 1600
SinglePage Allocator 506960
(5 row(s) affected)
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2784
MultiPage Allocator 3272
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 1620056
VM Committed 39152
AWE Allocated 3495936
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 2112
(7 row(s) affected)
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 120
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 360
MultiPage Allocator 72
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- -----------
VM Reserved 240
VM Committed 240
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 320
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- -----------
VM Reserved 15040
VM Committed 15040
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4744
MultiPage Allocator 32176
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 592
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 232
MultiPage Allocator 344
(7 row(s) affected)
MEMORYCLERK_SQLHTTP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 48
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_SNI (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_SNI (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 48
MultiPage Allocator 32
(7 row(s) affected)
MEMORYCLERK_FULLTEXT (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_BHF (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 48
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- -----------
VM Reserved 4224
VM Committed 4224
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_HOST (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 104
MultiPage Allocator 48
(7 row(s) affected)
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4208
MultiPage Allocator 5848
(7 row(s) affected)
MEMORYCLERK_SOSNODE (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 1520
(7 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4224
MultiPage Allocator 7368
(7 row(s) affected)
MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 24
SM Committed 24
SinglePage Allocator 0
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_XE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 192
MultiPage Allocator 96
(7 row(s) affected)
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 83768
MultiPage Allocator 1784
(7 row(s) affected)
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 316256
MultiPage Allocator 14672
(7 row(s) affected)
CACHESTORE_PHDR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 50512
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XPROC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 240
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 72
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_NOTIF (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_STACKFRAMES (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 472
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 64
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTO (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_EVENTS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 3952
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_CONVPRI (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 496
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_FULLTEXTSTOPLIST (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 9576
MultiPage Allocator 192
(7 row(s) affected)
USERSTORE_DBMETADATA (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 13432
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 6720
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 776
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 152
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 160
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 72
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1096
MultiPage Allocator 56
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 56
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1096
MultiPage Allocator 112
(7 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 424
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- -----------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4832
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- -----------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4856
MultiPage Allocator 0
(7 row(s) affected)
Buffer Pool Value
---------------------------------------- -----------
Committed 436159
Target 524288
Database 371607
Dirty 6828
In IO 0
Latched 0
Free 1182
Stolen 63370
Reserved 0
Visible 193536
Stolen Potential 120489
Limiting Factor 10
Last OOM Factor 0
Last OS Error 0
Page Life Expectancy 632
(15 row(s) affected)
Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory 1461952512
Available Virtual Memory 255291392
Available Paging File 3534733312
Working Set 174616576
Percent of Committed Memory in WS 100
Page Faults 3435016
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
(10 row(s) affected)
Procedure Cache Value
---------------------------------------- -----------
TotalProcs 3720
TotalPages 58404
InUsePages 70
(3 row(s) affected)
Global Memory Objects Pages
---------------------------------------- -----------
Resource 332
Locks 607
XDES 35
SETLS 1
SE Dataset Allocators 2
SubpDesc Allocators 1
SE SchemaManager 501
SE Column Metadata Cache 719
SQLCache 475
Replication 2
ServerGlobal 33
XP Global 2
SortTables 3674
(13 row(s) affected)
Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 107303
Current Max 107303
Future Max 107303
Physical Max 139778
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
(11 row(s) affected)
Small Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 5647
Current Max 5647
Future Max 5647
(5 row(s) affected)
Optimization Queue (internal) Value
---------------------------------------- -----------
Overall Memory 1270833152
Target Memory 715005952
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway (internal) Value
---------------------------------------- -----------
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
(6 row(s) affected)
Medium Gateway (internal) Value
---------------------------------------- -----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
(6 row(s) affected)
Big Gateway (internal) Value
---------------------------------------- -----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
(6 row(s) affected)
Memory Pool Manager Pages
---------------------------------------- -----------
Reserved Current 0
Reserved Limit 120493
(2 row(s) affected)
Memory Pool (internal) Pages
---------------------------------------- -----------
Allocations 63366
Predicted 98314
Private Target 0
Private Limit 0
Total Target 183859
Total Limit 183859
OOM Count 0
(7 row(s) affected)
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- -----------
Allocations 61634
Rate 0
Target Allocations 147179
Future Allocations 0
Overall 155131
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- -----------
Allocations 1732
Rate 4
Target Allocations 87281
Future Allocations 0
Overall 155131
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- -----------
Allocations 0
Rate 0
Target Allocations 120489
Future Allocations 34944
Overall 155131
Last Notification 1
(6 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
August 11, 2011 at 8:04 am
It's not SQL. SQL has just under 4GB allocated.
Check perfmon, the process counters, working set for each running process on the server. That should help you turn up the culprit.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2011 at 9:28 am
Thanks for the reply.
I don't see any oher process that would be using all of this RAM.
At the moment Task Manager is stating that over 7GB of RAM is being used. Here are the results of DBCC MEMORYSTATUS (sorry for sending again but I thought it could be useful).
Memory Manager KB
---------------------------------------- -----------
VM Reserved 1716704
VM Committed 128336
AWE Allocated 3414016
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
---------------------------------------- -----------
VM Reserved 1713888
VM Committed 125632
AWE Allocated 3414016
MultiPage Allocator 62256
SinglePage Allocator 533832
(5 row(s) affected)
Memory node Id = 32 KB
---------------------------------------- -----------
VM Reserved 1728
VM Committed 1672
AWE Allocated 0
MultiPage Allocator 1600
SinglePage Allocator 533832
(5 row(s) affected)
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2696
MultiPage Allocator 3272
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 1620056
VM Committed 39152
AWE Allocated 3414016
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 2112
(7 row(s) affected)
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 128
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 360
MultiPage Allocator 72
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- -----------
VM Reserved 240
VM Committed 240
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 320
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- -----------
VM Reserved 15040
VM Committed 15040
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4712
MultiPage Allocator 32176
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 408
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 232
MultiPage Allocator 344
(7 row(s) affected)
MEMORYCLERK_SQLHTTP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 48
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_SNI (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_SNI (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 48
MultiPage Allocator 32
(7 row(s) affected)
MEMORYCLERK_FULLTEXT (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_BHF (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 48
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- -----------
VM Reserved 4224
VM Committed 4224
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_HOST (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 104
MultiPage Allocator 48
(7 row(s) affected)
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4256
MultiPage Allocator 5848
(7 row(s) affected)
MEMORYCLERK_SOSNODE (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 1520
(7 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4272
MultiPage Allocator 7368
(7 row(s) affected)
MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 24
SM Committed 24
SinglePage Allocator 0
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_XE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 192
MultiPage Allocator 96
(7 row(s) affected)
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 86552
MultiPage Allocator 1848
(7 row(s) affected)
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 338920
MultiPage Allocator 15400
(7 row(s) affected)
CACHESTORE_PHDR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 51960
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XPROC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 240
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 72
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_NOTIF (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_STACKFRAMES (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 472
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 64
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTO (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_EVENTS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 3960
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_CONVPRI (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 496
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_FULLTEXTSTOPLIST (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 9624
MultiPage Allocator 192
(7 row(s) affected)
USERSTORE_DBMETADATA (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 13456
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 6848
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 776
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 72
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 80
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 72
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1192
MultiPage Allocator 56
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 56
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1192
MultiPage Allocator 112
(7 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 424
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- -----------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4832
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- -----------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4856
MultiPage Allocator 0
(7 row(s) affected)
Buffer Pool Value
---------------------------------------- -----------
Committed 426534
Target 476905
Database 359536
Dirty 7808
In IO 0
Latched 0
Free 269
Stolen 66729
Reserved 0
Visible 193536
Stolen Potential 117130
Limiting Factor 10
Last OOM Factor 0
Last OS Error 0
Page Life Expectancy 1058
(15 row(s) affected)
Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory 825274368
Available Virtual Memory 253980672
Available Paging File 2875465728
Working Set 175439872
Percent of Committed Memory in WS 100
Page Faults 3490641
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
(10 row(s) affected)
Procedure Cache Value
---------------------------------------- -----------
TotalProcs 3897
TotalPages 61865
InUsePages 70
(3 row(s) affected)
Global Memory Objects Pages
---------------------------------------- -----------
Resource 332
Locks 607
XDES 31
SETLS 1
SE Dataset Allocators 2
SubpDesc Allocators 1
SE SchemaManager 501
SE Column Metadata Cache 725
SQLCache 500
Replication 2
ServerGlobal 33
XP Global 2
SortTables 3674
(13 row(s) affected)
Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 104310
Current Max 104310
Future Max 104310
Physical Max 139935
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
(11 row(s) affected)
Small Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 5490
Current Max 5490
Future Max 5490
(5 row(s) affected)
Optimization Queue (internal) Value
---------------------------------------- -----------
Overall Memory 1270833152
Target Memory 686915584
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway (internal) Value
---------------------------------------- -----------
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
(6 row(s) affected)
Medium Gateway (internal) Value
---------------------------------------- -----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
(6 row(s) affected)
Big Gateway (internal) Value
---------------------------------------- -----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
(6 row(s) affected)
Memory Pool Manager Pages
---------------------------------------- -----------
Reserved Current 0
Reserved Limit 117135
(2 row(s) affected)
Memory Pool (internal) Pages
---------------------------------------- -----------
Allocations 66724
Predicted 101707
Private Target 0
Private Limit 0
Total Target 183859
Total Limit 183859
OOM Count 0
(7 row(s) affected)
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- -----------
Allocations 65024
Rate 0
Target Allocations 147176
Future Allocations 0
Overall 155131
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- -----------
Allocations 1700
Rate -2
Target Allocations 83852
Future Allocations 0
Overall 155131
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- -----------
Allocations 0
Rate 0
Target Allocations 117135
Future Allocations 34983
Overall 155131
Last Notification 1
(6 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Here is a snap shot from Process Explorer:
ProcessPIDCPUPrivate BytesWorking SetVirtual Size
sqlservr.exe1348172,688 K171,328 K1,848,996 K
Rtvscan.exe197254,824 K82,788 K391,076 K
ReportingServicesService.exe166880,016 K60,724 K267,224 K
omtsreco.exe147234,292 K36,404 K165,092 K
msmdsrv.exe140438,728 K30,452 K217,732 K
svchost.exe83220,552 K28,536 K160,172 K
lsass.exe43624,968 K26,812 K91,192 K
MsDtsSrvr.exe123222,848 K23,260 K132,252 K
SQLAGENT.EXE188423,096 K17,444 K138,560 K
winlogon.exe3767,952 K11,312 K50,860 K
vmtoolsd.exe21327,364 K9,884 K63,892 K
explorer.exe105618,580 K9,340 K91,108 K
wmiprvse.exe34445,164 K8,708 K53,732 K
lserver.exe20326,208 K8,384 K83,176 K
procexp.exe33883.1323,420 K8,308 K100,660 K
dllhost.exe28042,672 K7,660 K30,748 K
wmiprvse.exe61123,784 K7,656 K34,800 K
services.exe4244,548 K7,552 K167,508 K
spoolsv.exe9484,028 K6,036 K37,072 K
SavRoam.exe18161,884 K5,572 K39,528 K
svchost.exe7161,904 K5,312 K25,896 K
DefWatch.exe11401,708 K4,868 K38,812 K
svchost.exe20082,544 K4,816 K57,740 K
msdtc.exe9801,940 K4,736 K25,416 K
svchost.exe7723,820 K4,584 K38,312 K
VMUpgradeHelper.exe23881,232 K4,420 K26,300 K
ccSetMgr.exe11123,884 K3,872 K33,176 K
winlogon.exe26283,900 K3,848 K50,072 K
sqlwriter.exe19321,008 K3,748 K21,460 K
csrss.exe3521,744 K3,716 K29,948 K
svchost.exe7921,336 K3,572 K22,020 K
csrss.exe37601,056 K3,424 K22,552 K
svchost.exe664952 K3,364 K19,512 K
ccEvtMgr.exe22444,076 K3,120 K39,976 K
AClntUsr.EXE1396972 K3,060 K28,424 K
vmacthlp.exe640580 K2,520 K16,032 K
svchost.exe1628608 K2,304 K11,508 K
logon.scr3820388 K1,672 K14,608 K
ACLIENT.EXE13884,912 K1,668 K52,708 K
rdpclip.exe2188980 K1,452 K30,344 K
SPBBCSvc.exe18525,596 K1,400 K63,420 K
taskmgr.exe5040984 K1,264 K26,160 K
AClntUsr.EXE2768964 K1,188 K28,424 K
VMwareTray.exe40642,024 K1,168 K32,900 K
ccApp.exe36163,692 K572 K31,644 K
smss.exe304156 K508 K3,816 K
System40 K260 K1,952 K
VPTray.exe2820960 K252 K27,480 K
System Idle Process096.870 K28 K0 K
Interruptsn/a< 0.010 K0 K0 K
Thanks for your help.
August 11, 2011 at 10:24 am
As a test I just ran a query to select * records from a table with over a million records in it and the memory usage spiked to maximum (8GB). I thought because I set the maximum server memory to 4096 MB that this couldn't happen. I'm missing something here.
August 11, 2011 at 10:34 am
If SQL's max memory is set to 4GB then the buffer pool cannot use more than 4GB memory. SQL does take memory outside of the buffer pool for things like CLR, backup buffers, thread stacks and a couple other things, but that's usually a few hundred MB at most.
Sure you've got max memory set right? Sure someone hasn't changed the setting?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2011 at 11:51 am
August 11, 2011 at 11:59 am
SQL should never be using the page file. If it is, something's already wrong.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2011 at 12:02 pm
slanky2 (8/11/2011)
Thanks for the reply.I don't see any oher process that would be using all of this RAM.
Step1:
Find who is consuming the memory currently?
SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)
go
Find which Queries plans and queries thar are cached ?
SELECT t.text, cp.objtype,qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qp.query_plan.exist('declare namespace n="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //n:MemoryFractions') = 1
Find Queries that are starving for memory?
SELECT TEXT, query_plan, requested_memory_kb,
granted_memory_kb,used_memory_kb, wait_order
FROM sys.dm_exec_query_memory_grants MG
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(MG.plan_handle)
go
These queries helps you in analysing memory usage...
August 11, 2011 at 12:06 pm
Those all check the contents of the buffer pool. SQL's max memory is set to 4GB, hence the buffer pool is limited to 4GB. The question being asked is what else other than the buffer pool is taking the memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2011 at 12:06 pm
August 11, 2011 at 12:07 pm
No.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2011 at 12:30 pm
Hi Gail,
That was what I thought. Since I set the max memory to use then queries shouldn't use anymore than that.
I ran
sp_configure 'min server memory', 1024
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
I did have the max memory set higher but I lowered it thinking it would help my issue.
Baffling!
August 11, 2011 at 12:41 pm
SQLFRNDZ
Gila
SQL should never be using the page file. If it is, something's already wrong.
So pagefile usage can be an indication to the memory bottleneck
Pagefile is used by the OS, not by SQL Server. iT IS VIRTUAL ram available for use by the OS for any variety of items. A good KB is http://support.microsoft.com/kb/2267427.
If pagefile is being used by the server, it will have a performance impact on SQL Server but will not necessarily be taking memory from SS. The goal is to adjust the max memory settings of SS high enough for operation without swapping memory with the OS, yet leave enough memory available to the OS so that it operates also without having to swap out memory to pagefile.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
August 11, 2011 at 12:43 pm
slanky2 (8/11/2011)
Hi Gail,That was what I thought. Since I set the max memory to use then queries shouldn't use anymore than that.
I ran
sp_configure 'min server memory', 1024
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
I did have the max memory set higher but I lowered it thinking it would help my issue.
Baffling!
Queries will not be able to use more than that 4GB. That's a limit on the size of the buffer pool. Not a suggestion or guideline. A limit.
As I mentioned earlier, SQL can use memory outside of the buffer pool, for things like thread stack, backup buffers, CLR, in-process linked server drivers and a couple other things, but unless you have some really strange stuff that won't be over a few hundred MB
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2011 at 12:47 pm
GilaMonster (8/11/2011)
slanky2 (8/11/2011)
Hi Gail,That was what I thought. Since I set the max memory to use then queries shouldn't use anymore than that.
I ran
sp_configure 'min server memory', 1024
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
I did have the max memory set higher but I lowered it thinking it would help my issue.
Baffling!
Queries will not be able to use more than that 4GB. That's a limit on the size of the buffer pool. Not a suggestion or guideline. A limit.
As I mentioned earlier, SQL can use memory outside of the buffer pool, for things like thread stack, backup buffers, CLR, in-process linked server drivers and a couple other things, but unless you have some really strange stuff that won't be over a few hundred MB
That's why I don't understand why some queries are causing the server to max out even though I set the max limit.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply