ratio of disk traffic to network traffic

What is a normal ratio of disk traffic to network traffic on a SQL
Server? I've got 5x the disk traffic as network traffic and wondering
whether this indicates inefficiency in query design.The answer is, as is so often with computers and software, "it depends."
If you are talking about actual disk reads/writes, then...
For a well designed and configured OLTP database, the ratio between disk and
NIC bytes can be very small -smaller than 1:2 (with adequate memory and
fully cached data).
However, for OLAP (or Reporting Servers), the ratio can be very high (I've
seen over 1000:1) since a lot of output in that usage is summary data.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"DCole" <cole.consulting@.gmail.com> wrote in message
news:1161978008.508056.73230@.m7g2000cwm.googlegroups.com...
> What is a normal ratio of disk traffic to network traffic on a SQL
> Server? I've got 5x the disk traffic as network traffic and wondering
> whether this indicates inefficiency in query design.
>|||DCole wrote:
> What is a normal ratio of disk traffic to network traffic on a SQL
> Server? I've got 5x the disk traffic as network traffic and wondering
> whether this indicates inefficiency in query design.
>
There isn't a "normal" ratio for such a thing. For instance, consider
the following OVERLY SIMPLIFIED example:
1. I submit a query to SQL - "SELECT DISTINCT TOP 1 Address1 FROM
CustomerAddress", less than 40 characters being piped in as network traffic.
2. CustomerAddress contains 50 million rows, with Address1 being the
first line of a customer address, defined as a VARCHAR(255). There are
no indexes on the CustomerAddress table.
3. To find all of the distinct Address1 values, SQL is going to scan
the Address1 table, looking at each of the 50 million rows - lots of
disk I/O.
4. The TOP 1 clause will result in only a single row being returned by
the query, containing a single VARCHAR(255) column - AT MOST 255
characters of data will be returned.
So, you can see there is very little network traffic, but a large amount
of disk I/O produced. Query efficiency, indexes, schema design, all
will have a direct effect on the amount of disk I/O that you see.
Network I/O means very little in relation to disk I/O.
Tracy McKibben
MCDBA
http://www.realsqlguy.com