November 8, 2012 at 9:30 am
I started evaluating a production OLTP backend that had been set up by someone who knew nothing about databases. They had the usual complaints (sometimes it's slow, but not always, and can't tell me exactly what or where it is slow) but it had been more or less stable for the last couple of years. The reason for my evaluation is that their colo hosts are telling them that their equipment is old and needs to be upgraded, so I'm trying to determine what their needs for the future are.
Currently, they're serving out anywhere from 60-120 simultaneous user connections to a 60GB database that doubles as a report server, averaging about 400 batch requests/sec during the busier portions of the day. This is running on a 8-proc Xeon 2.99GHz server with 96GB of RAM. When I popped into perfmon and loaded up my usual tuning counters, it didn't seem like memory was an issue at all (~100% buffer cache hit ratio, 0 checkpoint pages/sec, 0 lazy writes/sec, 0 memory grants pending, >300 page life expectancy, 0 page reads/sec), but SQL was also set up with the default config to use all of the available RAM. I dropped this down to 84GB to give the OS some room to breathe, as hard faults were being experienced by just logging into the OS. No change in perf counters over the next couple of days.
At this point, I started wondering how much RAM they really needed. I started cutting the amount allocated for SQL by a couple GB here and there to see where I'd start observing some indications of memory pressure. I got it down to 60GB when I noticed a couple checkpoint pages/sec and a couple page reads/sec. I increased the amount allocated up to 72GB and these counters stayed the same. I went back up to 84GB and still no change. Over the next 24 hours, the Page Life Expectancy dropped from well over 300 to 190. This really concerned me. SQL has not tried to request more than 60GB from the OS, even though it's configured to use as much as 84GB. The buffer cache hit ratio is still nearly 100%. I'm still seeing intermittent page reads, lazy writes, and checkpoint pages, but it's not bad. There are 0 memory grants pending. The page life expectancy has been stuck at 190 for the last 48 hours, even during no periods of no traffic.
Does anyone have any suggestions?
November 8, 2012 at 9:34 am
What is the Version/Edition of SQL Server? 32-bit or 64bit?
What is the Version/Edition of Windows? 32-bit or 64bit?
November 8, 2012 at 9:38 am
Sorry, SQL Server 2005 (9.0.4035) 64-bit, Windows Server 2008 SP1 64-bit.
Also, it's 4 physical dual-core Xeons, not 8 physical.
November 8, 2012 at 9:39 am
What are the results from this query?
select
[OBJECT_NAME]=
left(quotename(rtrim(a.[OBJECT_NAME]))+
quotename(rtrim(a.[COUNTER_NAME]))+
case
when rtrim(a.[INSTANCE_NAME]) = '' then ''
else quotename(rtrim(a.[INSTANCE_NAME])) end
,60),
[Memory GB]=
convert(decimal(10,3),round((a.[CNTR_VALUE]+0.000)/(1024.000*1024.000),3))
from
master.dbo.sysperfinfo a
where
a.[OBJECT_NAME] in ('SQLServer:Memory Manager')
November 8, 2012 at 9:42 am
[SQLServer:Memory Manager][Connection Memory (KB)] 0.001
[SQLServer:Memory Manager][Granted Workspace Memory (KB)]0.345
[SQLServer:Memory Manager][Lock Memory (KB)] 0.017
[SQLServer:Memory Manager][Lock Blocks Allocated] 0.053
[SQLServer:Memory Manager][Lock Owner Blocks Allocated]0.056
[SQLServer:Memory Manager][Lock Blocks] 0.002
[SQLServer:Memory Manager][Lock Owner Blocks] 0.002
[SQLServer:Memory Manager][Maximum Workspace Memory (KB)]63.754
[SQLServer:Memory Manager][Memory Grants Outstanding] 0.000
[SQLServer:Memory Manager][Memory Grants Pending] 0.000
[SQLServer:Memory Manager][Optimizer Memory (KB)] 0.002
[SQLServer:Memory Manager][SQL Cache Memory (KB)] 0.078
[SQLServer:Memory Manager][Target Server Memory (KB)] 84.970
[SQLServer:Memory Manager][Total Server Memory (KB)] 65.144
November 8, 2012 at 9:48 am
What is the SQL Server Edition?
You said "Windows Server 2008 SP1 64-bit"
What is the Edition? Is it Windows Server 2008 or Windows Server 2008 R2?
November 8, 2012 at 10:14 am
Windows Server 2008
November 10, 2012 at 2:12 am
Maybe mesaure it wrong
if i have a same problem
first i create table in db and then insert values
and run
CHECKPOINT
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
SELECT TABLE and look at the physical read
after 6 hours later
SELECT TABLE and look at the physical read if physical
read is 0 i think the page files still in buffer
again it 12 hours
and 24 hours
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply