Hi guys,

Just a quick question regarding a problem i seem to be having at the
moment which i can't seem to get my head around.

I have started at an organisation which has a very basic setup. A
clustered sql 2000 sp4 server with approx 90 databases on. Only a few
of these have much traffic on and performance on the whole seems ok.

The server is connected to a SAN with two LUNS being made available
for the data and log drives. Performance in general is ok with disk
latency for reads and writes on both luns around 10-20ms. There is a
constant stream of reads using approx 1.5 - 2 mb per second from the
data drive. On the odd instance disk read go upto 20-30mb per second
and the disk queue may rise to 10 for a very short period of time but
on the whole the disks seam to handle it and latency for the above
peak may go upto 200ms.

The problem seems to happen when checkpointing seems to happen. I have
set the trace flags so i can see what database is checkpointing at any
given time and when the highest throughput database checkpoints we see
performance problems.

Latency can go to 20-30 seconds for 30-60 seconds which effectively
halts processing for this time. Errors start appearing in the sql log
for files taking longer than 15s to respond.

What's throwing me is that the throughput on the disks doesn't seem
very high at these points. As an example checkpoint pages sec may be
around the 200-300 mark but the actual bytes sent to disk is around
around 1.5 - 2mb and we've already seen above that the disks seem to
cope with around 40-50mb sec. When the checkpointing happens the disk
queue length shoots to around 200-300.

What i have noticed is that the latancy on the log drive also seems to
shoot up at the same time so don't know if the problem is somewhere
between the server and SAN. Its just thrown me that the performance
gets so bad on checkpoints on such low throughput. I thought it may
have been down to the block size written which is about 10k on
checkpoints but looking through the perfmon feeds this blocksize seems
to happen at points not during a checkpoint with no problem.

One point to note is that when the lazy writer process kicks in that
although performance decreases ie latency may go to 200/330ms but
nothing like the problems experiencing on the checkpointing.

To top it off, this is a 24/7 system with management not allowing any
downtime for running SQLIO or any tools like that. The SAN guys say
everything is running fine with the SAN only being 36% utilised.
Saying that, their snapshot of san performance is running every 1
minute so i guess they could miss the 30-60 secs timeframe when i see
problems.

If anyone has any suggestions as to what may be happening here then
any advice would be much appreciated.

Thanks

Ian.

The SAN guys never see any problem - there is a constant fight between
DBAs and SAN guys. Maybe the advent of SSD disks, will make those fights
of the past.

The traffic in this newsgroup is not that big, so you may be better off
asking in for instance
http://social.msdn.microsoft.com/For...engine/threads

I have heard several war stories about the problems with misconfigured
SANs from my MVP mates, but I don't really feel qualified to give any
advice in this issue myself.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 31 Aug, 21:44, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Thanks erland, i'll post it there.