I don't think mine is using all of the memory it could on the server. I've read that the Task Manager does not give you an accurate measure of the memory usage but 130 MB max does not seem right on a 12 GB server. I've set the minimum to 2 GB in SSMS. The account that is running sqlservice has Lock Pages in Memory privileges. I've turned on AWE in SSMS just to be safe. We are experiencing high CPU usage and my theory is that it is caused my unnecessary I/O due to not being able to cache. The serve has Windows 2003 sp1. The SQL2005 Buffer Manager AWE Lookup maps\sec, stolen maps\sec, unmap calls/sec, unmap pages/sec, write maps/sec are all 0.
Any help would be appreciated. Below is the out from DBCC MEMORYSTATUS. This server also has an instance of sql2000 EE on it with max memory set to 5 GB and it seems to be running fine.
Memory Manager KB
--
VM Reserved 12687344
VM Committed 87304
AWE Allocated 3211008
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
--
VM Reserved 12681712
VM Committed 81752
AWE Allocated 3211008
MultiPage Allocator 19864
SinglePage Allocator 272960
(5 row(s) affected)
MEMORYCLERK_SQLGENERAL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4240
MultiPage Allocator 4984
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
- --
VM Reserved 12615680
VM Committed 16384
AWE Allocated 3211008
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 1128
(7 row(s) affected)
MEMORYCLERK_SQLQUERYEXEC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 88
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 672
MultiPage Allocator 96
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (Total) KB
- --
VM Reserved 240
VM Committed 240
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 320
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (Total) KB
- --
VM Reserved 3904
VM Committed 3904
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1280
MultiPage Allocator 472
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 728
MultiPage Allocator 0
(7 row(s) affected)
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
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 112
MultiPage Allocator 320
(7 row(s) affected)
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
(7 row(s) affected)
MEMORYCLERK_SNI (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 16
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
MEMORYCLERK_BHF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 56
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLQERESERVATIONS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 331616
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_HOST (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3416
MultiPage Allocator 11360
(7 row(s) affected)
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
(7 row(s) affected)
CACHESTORE_OBJCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 69400
MultiPage Allocator 672
(7 row(s) affected)
CACHESTORE_SQLCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 166824
MultiPage Allocator 32
(7 row(s) affected)
CACHESTORE_PHDR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 17944
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XPROC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 144
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_TEMPTABLES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
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
(7 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 456
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERKEK (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
CACHESTORE_BROKERRSB (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERREADONLY (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1784
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SCHEMAMGR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2192
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_DBMETADATA (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2800
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 872
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 256
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 168
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LBSS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 544
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1824
MultiPage Allocator 48
(7 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 288
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
- --
VM Reserved 8192
VM Committed 8192
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 20856
MultiPage Allocator 0
(7 row(s) affected)
Buffer Distribution Buffers
--
Stolen 13626
Free 28271
Cached 33153
Database (clean) 315604
Database (dirty) 2557
I/O 0
Latched 5
(7 row(s) affected)
Buffer Counts Buffers
--
Committed 393216
Target 393216
Hashed 318166
Stolen Potential 297983
External Reservation 28759
Min Free 64
Visible 393216
Available Paging File 2528231
(8 row(s) affected)
Procedure Cache Value
--
TotalProcs 2358
TotalPages 31877
InUsePages 425
(3 row(s) affected)
Global Memory Objects Buffers
--
Resource 257
Locks 2610
XDES 42
SETLS 2
SE Dataset Allocators 4
SubpDesc Allocators 2
SE SchemaManager 273
SQLCache 505
Replication 2
ServerGlobal 48
XP Global 2
SortTables 2
(12 row(s) affected)
Query Memory Objects Value
--
Grants 1
Waiting 0
Available (Buffers) 241218
Maximum (Buffers) 282670
Limit 282670
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 295470
(11 row(s) affected)
Small Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 12800
Maximum (Buffers) 12800
Limit 12800
(5 row(s) affected)
Optimization Queue Value
--
Overall Memory 2581880832
Target Memory 2209619968
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway Value
--
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 500000
Threshold 500000
(6 row(s) affected)
Medium Gateway Value
--
Configured Units 2
Available Units 2
Acquires 0
Waiters 0
Threshold Factor 12
(5 row(s) affected)
Big Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
(5 row(s) affected)
MEMORYBROKER_FOR_CACHE Value
-- --
Allocations 33157
Rate 2
Target Allocations 301911
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_STEAL Value
-- --
Allocations 963
Rate 14
Target Allocations 269729
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_RESERVE Value
-- --
Allocations 41452
Rate 21351
Target Allocations 315171
Future Allocations 70667
Last Notification 1
(5 row(s) affected)
What "edition" of 2003 is installed. You need to have Enterprise or Data Center installed for 12gb of RAM. See http://www.microsoft.com/windowsserver2003/evaluation/features/comparefeatures.mspx.Did you set PAE in the Windows boot.ini? If you right click on "My Computer" and select "Properties", how much memory does it show and does it say "Physical Address Extension" after the RAM?
2005 only allocates memory when needed, instead of 2000 which just grabs it all when the service starts.|||
win 2003 EE sp1.
The server doesn't seem to have a boot.ini file. Does 64 bit itanium servers usually have one?
The my computer reading is 11.9 GB of Ram. It does not say "Physical Address Extendsions"
The sql2000 instance is using 6 GB of ram so AWE should be working but it isn't for sql2005.
We use a lot of table variable in 2005 that are stored in memory if it is available an I think sql is thinking there isn't any and writing them to the tempdb instead.
Thanks for responding to my post
|||I forgot in 64bit you don't set PAE, that is only a 32 bit problem.In SQL 2005 tempdb usage was completely changed. It now stores a lot of temp data that 2000 had in RAM, in tempdb.
See: http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx|||I read that one yesterday. "System" has locked pages in memory rights. Our instance is running under "Local System" so it should be doing this, right.|||I talked to someone at Microsoft and they said to ignore Task Manager reading (nice). You can tell SQL memory usage by using Performance Monitor object SQL Memory Management counters "Target Server Memory" and "Total Server Memory". So, unless you are a DBA and know about this Task Manager misleads you.
No comments:
Post a Comment