database size vs. its performance

[aleu]

Hi all,

Could you please advise whether there are documents describing impact of
MS SQL server 2005 database size on its performance? I have essentially
two things in mind when writing the "database size":
1.) number of records stored within the database (e.g. one customer's
account with 5,000 records vs. another account with 500,000 records.)

2.) physical database size (amount of data written to a disk volume) -
e.g. one database of 10GB versus another of 1TB. My guess is that the
10GB database will outperform the large one, but I was wondering whether
there are methods to measure / quantify this.

Please let me know what are some good articles to read on this topic.

Thank you.

Regards,
AL

[Tom van Stiphout]

On Tue, 18 Nov 2008 23:03:57 -0500, "al**@vp.pl" <al**@vp.plwrote:

Re 1: assuming the Account column is indexed, it's simply a matter of
how many reads are needed in a binary tree to find the record in
question. If N is the number of rows in a table, the number of reads
is log(N)/log(2). So for 1,024 rows it is 10, for 1,048,576 rows it is
20, etc. That's the power of indexing.
Without an index it would be N/2, so for 1,048,576 rows it would take
a long time.

I think physical size has little to do with it.

-Tom.
Microsoft Access MVP

>Hi all,

Could you please advise whether there are documents describing impact of
MS SQL server 2005 database size on its performance? I have essentially
two things in mind when writing the "database size":
1.) number of records stored within the database (e.g. one customer's
account with 5,000 records vs. another account with 500,000 records.)

2.) physical database size (amount of data written to a disk volume) -
e.g. one database of 10GB versus another of 1TB. My guess is that the
10GB database will outperform the large one, but I was wondering whether
there are methods to measure / quantify this.

Please let me know what are some good articles to read on this topic.

Thank you.

Regards,
AL

[Erland Sommarskog]

al**@vp.pl (al**@vp.pl) writes:

Could you please advise whether there are documents describing impact of
MS SQL server 2005 database size on its performance? I have essentially
two things in mind when writing the "database size":
1.) number of records stored within the database (e.g. one customer's
account with 5,000 records vs. another account with 500,000 records.)

2.) physical database size (amount of data written to a disk volume) -
e.g. one database of 10GB versus another of 1TB. My guess is that the
10GB database will outperform the large one, but I was wondering whether
there are methods to measure / quantify this.

There is one standard reply to almost all performance questions: "It
depends".

You could very well have a lot better performance on a 1TB database than
with a 10 GB database. The big database could be well designed, be properly
indexed, be regularly defragmented etc. Furthermore, the access pattern
could be such that getting good performance is a relatively simple task.
On the other hand, the 10 GB database could be very poorly designed, have
no good indexes at all. Or the application using that database could simply
be more challenging in how it accesses the data.

What we can say, is that if the 10 GB database with poor performance would
grow to 1 TB, things will get a lot worse. If there are a lot of table
scans, response time will 100-fold.

If the properly designed 1TB would be distilled to a 10GB, response time
would possibly decrease somewhat, but not with a factor of 100.

There is one aspect, though, where the pure size matter, and that is with
maintenance. Backing up a 1TB database will take 100 times longer than
backing up a 10GB with the same hardware and backup procedures. The same
applies to defragmentation jobs.

What one can say is that the more your database grows, the more care you
need to take. Both in the database design, because flaws that may go
unnoticed in a small database, will kill you when the data size grows.
And the management of a 1TB database certainly is a challenge.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[DA Morgan]

Tom van Stiphout wrote:

On Tue, 18 Nov 2008 23:03:57 -0500, "al**@vp.pl" <al**@vp.plwrote:

Re 1: assuming the Account column is indexed, it's simply a matter of
how many reads are needed in a binary tree to find the record in
question. If N is the number of rows in a table, the number of reads
is log(N)/log(2). So for 1,024 rows it is 10, for 1,048,576 rows it is
20, etc. That's the power of indexing.
Without an index it would be N/2, so for 1,048,576 rows it would take
a long time.

I think physical size has little to do with it.

-Tom.
Microsoft Access MVP

Erland's answer is far closer to correct than what you have posted
which is clearly incorrect. The questions asked by the OP are
substantially more complex than just a question of size. Reading
rows from disk is one issue among many. Add to this the number of rows
that mush be hashed, joined, filtered, possibly swapped to disk due to
aggregation and ordering.

Physical size has a lot to do with it.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu (replace x with u to respond)

[Tom van Stiphout]

On Wed, 19 Nov 2008 09:05:58 -0800, DA Morgan <da******@psoug.org>
wrote:

The OP gave an example what he was thinking of: "...one customer's
account with 5,000 records vs. another account with 500,000
records...", which is what I latched on to.
With physical size I took him to mean such a table with few columns,
or with many columns, or alternatively a database that otherwise has a
bunch of other tables unrelated to our query of the Customer Accounts.
I believe in that context (which I should have clarified) my answer
was in the ballpark.

I have nothing but respect for Erland. He read the question
differently than I did.

-Tom.

>Tom van Stiphout wrote:

>On Tue, 18 Nov 2008 23:03:57 -0500, "al**@vp.pl" <al**@vp.plwrote:

Re 1: assuming the Account column is indexed, it's simply a matter of
how many reads are needed in a binary tree to find the record in
question. If N is the number of rows in a table, the number of reads
is log(N)/log(2). So for 1,024 rows it is 10, for 1,048,576 rows it is
20, etc. That's the power of indexing.
Without an index it would be N/2, so for 1,048,576 rows it would take
a long time.

I think physical size has little to do with it.

-Tom.
Microsoft Access MVP

Erland's answer is far closer to correct than what you have posted
which is clearly incorrect. The questions asked by the OP are
substantially more complex than just a question of size. Reading
rows from disk is one issue among many. Add to this the number of rows
that mush be hashed, joined, filtered, possibly swapped to disk due to
aggregation and ordering.

Physical size has a lot to do with it.

[steve11228]

On Nov 19, 9:31*pm, Tom van Stiphout <[e-mail]:

On Wed, 19 Nov 2008 09:05:58 -0800, DA Morgan <[e-mail]>
wrote:

The OP gave an example what he was thinking of: "...one customer's
account with 5,000 records vs. another account with 500,000
records...", which is what I latched on to.
With physical size I took him to mean such a table with few columns,
or with many columns, or alternatively a database that otherwise has a
bunch of other tables unrelated to our query of the Customer Accounts.
I believe in that context (which I should have clarified) my answer
was in the ballpark.

I have nothing but respect for Erland. He read the question
differently than I did.

I vaguely remember someone with your name from the days I used to post
among the Access cry babies (a long time ago) :) They didn't
understand me then and they sure as hell aren't going to understand me
know:) Oh well, carry-on :) Hope things are well with ya.

best,
steve dassin
www.beyondsql.blogspot.com

-Tom.

Tom van Stiphout wrote:

On Tue, 18 Nov 2008 23:03:57 -0500, "[e-mail]" <[e-mail]:

Re 1: assuming the Account column is indexed, it's simply a matter of
how many reads are needed in a binary tree to find the record in
question. If N is the number of rows in a table, the number of reads
is log(N)/log(2). So for 1,024 rows it is 10, for 1,048,576 rows it is
20, etc. That's the power of indexing.
Without an index it would be N/2, so for 1,048,576 rows it would take
a long time.

I think physical size has little to do with it.

-Tom.
Microsoft Access MVP

Erland's answer is far closer to correct than what you have posted
which is clearly incorrect. The questions asked by the OP are
substantially more complex than just a question of size. Reading
rows from disk is one issue among many. Add to this the number of rows
that mush be hashed, joined, filtered, possibly swapped to disk due to
aggregation and ordering.

Physical size has a lot to do with it.