July 16, 2013 at 12:55 pm
users experience slowness on the sql server 2005 sp1.
Windows Server 2003 Standard Edition SP1
Physical RAM: 3.25 GB
I set the max memory of sql server to 2.25GB, but when I run the following query
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name = 'Total Server Memory (KB)'
OR counter_name = 'Target Server Memory (KB)';
and have a result of
counter_name cntr_valuecntr_type
Target Server Memory (KB) 162080065792
Total Server Memory (KB) 162080065792
It looks like sql server is not using all available memory. also the sometimes PLE drops below 300 suddenly. Procedure Cache Hit Ratio goes below 90. Can anyone help me find what goes wrong with the server? Thanks.
July 16, 2013 at 2:45 pm
is this 32bit, if so thats all the memory you will get unless you set the /3Gb flag in the boot.ini
---------------------------------------------------------------------
July 16, 2013 at 11:59 pm
As said by above users in 32 bit editions do you have /PAE /3GB in boot.ini file and "Lock pages in memory" should be given access for SQL server service account.
and is it any specific sql code/SP working slowly or compelete server?
Regards
Durai Nagarajan
July 17, 2013 at 6:37 am
Yes, this is 32bit server. I don't have /PAGE /3GB in boot.ini file. "Lock page in memory" is not given access to sql server service account.
Here is the DBCC Memorystatus result,
Memory Manager KB
------------------------------ --------------------
VM Reserved 1703288
VM Committed 1699800
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
------------------------------ --------------------
VM Reserved 1699192
VM Committed 1695856
AWE Allocated 0
MultiPage Allocator 48824
SinglePage Allocator 925104
(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 5736
MultiPage Allocator 1176
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 1636864
VM Committed 1636864
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 400
(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 2376
MultiPage Allocator 72
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 120
VM Committed 120
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 5504
VM Committed 5504
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4896
MultiPage Allocator 12936
(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 1480
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 80
MultiPage Allocator 192
(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 264
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 432
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 24
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 9456
MultiPage Allocator 5832
(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 24
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 17720
MultiPage Allocator 32
(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 829648
MultiPage Allocator 27280
(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 33360
MultiPage Allocator 704
(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 48
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 16
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 80
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 8
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 32
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 464
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 4912
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 4672
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 2096
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 1392
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 304
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 240
MultiPage Allocator 0
(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 3808
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 256
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 Commited 0
SinglePage Allocator 992
MultiPage Allocator 0
(7 row(s) affected)
Buffer Distribution Buffers
------------------------------ -----------
Stolen 3120
Free 6642
Cached 112518
Database (clean) 78934
Database (dirty) 1385
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
------------------------------ --------------------
Committed 202600
Target 202600
Hashed 80320
Stolen Potential 76832
External Reservation 0
Min Free 128
Visible 202600
Available Paging File 355960
(8 row(s) affected)
Procedure Cache Value
------------------------------ -----------
TotalProcs 3937
TotalPages 113599
InUsePages 894
(3 row(s) affected)
Global Memory Objects Buffers
------------------------------ --------------------
Resource 222
Locks 127
XDES 100
SETLS 4
SE Dataset Allocators 8
SubpDesc Allocators 4
SE SchemaManager 613
SQLCache 501
Replication 2
ServerGlobal 26
XP Global 2
SortTables 1523
(12 row(s) affected)
Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 68386
Maximum (Buffers) 68386
Limit 68400
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 72000
(11 row(s) affected)
Small Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 3638
Maximum (Buffers) 3638
Limit 3638
(5 row(s) affected)
Optimization Queue Value
------------------------------ --------------------
Overall Memory 1330200576
Target Memory 354762752
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway Value
------------------------------ --------------------
Configured Units 16
Available Units 16
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
(6 row(s) affected)
Medium Gateway Value
------------------------------ --------------------
Configured Units 4
Available Units 4
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 112359
Rate 653
Target Allocations 153663
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_STEAL Value
-------------------------------- --------------------
Allocations 3113
Rate -458
Target Allocations 43306
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_RESERVE Value
-------------------------------- --------------------
Allocations 0
Rate -3
Target Allocations 76803
Future Allocations 36152
Last Notification 1
(5 row(s) affected)
Thanks.
July 17, 2013 at 6:50 am
just set the /3GB switch in the boot.ini and get the server restarted.
See how that helps and take it from there, you may well still need to look at and tune your queries.
Have you ruled out obvious things like blocking?
---------------------------------------------------------------------
July 17, 2013 at 8:09 am
I don't see blocking transactions on the server. can you help me take a look at the dbcc memeorystatus result above?
July 17, 2013 at 8:57 am
sorry that would take more time than i have and we cannot be totaly sure memory is the main problem. Maximise the memory you do have available to you first.
---------------------------------------------------------------------
July 17, 2013 at 9:52 am
Thank you, George. adding /3g switch in the boot.ini file will require a server reboot, right? application team may not want to reboot the server. I will let them know anyway.
The server also have low PLE and Procedure Cache Hit Ratio? Do you think it is memory issue? Thanks.
July 17, 2013 at 10:01 am
yes it requires a reboot. They'll have to reboot it some time if they want it fixed.
You will know more after increasing memory SQL can use.
If PLE is consistently low it points to memory yes. The /3GB wont help proc cache
---------------------------------------------------------------------
July 17, 2013 at 12:27 pm
the server has a total of 3.25 GB memory. if turn on the /3gb, will it leave too less memory for OS?
July 17, 2013 at 2:28 pm
yes it would so you must set max memory in SQL (as I believe you have done at 2.25?)
---------------------------------------------------------------------
July 19, 2013 at 9:48 am
Yes, I did set the max memory. Thank you very much for the clarification, George.
SQL Server is using around 1.6 GB of memory and CACHESTORE_SQLCP uses almost 0.8GB. Do you think the ad-hoc plan chaching is causing the internal memory issue and alsocause low PLE. I have SQL Server 2005 SP1. Thanks
July 19, 2013 at 1:53 pm
buffer cache and proc cache are separate so I don't think it would affect PLE directly. Are you actually seeing any memory related error messages?
with such a large CACHESTORE_SQLCP you should test with sp_configure option 'optimise for ad-hoc workloads' turned on, this will reduce bloat.
Also SP1 is way out of date, you should patch to SP4, there may be improvements\fixes that will help
---------------------------------------------------------------------
July 19, 2013 at 2:04 pm
Can't find the option of 'optimize for ad hoc workloads', here is the error message
The configuration option 'optimize for ad hoc workloads' does not exist, or it may be an advanced option.
July 19, 2013 at 2:33 pm
as the message says it is an advanced setting
sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO
sp_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGURE
GO
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply