Maximum Table Size

We have been recommended by our database designers that 20million rows is th
e
maximum number of rows a data warehouse should be on SQLServer.
What is the opinion of you guys on this benchmark? Have you seen tables
bigger than that? Did you notice any performance impact.
Our table is at 14million rows and is about 14gb, we are analysing all
opportunities at present and would like a second opinion please.
Regards,
Marc> What is the opinion of you guys on this benchmark? Have you seen tables
> bigger than that?
YES!

> Did you notice any performance impact.
There are always performance concerns. The guideline should be more focused
on proper indexing and row widths, rather than number of rows.
A|||My recommendation is to sack your database designers and hire some people
who know what they are talking about. Seriously. The 20 million row comment
is pure rubbish.
There are SQL Server databases (not many though, because not many companies
have that much data) that have tables with billions of rows. 20 million rows
should not present any problems at all in a properly designed SQL Server
database, and I have seen numerous tables that contain that number of rows
or more.14 GB in itself is not much for a SQL Server database, but if the
table only contains 14 million rows, that is 1000 bytes per row, a rather
large rowsize for a datawarehouse fact table. A datawarehouse fact table
should almost exclusively contain numeric columns, and these columns should
be designed to be as small as possible, usually they are 4 byte integers. If
that is the case you are talking about 250 columns in that table, which is
possible from a proper logical design point of view, but sounds a bit much
too me. SQL Server can of course easily handle 250 columns in a table, it is
usually an indication of a bad database design though, to have that many
columns in one table.
Jacco Schalkwijk
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:55BE7712-1CAE-4E6A-9EEA-05286669D9D2@.microsoft.com...
> We have been recommended by our database designers that 20million rows is
> the
> maximum number of rows a data warehouse should be on SQLServer.
> What is the opinion of you guys on this benchmark? Have you seen tables
> bigger than that? Did you notice any performance impact.
> Our table is at 14million rows and is about 14gb, we are analysing all
> opportunities at present and would like a second opinion please.
> Regards,
> Marc|||Depends on a lot of things
for example the wider the tables the slower the selects (less rows on a page
)
CPU, Disk etc etc etc
http://sqlservercode.blogspot.com/
"marcmc" wrote:

> We have been recommended by our database designers that 20million rows is
the
> maximum number of rows a data warehouse should be on SQLServer.
> What is the opinion of you guys on this benchmark? Have you seen tables
> bigger than that? Did you notice any performance impact.
> Our table is at 14million rows and is about 14gb, we are analysing all
> opportunities at present and would like a second opinion please.
> Regards,
> Marc|||> We have been recommended by our database designers that 20million rows is theed">
> maximum number of rows a data warehouse should be on SQLServer.
If your database designers say this then what do they recommend you do about
it? Sounds like either a sales pitch or a lame excuse for poor performance.
There is no fixed limit on the number of rows in a SQL Server table. Even if
there were such a limitation it would be irrelevant in a DW scenario because
you can implement a partitioned view across many tables on many different
devices.
14GB is a fairly modest sized data warehouse. DW on the terabyte scale is
pretty normal in SQL Server today.
David Portas
SQL Server MVP
--|||thx jacco,
we have a table 95 columns wide with 3228 characters.
the table is actually 18million rows(my mistake), we have had some
performance issues with it such as when linked to other large
tables(4million+ records).
Do you think the number of records or the row size is more important
Most of our columns are integers [ID's] but we do have some with
smalldatetime and one with a 19 char length!
we have people coming to talk teradata/oracle etc etc but no one has yet
identified the database design flaws yet. How can we look at this in more
detail especially in respect to the 20million maximum rowcount table size!
Appreciate your input
Marc|||As David emphasised, there is no limit to the number of rows in a table in
SQL Server. That the (flawed) design and lack of performance tuning skills
of your database designers doesn't allow for a reasonable performance with
20 millions row on your system, is their fault and not SQL Server's.
It's almost impossible to give proper advice in a newsgroup post on database
design flaws and performance issues in what seems a reasonably large system.
The best thing you can do is get an independent consultant in for a few days
to check the system. It will cost some money upfront, but will save you
loads in the long run. A number of MVPs work as independent consultants, and
I can forward your details to them if you contact me offline.
Jacco Schalkwijk
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:40A756CA-0E23-4F44-8EE0-D742C1EF9C99@.microsoft.com...
> thx jacco,
> we have a table 95 columns wide with 3228 characters.
> the table is actually 18million rows(my mistake), we have had some
> performance issues with it such as when linked to other large
> tables(4million+ records).
> Do you think the number of records or the row size is more important
> Most of our columns are integers [ID's] but we do have some with
> smalldatetime and one with a 19 char length!
> we have people coming to talk teradata/oracle etc etc but no one has yet
> identified the database design flaws yet. How can we look at this in more
> detail especially in respect to the 20million maximum rowcount table size!
> Appreciate your input
> Marc
>|||> Do you think the number of records or the row size is more important
Neither. Optimal design and implementation are immeasurably more important.
Lousy design can destroy performance with only a few thousand rows. Since yo
u
(or your namesake) just stated in another thread that you "always use
cursors" you may not need to look any further than that for an explanation o
f
why you can't scale.
David Portas
SQL Server MVP
--
"marcmc" wrote:

> thx jacco,
> we have a table 95 columns wide with 3228 characters.
> the table is actually 18million rows(my mistake), we have had some
> performance issues with it such as when linked to other large
> tables(4million+ records).
> Do you think the number of records or the row size is more important
> Most of our columns are integers [ID's] but we do have some with
> smalldatetime and one with a 19 char length!
> we have people coming to talk teradata/oracle etc etc but no one has yet
> identified the database design flaws yet. How can we look at this in more
> detail especially in respect to the 20million maximum rowcount table size!
> Appreciate your input
> Marc
>|||To add to what everyone else says, after design (and a 95 column wide table
may or may not be a design issue, depending on if this is a fact table or
not (if so, then 80+ dimensions may be an issue, but I degress) the hardware
is the key. Too often people who claim some fixed number as a maximum don't
think of a Windows server as scalable. A lot will depend on your disk
subsystem for example. You might be doing your work on IDE drives, or a
slow Raid-5 array, or one of unlimited possibilites. When you start to
approact a large size/many users, the cost does go up greatly, but it is
likely not SQL Server's fault (as these vendors may tell you it is, since
they have a vested interest in you going to Oracle on their hardware)
As Jacco says in particular, you need someone to look at all of these
factors independent of a hardward/software vendor (ie they aren't
salespeople!) to get a valid look at what is going on.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:40A756CA-0E23-4F44-8EE0-D742C1EF9C99@.microsoft.com...
> thx jacco,
> we have a table 95 columns wide with 3228 characters.
> the table is actually 18million rows(my mistake), we have had some
> performance issues with it such as when linked to other large
> tables(4million+ records).
> Do you think the number of records or the row size is more important
> Most of our columns are integers [ID's] but we do have some with
> smalldatetime and one with a 19 char length!
> we have people coming to talk teradata/oracle etc etc but no one has yet
> identified the database design flaws yet. How can we look at this in more
> detail especially in respect to the 20million maximum rowcount table size!
> Appreciate your input
> Marc
>