Re: db performance on san mapped to one drive
- From: "Mike Epprecht \(SQL MVP\)" <mike@xxxxxxxxxxxx>
- Date: Sun, 12 Jun 2005 12:46:31 +0200
Hi
The disk queues do not look too good.
What type of SAN is it? EMC? Depending on the SAN technology, the disk
layout can play a big role. RAID-10 preferably.
It does not help splitting up the volume into different volumes / drive
letters, unless the volumes are going to be on different spindles.
The Transaction log writes are generally sequential, whilst the database is
usually random. Having the same disks try to service both types of requests
leads to terrible disk queue lengths.
The OS can't do much more to help the SAN drives, they are the ones not
coping.
With 4GB or RAM and 32 Bit OS, SQL Server can only use up to 3GB and the OS
gets the other 1GB (assuming /3GB is in boot.ini) Considering the load on
the server, 4GB is a bit little. 8GB would start to sound right, 16 might
become very sweet. With more RAM, SQL Server can cache more, resulting in
less reads being required. In effect, you have too many processors for the
RAM.
There are other factors that can bury the best hardware. Bad database and
index design, and terrible queries. e.g bad indexing results in SQL Server
having to read though all the data in a table, this causes more read
requests. this slows down writes, which in turn slow down reads....on and on
and on.
Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@xxxxxxxxxxxx
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<paulaner@xxxxxxxxx> wrote in message
news:0hdka111vmelvphc86a7rrpcopjpdjt8d9@xxxxxxxxxx
>
> I'm working on a performance problem on a SQL server 2000 W2K SP4
> Active / Active cluster. The primary database (bigdb) is 140 Gb and
> has 1200 users. Looking at perfmon I am suspecting an I/O bottleneck.
> The 8 CPUs in the system run all under 50%, and the 4 Gb RAM shows
> only 3.2 Gb in use. I'm an old Windows guy, but new to SQL
> performance tuning. The system is all up to par with patches etc.
>
> The disk is a screaming fast SAN with great cache. The databases are
> all on a single drive mapped to H: for this instance. Here's some
> results from a ~20 hr perfmon analysis with ~4500 values of drive H:
>
> Counter Average Min Max
> Disk Bytes / sec: 4,406,000 52,000 39,912,000
> Disk Queue Length: 3.50 0 255
> %Disk Time: 344 0.12 15,500
>
> So, I'm wondering if separating the databases onto different drive
> letters would help disk time and queue length. For example: tempdb on
> H:, bigdb-data on I:, bigdb-index on J:, bigdb-logs on K: and master
> etc. on J:. All of these would still on the same san fiber, but maybe
> W2K would do a better job managing the data using multiple disks to
> the same SAN? Thoughts?
>
> Are there any tuning tweeks I can make to W2K to give more priority to
> the SQL SAN drive? The internal drives are not have any issues...
>
>
>
.
- References:
- db performance on san mapped to one drive
- From: paulaner
- db performance on san mapped to one drive
- Prev by Date: db performance on san mapped to one drive
- Next by Date: SQL high availability and balancing
- Previous by thread: db performance on san mapped to one drive
- Next by thread: SQL high availability and balancing
- Index(es):
Relevant Pages
|
