February 13, 2009 at 9:31 am
First, a little background:
OS: Windows Server 2003 R2 Enterprise x64 Edition SP2
SQL Server: 8.00.2040(SP4) x86
Memory: 16 GB Ram
-AWE Enabled
-no referrence to /3GB(I'm wondering if this should be there on since SQL is x86) or /PAE being enabled in the boot.ini
-Locked Pages in memory enabled
-Total Paging file for all drives is 24573 MB allocated on one drive by itself and ignored by the AV software.
-T1118 trace flag as startup per (http://support.microsoft.com/kb/328551) since we had issues with this. We added MAXDOP 1 to a few queries and seem to have slowed it down.
Through perfmon Total Server Memory(KB) is maxed out at 100%.
I had allocated 12336 as the Max Server Memory with no Min Memory.
I just tested a change like the following:
--the memory was set to 0-12336
EXEC sp_configure N'min server memory (MB)', N'2000'
GO
EXEC sp_configure N'max server memory (MB)', N'14000'
GO
RECONFIGURE WITH OVERRIDE
GO
Found this link:
http://www.sqlservercentral.com/articles/Performance+Tuning/troubleshootingsqlserverwiththesysperfinfotable/987/[/url]
SELECT 'Procedure
Cache Allocated',
CONVERT(int,((CONVERT(numeric(10,2),cntr_value)
* 8192)/1024)/1024)
as 'MBs'
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Buffer Manager' and
counter_name = 'Procedure cache pages'
UNION
SELECT 'Buffer Cache database pages',
CONVERT(int,((CONVERT(numeric(10,2),cntr_value)
* 8192)/1024)/1024)
as 'MBs'
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Buffer Manager' and
counter_name = 'Database pages'
UNION
SELECT 'Free pages',
CONVERT(int,((CONVERT(numeric(10,2), cntr_value)
* 8192)/1024)/1024)
as 'MBs'
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Buffer Manager' and
counter_name = 'Free pages'
Gives me 12348 MBs used.
Buffer Cache database pages = 12258 MB
Free pages = 10 MB
Procedure Cache Allocated = 34 MB
Here's the output of DBCC MEMORYSTATUS
Buffer Distribution Buffers
------------------------------ -----------
Stolen 6340
Free 2130
Procedures 5713
Inram 0
Dirty 245462
Kept 0
I/O 127
Latched 626
Other 1318610
(9 row(s) affected)
Buffer Counts Buffers
------------------------------ -----------
Commited 1579008
Target 1579008
Hashed 1564849
InternalReservation 1117
ExternalReservation 444
Min Free 1024
Visible 450176
(7 row(s) affected)
Procedure Cache Value
------------------------------ -----------
TotalProcs 1620
TotalPages 5713
InUsePages 3076
(3 row(s) affected)
Dynamic Memory Manager Buffers
------------------------------ -----------
Stolen 10762
OS Reserved 1624
OS Committed 1602
OS In Use 1506
General 3375
QueryPlan 5692
Optimizer 0
Utilities 483
Connection 1324
(9 row(s) affected)
Global Memory Objects Buffers
------------------------------ -----------
Resource 2424
Locks 938
XDES 70
SQLCache 93
Replication 2
LockBytes 2
ServerGlobal 27
(7 row(s) affected)
Query Memory Objects Value
------------------------------ -----------
Grants 2
Waiting 0
Available (Buffers) 329953
Maximum (Buffers) 330445
(4 row(s) affected)
Optimization Queue Value
------------------------------ -----------
Optimizing 0
Waiting 0
Available 32
Maximum 32
(4 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I'm getting OS avg disk queue length and OS disk time alerts through Idera's Diagnostic Manager but I believe I need to handle the OS Paging issue first.
Can anyone read through this garbled mess of mine and come out with any ideas?
If I haven't provided enough detail please let me know.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
February 13, 2009 at 10:03 am
Do you have the MIN and MAX set with the memory on SQL and not leave as the defaults.
Plus there is a option to set the lock in pages in memory that you need to set if using 64 bit.
Otherwise the paging will go crazy.
Hope that helps.
February 13, 2009 at 10:10 am
yep, that's in the first part of my post.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply