Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sql server 2005 sp1 is slow Expand / Collapse
Author
Message
Posted Tuesday, July 16, 2013 12:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 14, 2014 7:13 AM
Points: 55, Visits: 1,303
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_value cntr_type
Target Server Memory (KB) 1620800 65792
Total Server Memory (KB) 1620800 65792

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.
Post #1474257
Posted Tuesday, July 16, 2013 2:45 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:23 AM
Points: 5,886, Visits: 13,047
is this 32bit, if so thats all the memory you will get unless you set the /3Gb flag in the boot.ini

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

Post #1474294
Posted Tuesday, July 16, 2013 11:58 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:45 AM
Points: 1,058, Visits: 2,696
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
Post #1474377
Posted Wednesday, July 17, 2013 6:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 14, 2014 7:13 AM
Points: 55, Visits: 1,303
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.
Post #1474555
Posted Wednesday, July 17, 2013 6:50 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:23 AM
Points: 5,886, Visits: 13,047
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?


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

Post #1474567
Posted Wednesday, July 17, 2013 8:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 14, 2014 7:13 AM
Points: 55, Visits: 1,303
I don't see blocking transactions on the server. can you help me take a look at the dbcc memeorystatus result above?
Post #1474634
Posted Wednesday, July 17, 2013 8:57 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:23 AM
Points: 5,886, Visits: 13,047
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.

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

Post #1474666
Posted Wednesday, July 17, 2013 9:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 14, 2014 7:13 AM
Points: 55, Visits: 1,303
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.
Post #1474702
Posted Wednesday, July 17, 2013 10:01 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:23 AM
Points: 5,886, Visits: 13,047
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


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

Post #1474705
Posted Wednesday, July 17, 2013 12:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 14, 2014 7:13 AM
Points: 55, Visits: 1,303
the server has a total of 3.25 GB memory. if turn on the /3gb, will it leave too less memory for OS?
Post #1474759
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse