Re: Multi-Channel Raid VS SAN Storage

For transaction logging, ideally it should be < 10ms. Generally speaking, for
small I/Os (e.g. 12KB per I/O), an I/O should be less tha 15~20ms (on some
systems this would be too high a threshold). If it's into 50ms, you
definitely have an I/O latency problem.

Linchi

"Eli Silverman" wrote:

We do have a hardware refresh policy. Unfortunately our company was acquired
by another and even though the server refresh has been approved in our budget
each of the past two years they have been dragging their feet allowing us to
do so.

I will take a look at the avg disk sec/read and avg disk sec/write settings.
Considering the drives are 15krpm ul320, what is an acceptable reading?

"Linchi Shea" wrote:

The server itself is about 5 and a half years old. I know it is a Dual

A 5~6 years old server is a very old server. You would definitely benefit
from regular hardware refresh. That's right, regardless of how a sever may
perform, you should put in a policy to refresh your hardware (primarily your
server) once every three/four years or so. If you don't have any current
performance issue, this keeps you ahead of the game. If you do have some
performance issue, this shold help alleviate the performance problems. This
should also help improve stability and so on.

The one reading I did see was that when we ran one of the reports the disk
Queue lengh waivered between 1 and 6 every second.

You should also look at Avg Disk sec/Read and Avg Disk sec/Write to see what
kind of storage I/O latency you are experiencing.

As I mentioned, the server is one node in a cluster environment, and SQL is
the main service running but I don't know what overhead the cluster service
adds.

The cluster service does NOT add any overhead.

Linchi

"Eli Silverman" wrote:

The server itself is about 5 and a half years old. I know it is a Dual
processor 2.2ghz Xeon. I am having our network people try to ge me some
numbers but I am not versed enough in performance monitor to know what
appropriate readings should be, or even which ones should be run.

Seeing that the RAID is shared by 3 servers we are having dificulty
determining if the server is disk bound.

You are correct, I am guessing. To date I have been relying on our IT staff
to monitor the performance but I am not certain they know that more about it
than me.

The one reading I did see was that when we ran one of the reports the disk
Queue lengh waivered between 1 and 6 every second.

As I mentioned, the server is one node in a cluster environment, and SQL is
the main service running but I don't know what overhead the cluster service
adds. IIS is also running and this is just one of the 4 databases hosted on
the SQL server.



"Andrew J. Kelly" wrote:

Having 3GB of memory for SQL Server these days is just asking for trouble.
Heck my laptop has 4GB alone. But if the db is only 1.5GB that still should
be enough for most things. I suspect you have other things running on the
server than just SQL Server. If so it is hard to say how much more you need.
But the bottom line is you need to narrow down where the bottle necks really
are. It sounds like you are guessing at the moment. When these reports are
running are you CPU or Disk bound? Are you sure you are not simply being
blocked? How many CPU's do you have? Have you run any traces to see which
queries may need tuning?

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Eli Silverman" <EliSilverman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8C7E94D7-7DA8-42F8-9198-5A22F6CB71DB@xxxxxxxxxxxxxxxx
Thanks for the quick feedback.
The idea of keeping the database in memory is appealing, but can that be
implemented in a clustered environment?
Also how would one implement that any way.
We actually are running RAID 1+0 and our aplication is definately more
read
than write intensive.

We get tollerable performance at best. but the second someone tries to run
a
report or copy a large datafile performance slows to a crawl. A page that
typically loads in 1-2 seconds can take 20-30. and the report that takes
10-15 seconds to run when nobody is on can take 2-3 minutes.
Our report SPs are vry tempDB and memory intensive.
A typical report may require 15-20 tables

We have found that in order for them to run in a timely fashion we have
sometimes needed to process the data in several separate stems creating 3
or
4 temp table to hold the data and then join all of the temp tables
together
at the end.
Maybe we should be using @ memory tables for processing the reports
instead
of # temp tables but for the most part I seemd to get better performance
out
of the # temp tables.
Probably because our SQL server only has 3 GB of RAM.

There are a number of inherited inefficiencies from the original design
that
we are working on eliminating so I think the IO is much more intensive
than
it needs to be.
Additionally there is a lot of IO from the fileshare.

Also we have 45 people that access the database continually in house and
roughly 3000 people that have access to one of the 5 client web sites that
access this particular database. We also have 3 other databases that
reside
on the same server but they are smaller and see much less activity. Our
sites
use pooled connections but we typically have about 60 connections to the
SQL
server at any given time.

I think my best choice at the moment is to try and convice them that SQL
needs it's own SAN device, or at least a dedicated IO channel on the SAN.
And
to mak ecertain that we can get as much memory as possible.


"Andrew J. Kelly" wrote:

Eli,

Explaining SAN storage can take up a whole week but some general comments
from what you have said so far. First off a 1.5GB database is extremely
small for SQL Server these days. You should be able to fit the entire
database in memory so most of the IO should be logical and not physical
anyway. The exception would be the transaction log file. But again with a
db
so small you probably don't have much I/O to begin with. If you are
currently using a single Raid 1 array and share that with Exchange and a
file share and get OK performance now you should only see improvement
with a
SAN. Even though you will most likely still share an array on the SAN
with
other apps you will most likely have many more spindles in the array than
you have now with a Raid 1. The SAN also most likely has more cache to
buffer the writes which will also help. The key in your case is to ensure
you have enough memory to keep the db in cache and you should be pretty
set.


--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Eli Silverman" <EliSilverman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4F01EC36-1F3A-4B73-8004-313E186878B3@xxxxxxxxxxxxxxxx
I am hoping someone can give me some advice about the performance
difference
between Multi-Channel Raid vs SAN storage.

My basic question has to do with Performance and optimization.
Assuming I have the same spec drives (size, speed, throughput, ets...)
is
SQL better off with a Multi-channel raid controller or a SAN storrage
array?
Are there such things as multi-channel SAN storrage Arrays?
Can anybody direct me to some good white papers abbout SAN storage and
SQL?

We currently run a two node Clustered SQL 2000 environment.
Unfortunately the nodes are attached to a common 1 channel raid device
so
everything runs off the 1 Raid array.

We are getting ready to upgrade our hardware, however our parent
company
is
insisting that we implement a SAN for all of our data storage and I am
wondering if that is the best solution for our SQL server. We will also
be
upgrading to SQL 2005 at the same time.

I know we could benefit from moving the logs, tempdb and possibly some
of
our indexes over to separate IO channels, but I am uncertain as to how
a
SAN
environment affects these concepts.

I don't think the database is all that big, roughly 1.5GB but there are
a
couple of sizeable tables. one audit table has over 5 million records
and
our
expense tables have about 300K and will probably grow by at least
25-30K
records per year.

The RAID array has been divided into multiple volumes. One dedicated to
SQL,
another to Exchange and a third to general fileshare. But they are
still
on a
single SCSI channel.

My concern is that when we migrate to our new hardware they are going
to
try
and implement the same architecture and drive throughput is going to
continue
to be stolen from SQL by the other servers and applications. I want to
make
certain tha I ask all the right questions and make all the right points
to
avoid having SQL crippled by our new infrastructure.

Thanks in advance for any advice.

Eli Silverman










.

Relevant Pages

  • Re: Add new cluster and use existing LUNs?
    ... Meta-volumes are dedicated to a single host, single activity (OS, swap file, ... varied activity (DBMSes, Messaging Server, File Server, Web Servers, ... Some of the physical spindle limitations can be addressed through the SAN ... >> test would be to run a simulated SQL I/O load on the newly created LUNs ...
    (microsoft.public.sqlserver.clustering)
  • Re: Multi-Channel Raid VS SAN Storage
    ... A 5~6 years old server is a very old server. ... As I mentioned, the server is one node in a cluster environment, and SQL is ... We actually are running RAID 1+0 and our aplication is definately more ... needs it's own SAN device, or at least a dedicated IO channel on the SAN. ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL I/O Errors
    ... Thanks ever so much for this, it was in fact the fibre cable between the SQL ... We have a major problem with our Test SQL Server, ... SQL Data resides on the E Drive which is SAN Fibre attached, ... At first we thought it was an Operating System fault and completely ...
    (microsoft.public.sqlserver.server)
  • Re: Multi-Channel Raid VS SAN Storage
    ... The server itself is about 5 and a half years old. ... As I mentioned, the server is one node in a cluster environment, and SQL is ... We actually are running RAID 1+0 and our aplication is definately more ... needs it's own SAN device, or at least a dedicated IO channel on the SAN. ...
    (microsoft.public.sqlserver.setup)
  • Multi-Channel Raid VS SAN Storage
    ... SQL better off with a Multi-channel raid controller or a SAN storrage array? ... Can anybody direct me to some good white papers abbout SAN storage and SQL? ...
    (microsoft.public.sqlserver.setup)