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

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

Ratio Calculation based on calculations from two different dimensions

I am trying to come up with a way to calculate a ratio that is based on the values from two different dimension calculations. The values are based on how a person responded to a question on a survey. I have one calculation that I can get with the following MDX statement:

select {Measures.[Brand Usage Last Try 2-6]} on columns,

non empty([brand usage].[brand label].children) on rows

from cube

RESULTS:

Brand Usage Last Try 2-6

Brand 1

164.277

Brand 2

208.513

Brand 3

131.193

(The [Brand Usage Last Try 2-6] measure is the Measures.Weight aggregate calculation for people that responded with a value from 2 to 6 for a particular brand).

The second calculation is the following:

with member measures.hrd as ([Brand Awareness].[VarResponse].[Var3].&[hrd].&[heard_of_brand], [Measures].[Weight])

select {measures.hrd} on columns,

non empty([brand awareness].[brand label].children) on rows

from cube

RESULTS:

hrd

Brand 1

301.601

Brand 2

462.385

Brand 3

361.533

I need to be able to take the calculation from the first one and divide it by the value for the second calculation based on brand. I tried using the linkmember to retrieve the values based on the brand, but it is not returning the results that I expected.

with member measures.hrd as ([Brand Awareness].[VarResponse].[Var3].&[hrd].&[heard_of_brand], [Measures].[Weight])

member measures.hrdlnk as (measures.hrd, linkmember([Brand Usage].[Brand Label].currentmember,[Brand Awareness].[Brand Label]))

member measures.ratio as (iif(measures.hrdlnk > 0, measures.[brand usage last try 2-6]/measures.hrdlnk, null)*100)

select {measures.[Brand Usage Last Try 2-6], measures.hrd, measures.hrdlnk,

measures.ratio} on columns,

non empty([brand usage].[brand label].children) on rows

from cube

RESULTS:

Brand Usage Last Try 2-6

hrd

ratio

Brand 1

164.277

301.601

54.47

Brand 2

208.513

(null)

(null)

Brand 3

131.193

271.332

48.35

Somehow the first brand ratio calculation comes out right, but that is the only one. Both of these dimensions reference a brand dimension. Each dimension, [Brand Awareness] and [Brand Usage], contain questions and potential responses for each type of brand in the brand dimension. Both dimensions have a hierarchy that I created [VarResponse] in them that is based on the type of question [Var3] (which is the first three characters of the Variable name) and then the response [Category Name].

"Both of these dimensions reference a brand dimension. Each dimension, [Brand Awareness] and [Brand Usage], contain questions and potential responses for each type of brand in the brand dimension." - could you explain how dimension usage is configured for the cube, in terms of these 3 dimensions and the [Measures].[Weight] measure group? Assuming that [Brand] has a regular relation to the fact table, and that [Brand Awareness] and [Brand Usage] each has a many-to-many relation to the measure group, there may be unintended interaction when computing hrdlnk, so try this:

member measures.hrdlnk as (measures.hrd, linkmember([Brand Usage].[Brand Label].currentmember, [Brand Awareness].[Brand Label]), [Brand Usage].[Brand Label].[All])

|||

This calculation did work and produced the results that I was looking for, thank you so much.

In regards to the configuration, the brand table is linked to each of the dimension tables, awareness and usage. The brand table does not directly relate to the fact table. The dimension tables, awareness and usage have a regular relationship to the fact table and they both have a key designated in the table. This is how it is currently configured.

Brand (Brand_ID) -->Brand Awareness (Brand_Awareness_ID, Brand_ID)

-->Brand Usage(Brand_Usage_ID, Brand_ID)

-->Fact Table(Date_ID, Respondent_ID, Brand_Awareness_ID, Brand_Usage_ID)

The granularity within the fact table is not what I am typically used to, so this is kind of an odd setup.