Very Computer

by **Will Standle** » Wed, 20 Sep 2000 04:00:00

Are there some benchmarks that can be used to predict some typical/average
execution times for a query to execute and return results with sql server
running on a win2k machine.

Given a defined machine, with defined resources, with defined tables, record
count, etc.

In my case I am looking at retrieving a set of records from a large database
that match a certain criteria... and having the search be from a single
table... no joins... even though there may be a number of tables in the
database. Record count would be between 1 million and 10 million.

We are attempting to estimate system capacity... and... obviously... are new
to all this.

thanks for any help

Will

by **Keith Kratochvi** » Wed, 20 Sep 2000 04:00:00

I do not think that you can benchmark this.
You probably cannot even guess at what it would be.

There are many factors including.
Server load --when under a heavy load the result might take longer to
return.
Network capacity --how quickly can you move data across the network. How
much other traffic is on the network taking that bandwidth?
Client system--if you are retrieving 1M to 10M rows, the client needs to
'hold' this data. How well it does this is due to processor, ram, the
application that you are loading / displaying the information into.

As you can see, I covered the server, the network, and the client and this
list is by no means complete.

Keith

Quote:> Are there some benchmarks that can be used to predict some typical/average
> execution times for a query to execute and return results with sql server
> running on a win2k machine.

> Given a defined machine, with defined resources, with defined tables,
record
> count, etc.

> In my case I am looking at retrieving a set of records from a large
database
> that match a certain criteria... and having the search be from a single
> table... no joins... even though there may be a number of tables in the
> database. Record count would be between 1 million and 10 million.

> We are attempting to estimate system capacity... and... obviously... are
new
> to all this.

> thanks for any help

> Will

by **Will Standle** » Wed, 20 Sep 2000 04:00:00

Keith,

The client is not retrieving 1 to 10 million records... there are 1 to 10
million records in the table being queried. There may be zero to a few
hundred matching records for any particular query.

Question: How can one do serious system design and estimate system
requirements (hardware, software, network configuration, etc) if there are
too many variables?

How about nailing down some assumptions to get an idea of the 'ideal world'
performance...

one client's connected to
one server running sql server 7 on win2k server
on a LAN, each unit side by side on the table
512 meg ram
30g hard disk
500 mhz p3 single processor
100mhz network connection via crossover cable
the client is programmed to issue queries to the server as fast as the
server can handle them

If this reference configuration won't deliver us a 'reference model' we can
use... define one that can be used to minimize the variables.

No criticism here... just trying to get to a reference point...

(The analogy is electronic circuit design... there is no perfect transistor
or op amp... no perfect resistor or inductor... but to complete a design you
start with ideal components in ideal conditions, then begin to introduce
'real world' conditions such as defined gain vs unlimited gain, value
tolerances, temp variances, etc...)

thanks - Will

> I do not think that you can benchmark this.
> You probably cannot even guess at what it would be.

> There are many factors including.
> Server load --when under a heavy load the result might take longer to
> return.
> Network capacity --how quickly can you move data across the network. How
> much other traffic is on the network taking that bandwidth?
> Client system--if you are retrieving 1M to 10M rows, the client needs to
> 'hold' this data. How well it does this is due to processor, ram, the
> application that you are loading / displaying the information into.

> As you can see, I covered the server, the network, and the client and this
> list is by no means complete.

> Keith

> > Are there some benchmarks that can be used to predict some
typical/average
> > execution times for a query to execute and return results with sql
server
> > running on a win2k machine.

> > Given a defined machine, with defined resources, with defined tables,
> record
> > count, etc.

> > In my case I am looking at retrieving a set of records from a large
> database
> > that match a certain criteria... and having the search be from a single
> > table... no joins... even though there may be a number of tables in the
> > database. Record count would be between 1 million and 10 million.

> > We are attempting to estimate system capacity... and... obviously... are
> new
> > to all this.

> > thanks for any help

> > Will

by **BP Margoli** » Wed, 20 Sep 2000 04:00:00

Will,

Benchmarks is a "loaded" term, as you might know. There are standard TPC
benchmarks (see www.tpc.org), however from a practical matter these are
worthless for anything other than bragging rights. If you investigate the
equipment used to achieve the benchmarks posted, you'll find that Microsoft
and Oracle and IBM and everyone else spends literally millions of dollars on
hardware ... things like systems with 96 cpu's and the such ... just not
what most businesses can avoid :-)

Additionally, to the best of my knowledge, if you read just about any
license for a RDBMS, it actually prohibits one from publishing benchmark
results without the explicit approval of the vendor of the RDBMS.

So what are you left with ... well, at least with SQL Server, you can get
usually get a free 120-day evaluation copy from the Microsoft Web site.
Presumably, within the space of 120 days you can get a feel for the
performance that SQL Server can deliver. The one proviso that I will offer
is: if you are new to SQL Server (or to Oracle, or to DB2, or to ...) hire
someone who is knowledgeable to assist you in setting up the environment.
Yeah, I know it can be expensive, but I've seen situations where a lack of
knowledge has unfairly caused one RDBMS to apparently perform miserably.
BTW, this can be viewed as a justification for why RDBMS vendors prohibit
independent benchmarks from being published.

----------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:> Are there some benchmarks that can be used to predict some typical/average
> execution times for a query to execute and return results with sql server
> running on a win2k machine.

> Given a defined machine, with defined resources, with defined tables,
record
> count, etc.

> In my case I am looking at retrieving a set of records from a large
database
> that match a certain criteria... and having the search be from a single
> table... no joins... even though there may be a number of tables in the
> database. Record count would be between 1 million and 10 million.

> We are attempting to estimate system capacity... and... obviously... are
new
> to all this.

> thanks for any help

> Will

by **Will Standle** » Thu, 21 Sep 2000 04:00:00

BP,

I would like to find a sql server expert to provide some email
coaching/advice... possibly a few phone calls.

1 - How would you suggest I find such an expert? Post a note on this
newsgroup?
2 - How can I evaluate their level of knowledge? Choosing the 'kinda know
it' advisor can be a very expensive mistake.

I'm posting a separate note about how to design a large database system -
software - hardware - plan for capicity.

From what I read todate... everybody just uses gross overkill to make sure
they are covered... not very scientific...

Best regards,

Will

> Will,

> Benchmarks is a "loaded" term, as you might know. There are standard TPC
> benchmarks (see www.tpc.org), however from a practical matter these are
> worthless for anything other than bragging rights. If you investigate the
> equipment used to achieve the benchmarks posted, you'll find that
Microsoft
> and Oracle and IBM and everyone else spends literally millions of dollars
on
> hardware ... things like systems with 96 cpu's and the such ... just not
> what most businesses can avoid :-)

> Additionally, to the best of my knowledge, if you read just about any
> license for a RDBMS, it actually prohibits one from publishing benchmark
> results without the explicit approval of the vendor of the RDBMS.

> So what are you left with ... well, at least with SQL Server, you can get
> usually get a free 120-day evaluation copy from the Microsoft Web site.
> Presumably, within the space of 120 days you can get a feel for the
> performance that SQL Server can deliver. The one proviso that I will offer
> is: if you are new to SQL Server (or to Oracle, or to DB2, or to ...) hire
> someone who is knowledgeable to assist you in setting up the environment.
> Yeah, I know it can be expensive, but I've seen situations where a lack of
> knowledge has unfairly caused one RDBMS to apparently perform miserably.
> BTW, this can be viewed as a justification for why RDBMS vendors prohibit
> independent benchmarks from being published.

> ----------------------------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.

> > Are there some benchmarks that can be used to predict some
typical/average
> > execution times for a query to execute and return results with sql
server
> > running on a win2k machine.

> > Given a defined machine, with defined resources, with defined tables,
> record
> > count, etc.

> > In my case I am looking at retrieving a set of records from a large
> database
> > that match a certain criteria... and having the search be from a single
> > table... no joins... even though there may be a number of tables in the
> > database. Record count would be between 1 million and 10 million.

> > We are attempting to estimate system capacity... and... obviously... are
> new
> > to all this.

> > thanks for any help

> > Will

by **BP Margoli** » Thu, 21 Sep 2000 04:00:00

Will,

Excellent questions ... but let me ask one in turn ... How do you know when
you commit to a marriage that it won't turn out to be "a very expensive
mistake". It would be nice for there to be guarantees in life ...
unfortunately, other than death and taxes, I know of very few. Basically, as
with most major decisions, it comes down to using your best judgment, asking
individuals whose judgment you trust for names of "SQL Server experts"
(side note ... I have the great good fortune to be a SQL Server MVP ...
there are exactly 20 of us in the world ... and you know what, I have
problems calling myself a "SQL Server expert" ... the things I don't know
about SQL Server far exceed the things I do know about SQL Server ... the
only thing I can usually claim, is that my level of knowledge about SQL
Server is usually better than most of the individuals that I encounter in
this industry.)

Sorry that I can't give you an answer you wanted.

----------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

> BP,

> I would like to find a sql server expert to provide some email
> coaching/advice... possibly a few phone calls.

> 1 - How would you suggest I find such an expert? Post a note on this
> newsgroup?
> 2 - How can I evaluate their level of knowledge? Choosing the 'kinda know
> it' advisor can be a very expensive mistake.

> I'm posting a separate note about how to design a large database system -
> software - hardware - plan for capicity.

> From what I read todate... everybody just uses gross overkill to make sure
> they are covered... not very scientific...

> Best regards,

> Will

> > Will,

> > Benchmarks is a "loaded" term, as you might know. There are standard TPC
> > benchmarks (see www.tpc.org), however from a practical matter these are
> > worthless for anything other than bragging rights. If you investigate
the
> > equipment used to achieve the benchmarks posted, you'll find that
> Microsoft
> > and Oracle and IBM and everyone else spends literally millions of
dollars
> on
> > hardware ... things like systems with 96 cpu's and the such ... just not
> > what most businesses can avoid :-)

> > Additionally, to the best of my knowledge, if you read just about any
> > license for a RDBMS, it actually prohibits one from publishing benchmark
> > results without the explicit approval of the vendor of the RDBMS.

> > So what are you left with ... well, at least with SQL Server, you can
get
> > usually get a free 120-day evaluation copy from the Microsoft Web site.
> > Presumably, within the space of 120 days you can get a feel for the
> > performance that SQL Server can deliver. The one proviso that I will
offer
> > is: if you are new to SQL Server (or to Oracle, or to DB2, or to ...)
hire
> > someone who is knowledgeable to assist you in setting up the
environment.
> > Yeah, I know it can be expensive, but I've seen situations where a lack
of
> > knowledge has unfairly caused one RDBMS to apparently perform miserably.
> > BTW, this can be viewed as a justification for why RDBMS vendors
prohibit
> > independent benchmarks from being published.

> > ----------------------------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.

> > > Are there some benchmarks that can be used to predict some
> typical/average
> > > execution times for a query to execute and return results with sql
> server
> > > running on a win2k machine.

> > > Given a defined machine, with defined resources, with defined tables,
> > record
> > > count, etc.

> > > In my case I am looking at retrieving a set of records from a large
> > database
> > > that match a certain criteria... and having the search be from a
single
> > > table... no joins... even though there may be a number of tables in
the
> > > database. Record count would be between 1 million and 10 million.

> > > We are attempting to estimate system capacity... and... obviously...
are
> > new
> > > to all this.

> > > thanks for any help

> > > Will

by **Ivan Arjentinsk** » Sat, 23 Sep 2000 04:00:00

You are really very modest.
Maybe there are no more than 20 people in the world to be so modest,
comparing this with their expertise.

--
Ivan Arjentinski
-----------------------------------------------
Please answer only to the newsgroups.
I'll not answer any direct emails.
-----------------------------------------------

> Will,

> Excellent questions ... but let me ask one in turn ... How do you know
when
> you commit to a marriage that it won't turn out to be "a very expensive
> mistake". It would be nice for there to be guarantees in life ...
> unfortunately, other than death and taxes, I know of very few. Basically,
as
> with most major decisions, it comes down to using your best judgment,
asking
> individuals whose judgment you trust for names of "SQL Server experts"
> (side note ... I have the great good fortune to be a SQL Server MVP ...
> there are exactly 20 of us in the world ... and you know what, I have
> problems calling myself a "SQL Server expert" ... the things I don't know
> about SQL Server far exceed the things I do know about SQL Server ... the
> only thing I can usually claim, is that my level of knowledge about SQL
> Server is usually better than most of the individuals that I encounter in
> this industry.)

> Sorry that I can't give you an answer you wanted.

> ----------------------------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.

> > BP,

> > I would like to find a sql server expert to provide some email
> > coaching/advice... possibly a few phone calls.

> > 1 - How would you suggest I find such an expert? Post a note on this
> > newsgroup?
> > 2 - How can I evaluate their level of knowledge? Choosing the 'kinda
know
> > it' advisor can be a very expensive mistake.

> > I'm posting a separate note about how to design a large database
system -
> > software - hardware - plan for capicity.

> > From what I read todate... everybody just uses gross overkill to make
sure
> > they are covered... not very scientific...

> > Best regards,

> > Will

> > > Will,

> > > Benchmarks is a "loaded" term, as you might know. There are standard
TPC
> > > benchmarks (see www.tpc.org), however from a practical matter these
are
> > > worthless for anything other than bragging rights. If you investigate
> the
> > > equipment used to achieve the benchmarks posted, you'll find that
> > Microsoft
> > > and Oracle and IBM and everyone else spends literally millions of
> dollars
> > on
> > > hardware ... things like systems with 96 cpu's and the such ... just
not
> > > what most businesses can avoid :-)

> > > Additionally, to the best of my knowledge, if you read just about any
> > > license for a RDBMS, it actually prohibits one from publishing
benchmark
> > > results without the explicit approval of the vendor of the RDBMS.

> > > So what are you left with ... well, at least with SQL Server, you can
> get
> > > usually get a free 120-day evaluation copy from the Microsoft Web
site.
> > > Presumably, within the space of 120 days you can get a feel for the
> > > performance that SQL Server can deliver. The one proviso that I will
> offer
> > > is: if you are new to SQL Server (or to Oracle, or to DB2, or to ...)
> hire
> > > someone who is knowledgeable to assist you in setting up the
> environment.
> > > Yeah, I know it can be expensive, but I've seen situations where a
lack
> of
> > > knowledge has unfairly caused one RDBMS to apparently perform
miserably.
> > > BTW, this can be viewed as a justification for why RDBMS vendors
> prohibit
> > > independent benchmarks from being published.

> > > ----------------------------------------------------------------
> > > BP Margolin
> > > Please reply only to the newsgroups.
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> which
> > > can be cut and pasted into Query Analyzer is appreciated.

> > > > Are there some benchmarks that can be used to predict some
> > typical/average
> > > > execution times for a query to execute and return results with sql
> > server
> > > > running on a win2k machine.

> > > > Given a defined machine, with defined resources, with defined
tables,
> > > record
> > > > count, etc.

> > > > In my case I am looking at retrieving a set of records from a large
> > > database
> > > > that match a certain criteria... and having the search be from a
> single
> > > > table... no joins... even though there may be a number of tables in
> the
> > > > database. Record count would be between 1 million and 10 million.

> > > > We are attempting to estimate system capacity... and... obviously...
> are
> > > new
> > > > to all this.

> > > > thanks for any help

> > > > Will

by **BP Margoli** » Sat, 23 Sep 2000 04:00:00

Ivan,

Modesty is not one of virtues ... honesty, I believe, is.

And remember ... I'm the idiot that confused an equi-join with an inner join
:-)

But thanks for your kind words!

BPM

> You are really very modest.
> Maybe there are no more than 20 people in the world to be so modest,
> comparing this with their expertise.

> --
> Ivan Arjentinski
> -----------------------------------------------
> Please answer only to the newsgroups.
> I'll not answer any direct emails.
> -----------------------------------------------

> > Will,

> > Excellent questions ... but let me ask one in turn ... How do you know
> when
> > you commit to a marriage that it won't turn out to be "a very expensive
> > mistake". It would be nice for there to be guarantees in life ...
> > unfortunately, other than death and taxes, I know of very few.
Basically,
> as
> > with most major decisions, it comes down to using your best judgment,
> asking
> > individuals whose judgment you trust for names of "SQL Server experts"
> > (side note ... I have the great good fortune to be a SQL Server MVP ...
> > there are exactly 20 of us in the world ... and you know what, I have
> > problems calling myself a "SQL Server expert" ... the things I don't
know
> > about SQL Server far exceed the things I do know about SQL Server ...
the
> > only thing I can usually claim, is that my level of knowledge about SQL
> > Server is usually better than most of the individuals that I encounter
in
> > this industry.)

> > Sorry that I can't give you an answer you wanted.

> > ----------------------------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.

> > > BP,

> > > I would like to find a sql server expert to provide some email
> > > coaching/advice... possibly a few phone calls.

> > > 1 - How would you suggest I find such an expert? Post a note on this
> > > newsgroup?
> > > 2 - How can I evaluate their level of knowledge? Choosing the 'kinda
> know
> > > it' advisor can be a very expensive mistake.

> > > I'm posting a separate note about how to design a large database
> system -
> > > software - hardware - plan for capicity.

> > > From what I read todate... everybody just uses gross overkill to make
> sure
> > > they are covered... not very scientific...

> > > Best regards,

> > > Will

> > > > Will,

> > > > Benchmarks is a "loaded" term, as you might know. There are standard
> TPC
> > > > benchmarks (see www.tpc.org), however from a practical matter these
> are
> > > > worthless for anything other than bragging rights. If you
investigate
> > the
> > > > equipment used to achieve the benchmarks posted, you'll find that
> > > Microsoft
> > > > and Oracle and IBM and everyone else spends literally millions of
> > dollars
> > > on
> > > > hardware ... things like systems with 96 cpu's and the such ... just
> not
> > > > what most businesses can avoid :-)

> > > > Additionally, to the best of my knowledge, if you read just about
any
> > > > license for a RDBMS, it actually prohibits one from publishing
> benchmark
> > > > results without the explicit approval of the vendor of the RDBMS.

> > > > So what are you left with ... well, at least with SQL Server, you
can
> > get
> > > > usually get a free 120-day evaluation copy from the Microsoft Web
> site.
> > > > Presumably, within the space of 120 days you can get a feel for the
> > > > performance that SQL Server can deliver. The one proviso that I will
> > offer
> > > > is: if you are new to SQL Server (or to Oracle, or to DB2, or to
...)
> > hire
> > > > someone who is knowledgeable to assist you in setting up the
> > environment.
> > > > Yeah, I know it can be expensive, but I've seen situations where a
> lack
> > of
> > > > knowledge has unfairly caused one RDBMS to apparently perform
> miserably.
> > > > BTW, this can be viewed as a justification for why RDBMS vendors
> > prohibit
> > > > independent benchmarks from being published.

> > > > ----------------------------------------------------------------
> > > > BP Margolin
> > > > Please reply only to the newsgroups.
> > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> > which
> > > > can be cut and pasted into Query Analyzer is appreciated.

> > > > > Are there some benchmarks that can be used to predict some
> > > typical/average
> > > > > execution times for a query to execute and return results with sql
> > > server
> > > > > running on a win2k machine.

> > > > > Given a defined machine, with defined resources, with defined
> tables,
> > > > record
> > > > > count, etc.

> > > > > In my case I am looking at retrieving a set of records from a
large
> > > > database
> > > > > that match a certain criteria... and having the search be from a
> > single
> > > > > table... no joins... even though there may be a number of tables
in
> > the
> > > > > database. Record count would be between 1 million and 10 million.

> > > > > We are attempting to estimate system capacity... and...
obviously...
> > are
> > > > new
> > > > > to all this.

> > > > > thanks for any help

> > > > > Will

Very Computer

How2 predict Query Execution Time ?

How2 predict Query Execution Time ?

How2 predict Query Execution Time ?

How2 predict Query Execution Time ?

How2 predict Query Execution Time ?

How2 predict Query Execution Time ?

How2 predict Query Execution Time ?

How2 predict Query Execution Time ?

How2 predict Query Execution Time ?