Hi all,
About to start a project to upgrade to MDAC 2.8 from MDAC 2.6 on a number of servers. Has anyone encountered any issues with this? Also, if we do NOT upgrade several client workstations, might we expect problems as a result?
We are aware that we need to patch MDAC for the vulnerability issues, but we are trying to comine this with a server upgrade for servers at risk. Has anyone had experience on a similar project?
Cheers,
DunNot so far, but you should be better off if you can carry the implementation on test environment.
Take a note from this KBA (http://support.microsoft.com/?kbid=828396) about MDAC 2.8.
HTH
Showing posts with label number. Show all posts
Showing posts with label number. Show all posts
Wednesday, March 28, 2012
Monday, March 19, 2012
Maximum Transactions
Is there a maximum number of transactions that SQL 2000 Standard can
handle at any given time?
Is this configurable?Standard answer: It Depends
It depends upon Hardware (CPU, memory), network traffic, size of the
transactions, database traffic, query plan, what color shoes you wear, etc.
Obviously a 8 CPU/64Gb/3.4Mhz server can handle quite a few more
transactions per second than a 1 CPU/1GB 1.7mhz server.
Now go take a 'common sense' pill. ;-)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"John Bailo" <jabailo@.texeme.com> wrote in message
news:_NmdnSjCQP6-XlrZnZ2dnUVZ_oidnZ2d@.speakeasy.net...
>
> Is there a maximum number of transactions that SQL 2000 Standard can
> handle at any given time?
> Is this configurable?
>|||Arnie Rowland wrote:
> Standard answer: It Depends
> It depends upon Hardware (CPU, memory), network traffic, size of the
> transactions, database traffic, query plan, what color shoes you wear, etc.
> Obviously a 8 CPU/64Gb/3.4Mhz server can handle quite a few more
> transactions per second than a 1 CPU/1GB 1.7mhz server.
> Now go take a 'common sense' pill. ;-)
>
Is there a way to see the current queued transactions?
And estimate room in the queue space for a given server?|||"John Bailo" <jabailo@.texeme.com> wrote in message
news:y-WdnckMabPZaVrZnZ2dnUVZ_uidnZ2d@.speakeasy.net...
> Arnie Rowland wrote:
> > Standard answer: It Depends
> >
> > It depends upon Hardware (CPU, memory), network traffic, size of the
> > transactions, database traffic, query plan, what color shoes you wear,
etc.
> >
> > Obviously a 8 CPU/64Gb/3.4Mhz server can handle quite a few more
> > transactions per second than a 1 CPU/1GB 1.7mhz server.
> >
> > Now go take a 'common sense' pill. ;-)
> >
> Is there a way to see the current queued transactions?
sp_who active will show you active transactions and any blocked ones.
> And estimate room in the queue space for a given server?
Again, not nearly enough info to go on. What are the size of your
transactions.
Lots of small ones vs lots of large ones will make a big difference.
handle at any given time?
Is this configurable?Standard answer: It Depends
It depends upon Hardware (CPU, memory), network traffic, size of the
transactions, database traffic, query plan, what color shoes you wear, etc.
Obviously a 8 CPU/64Gb/3.4Mhz server can handle quite a few more
transactions per second than a 1 CPU/1GB 1.7mhz server.
Now go take a 'common sense' pill. ;-)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"John Bailo" <jabailo@.texeme.com> wrote in message
news:_NmdnSjCQP6-XlrZnZ2dnUVZ_oidnZ2d@.speakeasy.net...
>
> Is there a maximum number of transactions that SQL 2000 Standard can
> handle at any given time?
> Is this configurable?
>|||Arnie Rowland wrote:
> Standard answer: It Depends
> It depends upon Hardware (CPU, memory), network traffic, size of the
> transactions, database traffic, query plan, what color shoes you wear, etc.
> Obviously a 8 CPU/64Gb/3.4Mhz server can handle quite a few more
> transactions per second than a 1 CPU/1GB 1.7mhz server.
> Now go take a 'common sense' pill. ;-)
>
Is there a way to see the current queued transactions?
And estimate room in the queue space for a given server?|||"John Bailo" <jabailo@.texeme.com> wrote in message
news:y-WdnckMabPZaVrZnZ2dnUVZ_uidnZ2d@.speakeasy.net...
> Arnie Rowland wrote:
> > Standard answer: It Depends
> >
> > It depends upon Hardware (CPU, memory), network traffic, size of the
> > transactions, database traffic, query plan, what color shoes you wear,
etc.
> >
> > Obviously a 8 CPU/64Gb/3.4Mhz server can handle quite a few more
> > transactions per second than a 1 CPU/1GB 1.7mhz server.
> >
> > Now go take a 'common sense' pill. ;-)
> >
> Is there a way to see the current queued transactions?
sp_who active will show you active transactions and any blocked ones.
> And estimate room in the queue space for a given server?
Again, not nearly enough info to go on. What are the size of your
transactions.
Lots of small ones vs lots of large ones will make a big difference.
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
>
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
>
maximum row size.. 8060 bytes ?
I got a warning like this.
Warning: The table 'tbSOTransHeader' has been created but
its maximum row size (9911) exceeds the maximum number of
bytes per row (8060). INSERT or UPDATE of a row in this
table will fail if the resulting row length exceeds 8060
bytes.
My questions are :
1. Is it true max row size is 8060 bytes ?
2. Can we make it larger ?
Thanks.Yes the max amount of actual data in the row can only be 8060. You can
create a table, view etc larger than that with variable length columns as
long as when populated they don't total more than that. NO you can't make
it larger.
--
Andrew J. Kelly
SQL Server MVP
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:082a01c3728f$48917d50$a301280a@.phx.gbl...
> I got a warning like this.
> Warning: The table 'tbSOTransHeader' has been created but
> its maximum row size (9911) exceeds the maximum number of
> bytes per row (8060). INSERT or UPDATE of a row in this
> table will fail if the resulting row length exceeds 8060
> bytes.
> My questions are :
> 1. Is it true max row size is 8060 bytes ?
> 2. Can we make it larger ?
> Thanks.
>|||Is there a function or stored procedure in T-sql to
calculate bytes in a row ?
thx.
>--Original Message--
>Yes the max amount of actual data in the row can only be
8060. You can
>create a table, view etc larger than that with variable
length columns as
>long as when populated they don't total more than that.
NO you can't make
>it larger.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
message
>news:082a01c3728f$48917d50$a301280a@.phx.gbl...
>> I got a warning like this.
>> Warning: The table 'tbSOTransHeader' has been created
but
>> its maximum row size (9911) exceeds the maximum number
of
>> bytes per row (8060). INSERT or UPDATE of a row in this
>> table will fail if the resulting row length exceeds 8060
>> bytes.
>> My questions are :
>> 1. Is it true max row size is 8060 bytes ?
>> 2. Can we make it larger ?
>> Thanks.
>>
>
>.
>|||use datalength.
ex: select datalength(col1) + datalength(col2) ... from <table>
--
-Vishal
kresna rudy kurniawan <kresnark@.yahoo.com> wrote in message
news:163c01c37293$55393f80$a601280a@.phx.gbl...
> Is there a function or stored procedure in T-sql to
> calculate bytes in a row ?
> thx.
>
> >--Original Message--
> >Yes the max amount of actual data in the row can only be
> 8060. You can
> >create a table, view etc larger than that with variable
> length columns as
> >long as when populated they don't total more than that.
> NO you can't make
> >it larger.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> message
> >news:082a01c3728f$48917d50$a301280a@.phx.gbl...
> >> I got a warning like this.
> >>
> >> Warning: The table 'tbSOTransHeader' has been created
> but
> >> its maximum row size (9911) exceeds the maximum number
> of
> >> bytes per row (8060). INSERT or UPDATE of a row in this
> >> table will fail if the resulting row length exceeds 8060
> >> bytes.
> >>
> >> My questions are :
> >>
> >> 1. Is it true max row size is 8060 bytes ?
> >> 2. Can we make it larger ?
> >>
> >> Thanks.
> >>
> >>
> >
> >
> >.
> >|||kresna rudy kurniawan wrote:
> I got a warning like this.
> Warning: The table 'tbSOTransHeader' has been created but
> its maximum row size (9911) exceeds the maximum number of
> bytes per row (8060). INSERT or UPDATE of a row in this
> table will fail if the resulting row length exceeds 8060
> bytes.
> My questions are :
> 1. Is it true max row size is 8060 bytes ?
> 2. Can we make it larger ?
> Thanks.
All columns of the row have to fit in the page (maximum 8060 bytes),
except for data types text, ntext and image. For those data types, only
16 bytes (for each text/ntext/image column) are needed in the page that
stores the row.
So if the current data does not fit the page, it might be an option to
change large char, nchar, varchar or nvarchar columns to text or ntext.
Hope this helps,
Gert-Jan
Warning: The table 'tbSOTransHeader' has been created but
its maximum row size (9911) exceeds the maximum number of
bytes per row (8060). INSERT or UPDATE of a row in this
table will fail if the resulting row length exceeds 8060
bytes.
My questions are :
1. Is it true max row size is 8060 bytes ?
2. Can we make it larger ?
Thanks.Yes the max amount of actual data in the row can only be 8060. You can
create a table, view etc larger than that with variable length columns as
long as when populated they don't total more than that. NO you can't make
it larger.
--
Andrew J. Kelly
SQL Server MVP
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:082a01c3728f$48917d50$a301280a@.phx.gbl...
> I got a warning like this.
> Warning: The table 'tbSOTransHeader' has been created but
> its maximum row size (9911) exceeds the maximum number of
> bytes per row (8060). INSERT or UPDATE of a row in this
> table will fail if the resulting row length exceeds 8060
> bytes.
> My questions are :
> 1. Is it true max row size is 8060 bytes ?
> 2. Can we make it larger ?
> Thanks.
>|||Is there a function or stored procedure in T-sql to
calculate bytes in a row ?
thx.
>--Original Message--
>Yes the max amount of actual data in the row can only be
8060. You can
>create a table, view etc larger than that with variable
length columns as
>long as when populated they don't total more than that.
NO you can't make
>it larger.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
message
>news:082a01c3728f$48917d50$a301280a@.phx.gbl...
>> I got a warning like this.
>> Warning: The table 'tbSOTransHeader' has been created
but
>> its maximum row size (9911) exceeds the maximum number
of
>> bytes per row (8060). INSERT or UPDATE of a row in this
>> table will fail if the resulting row length exceeds 8060
>> bytes.
>> My questions are :
>> 1. Is it true max row size is 8060 bytes ?
>> 2. Can we make it larger ?
>> Thanks.
>>
>
>.
>|||use datalength.
ex: select datalength(col1) + datalength(col2) ... from <table>
--
-Vishal
kresna rudy kurniawan <kresnark@.yahoo.com> wrote in message
news:163c01c37293$55393f80$a601280a@.phx.gbl...
> Is there a function or stored procedure in T-sql to
> calculate bytes in a row ?
> thx.
>
> >--Original Message--
> >Yes the max amount of actual data in the row can only be
> 8060. You can
> >create a table, view etc larger than that with variable
> length columns as
> >long as when populated they don't total more than that.
> NO you can't make
> >it larger.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> message
> >news:082a01c3728f$48917d50$a301280a@.phx.gbl...
> >> I got a warning like this.
> >>
> >> Warning: The table 'tbSOTransHeader' has been created
> but
> >> its maximum row size (9911) exceeds the maximum number
> of
> >> bytes per row (8060). INSERT or UPDATE of a row in this
> >> table will fail if the resulting row length exceeds 8060
> >> bytes.
> >>
> >> My questions are :
> >>
> >> 1. Is it true max row size is 8060 bytes ?
> >> 2. Can we make it larger ?
> >>
> >> Thanks.
> >>
> >>
> >
> >
> >.
> >|||kresna rudy kurniawan wrote:
> I got a warning like this.
> Warning: The table 'tbSOTransHeader' has been created but
> its maximum row size (9911) exceeds the maximum number of
> bytes per row (8060). INSERT or UPDATE of a row in this
> table will fail if the resulting row length exceeds 8060
> bytes.
> My questions are :
> 1. Is it true max row size is 8060 bytes ?
> 2. Can we make it larger ?
> Thanks.
All columns of the row have to fit in the page (maximum 8060 bytes),
except for data types text, ntext and image. For those data types, only
16 bytes (for each text/ntext/image column) are needed in the page that
stores the row.
So if the current data does not fit the page, it might be an option to
change large char, nchar, varchar or nvarchar columns to text or ntext.
Hope this helps,
Gert-Jan
maximum row size exceeds the maximum number of bytes per row (8060
Hi,
I have created a table (say Table1) with few columns, with one of the
columns (say column1) having the data type as Varchar(8000).
Now I run the below query -
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
if not exists (select * from dbo.syscolumns
where id = object_id(N'[dbo].[Table1]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1
and name = 'Column1')
ALTER TABLE dbo.Table1 ADD
Column1 varchar(500) NULL
GO
COMMIT
Now that the column Column1 already exists, the add column statement won't
be executed. But still I get the warning -
"Warning: The table 'Table1' has been created but its maximum row size
(8579) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes."
My question is, now that the column already exists, the statement itself
won't be executed. Then why do I still get this warning?That has to do with the sequence of query processing and when the warning is generated. Obviously
the warning (the fact that this table will have a row size for which you can exceed the limit in
your data) is generated in a stage which is earlier than when the statements are actually executed.
In other words, the If statement haven't been executed at this stage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"swap@.@.@." <swap@.discussions.microsoft.com> wrote in message
news:73AC8FFC-4041-4B22-AB25-4CCDB94598FC@.microsoft.com...
> Hi,
> I have created a table (say Table1) with few columns, with one of the
> columns (say column1) having the data type as Varchar(8000).
> Now I run the below query -
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> if not exists (select * from dbo.syscolumns
> where id = object_id(N'[dbo].[Table1]')
> and OBJECTPROPERTY(id, N'IsUserTable') = 1
> and name = 'Column1')
> ALTER TABLE dbo.Table1 ADD
> Column1 varchar(500) NULL
> GO
> COMMIT
> Now that the column Column1 already exists, the add column statement won't
> be executed. But still I get the warning -
> "Warning: The table 'Table1' has been created but its maximum row size
> (8579) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes."
> My question is, now that the column already exists, the statement itself
> won't be executed. Then why do I still get this warning?
>
I have created a table (say Table1) with few columns, with one of the
columns (say column1) having the data type as Varchar(8000).
Now I run the below query -
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
if not exists (select * from dbo.syscolumns
where id = object_id(N'[dbo].[Table1]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1
and name = 'Column1')
ALTER TABLE dbo.Table1 ADD
Column1 varchar(500) NULL
GO
COMMIT
Now that the column Column1 already exists, the add column statement won't
be executed. But still I get the warning -
"Warning: The table 'Table1' has been created but its maximum row size
(8579) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes."
My question is, now that the column already exists, the statement itself
won't be executed. Then why do I still get this warning?That has to do with the sequence of query processing and when the warning is generated. Obviously
the warning (the fact that this table will have a row size for which you can exceed the limit in
your data) is generated in a stage which is earlier than when the statements are actually executed.
In other words, the If statement haven't been executed at this stage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"swap@.@.@." <swap@.discussions.microsoft.com> wrote in message
news:73AC8FFC-4041-4B22-AB25-4CCDB94598FC@.microsoft.com...
> Hi,
> I have created a table (say Table1) with few columns, with one of the
> columns (say column1) having the data type as Varchar(8000).
> Now I run the below query -
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> if not exists (select * from dbo.syscolumns
> where id = object_id(N'[dbo].[Table1]')
> and OBJECTPROPERTY(id, N'IsUserTable') = 1
> and name = 'Column1')
> ALTER TABLE dbo.Table1 ADD
> Column1 varchar(500) NULL
> GO
> COMMIT
> Now that the column Column1 already exists, the add column statement won't
> be executed. But still I get the warning -
> "Warning: The table 'Table1' has been created but its maximum row size
> (8579) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes."
> My question is, now that the column already exists, the statement itself
> won't be executed. Then why do I still get this warning?
>
Maximum Recommended Databases
I've read MSDN that the theoretical maximum number of databases per
SQL server instance is 32,767. But I'd like to know if there is a
real world recommendation from Microsoft. I know this sounds like a
ridiculous question, but I've inherited an instance with approximately
1,000 databases (and growing) and I'm trying to determine when I
should expect major performance issues (don't laugh). I have a plan
for reducing the number of databases, but it won't happen over night.
I know a lot of this depends on database usage, but I'm just looking
for any information I can find. Thanks in advance!
I have a client with over 5000 databases and still growing. The usage
pattern is such that only a few dozen or even a few hundred are heavily used
at any one time. The system is performing quite well.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegr oups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>
|||I don't think I'm going to provide any new information that you don't
already have. But this is kind of like asking how big can a database be, or
how many rows can I get into a table? As the number of databases goes up,
the usage pattern really determines how detrimental each additional database
is to performance (let's leave administration costs out of this for now).
So many factors are involved that it's impossible for anyone to come up with
a real number that would apply to your situation and my situation
identically, even if you could feasibly perform all the real-world tests
required to come up with such a number...
When should you expect performance issues? When your usage patterns exceed
your hardware's capabilities. It is absolutely impossible to tell you that
it will happen when you add 5 or 500 more databases. In fact, it could
conceivably happen after you /*remove*/ databases ... if users of the
remaining databases coincidentally change their usage patterns.
I have relatively high-volume systems with over 500 databases, and they work
very well. Would I want to throw another 500 databases on there? Not in
one shot, I don't think. But it's not the number itself that scares me.
It's the fact that 10 or 15 of those (given my current scenario) would
likely end up high transaction hitters that *could* start to destabilize the
system. On the other hand, if they're all duds, then 500 could be added
easily without any impact to the system.
Aaron Bertrand
SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegr oups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>
|||On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
wrote:
>I've read MSDN that the theoretical maximum number of databases per
>SQL server instance is 32,767. But I'd like to know if there is a
>real world recommendation from Microsoft. I know this sounds like a
>ridiculous question, but I've inherited an instance with approximately
>1,000 databases (and growing) and I'm trying to determine when I
>should expect major performance issues (don't laugh).
The critical problem would probably be that they will never share
pages in a working set, so caching will be less effective. But that's
if they're all active at once, which is unlikely ... isn't it?
A few more open file handles at the OS level, ... nah. Whatever
insight you can get on data locality and access patterns is probably
best, but hey, that's true even if it's all a single database, too!
Josh
|||> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective.
Hmm, can you elaborate on this? When you have several databases, the data for each customer (or
whatever differentiate these databases) obviously need to be in separate tables, per customer. So,
data for one customer is on a separate set of pages compared to some other customer meaning data
retrieved for one customer cannot be used when querying from some other customer. But the same thing
would happen if the same set of table were in one database (except for very small tables where mixed
extents come into play). But if this is re-modeled and all customers share the same table, we have a
different situation...
> A few more open file handles at the OS level
Interesting you mention this, since this can impact caching. With many databases, setting auto close
might be needed (especially in 32 bit due to OS limitation and open file handles). And when a
database is auto closed, data in cache is removed (I know for sure this applies to plans, I'd have
to do a test to verify it applies to data).
Perhaps plan caching is a bigger issue? Assuming best practices are followed and stored procedures
are used. Now, we would end up with duplicate procedures, one set for each database. Even if we
don't use stored procedures, we won't be able to re-use cached plans across databases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:799fb3l6bsc2its7r7mfma3kjqr65gekh1@.4ax.com...
> On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
> wrote:
>
> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective. But that's
> if they're all active at once, which is unlikely ... isn't it?
> A few more open file handles at the OS level, ... nah. Whatever
> insight you can get on data locality and access patterns is probably
> best, but hey, that's true even if it's all a single database, too!
> Josh
>
|||I have a client with 6500+ databases. Biggest issues are rolling out
changes, size of msdb database (make it BIG to begin with to avoid
fragmentation from autogrowths), and most 3rd party tools break down when
you try to use them against the server due to massive amounts of system data
they typically try to read. I had a developer build a multi-threaded
"database shotgun" for doing maintenance stuff. HUGE help!
TheSQLGuru
President
Indicium Resources, Inc.
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegr oups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>
|||> they typically try to read. I had a developer build a multi-threaded
> "database shotgun" for doing maintenance stuff. HUGE help!
I use SQL Farm Combine for this.
http://www.sqlfarms.com/
|||On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Hmm, can you elaborate on this?
Just speculating wildly.
If they are a single database, there is at least a *chance* customers,
users, sessions, etc will share pages!
>Perhaps plan caching is a bigger issue?
Ugh, you're right!
But then, wouldn't this be quickly obvious to anyone running
hundreds++ of databases? And the initial comments mostly seem to be
that it runs pretty well.
Josh
|||> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
Agreed. To give a more precise answer, we would have to know the data model after coalescing into
one database.
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
True. But "run pretty well" doesn't mean that it cannot run better... :-)
Of course, this as well depends on the app. If stored procedures are used, then I expect to se some
benefit of having, for a particular proc, only one plan in cache instead of one per database. If
procedures are not used, then chances are that we mostly have exact text matching of the query
string in cache, and re-use would be minimal even if all is in the same database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:9acib3h5ct5mkgtoectjg2s9aqe8k5633k@.4ax.com...
> On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Just speculating wildly.
> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
>
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
> Josh
>
>
SQL server instance is 32,767. But I'd like to know if there is a
real world recommendation from Microsoft. I know this sounds like a
ridiculous question, but I've inherited an instance with approximately
1,000 databases (and growing) and I'm trying to determine when I
should expect major performance issues (don't laugh). I have a plan
for reducing the number of databases, but it won't happen over night.
I know a lot of this depends on database usage, but I'm just looking
for any information I can find. Thanks in advance!
I have a client with over 5000 databases and still growing. The usage
pattern is such that only a few dozen or even a few hundred are heavily used
at any one time. The system is performing quite well.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegr oups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>
|||I don't think I'm going to provide any new information that you don't
already have. But this is kind of like asking how big can a database be, or
how many rows can I get into a table? As the number of databases goes up,
the usage pattern really determines how detrimental each additional database
is to performance (let's leave administration costs out of this for now).
So many factors are involved that it's impossible for anyone to come up with
a real number that would apply to your situation and my situation
identically, even if you could feasibly perform all the real-world tests
required to come up with such a number...
When should you expect performance issues? When your usage patterns exceed
your hardware's capabilities. It is absolutely impossible to tell you that
it will happen when you add 5 or 500 more databases. In fact, it could
conceivably happen after you /*remove*/ databases ... if users of the
remaining databases coincidentally change their usage patterns.
I have relatively high-volume systems with over 500 databases, and they work
very well. Would I want to throw another 500 databases on there? Not in
one shot, I don't think. But it's not the number itself that scares me.
It's the fact that 10 or 15 of those (given my current scenario) would
likely end up high transaction hitters that *could* start to destabilize the
system. On the other hand, if they're all duds, then 500 could be added
easily without any impact to the system.
Aaron Bertrand
SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegr oups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>
|||On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
wrote:
>I've read MSDN that the theoretical maximum number of databases per
>SQL server instance is 32,767. But I'd like to know if there is a
>real world recommendation from Microsoft. I know this sounds like a
>ridiculous question, but I've inherited an instance with approximately
>1,000 databases (and growing) and I'm trying to determine when I
>should expect major performance issues (don't laugh).
The critical problem would probably be that they will never share
pages in a working set, so caching will be less effective. But that's
if they're all active at once, which is unlikely ... isn't it?
A few more open file handles at the OS level, ... nah. Whatever
insight you can get on data locality and access patterns is probably
best, but hey, that's true even if it's all a single database, too!
Josh
|||> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective.
Hmm, can you elaborate on this? When you have several databases, the data for each customer (or
whatever differentiate these databases) obviously need to be in separate tables, per customer. So,
data for one customer is on a separate set of pages compared to some other customer meaning data
retrieved for one customer cannot be used when querying from some other customer. But the same thing
would happen if the same set of table were in one database (except for very small tables where mixed
extents come into play). But if this is re-modeled and all customers share the same table, we have a
different situation...
> A few more open file handles at the OS level
Interesting you mention this, since this can impact caching. With many databases, setting auto close
might be needed (especially in 32 bit due to OS limitation and open file handles). And when a
database is auto closed, data in cache is removed (I know for sure this applies to plans, I'd have
to do a test to verify it applies to data).
Perhaps plan caching is a bigger issue? Assuming best practices are followed and stored procedures
are used. Now, we would end up with duplicate procedures, one set for each database. Even if we
don't use stored procedures, we won't be able to re-use cached plans across databases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:799fb3l6bsc2its7r7mfma3kjqr65gekh1@.4ax.com...
> On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
> wrote:
>
> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective. But that's
> if they're all active at once, which is unlikely ... isn't it?
> A few more open file handles at the OS level, ... nah. Whatever
> insight you can get on data locality and access patterns is probably
> best, but hey, that's true even if it's all a single database, too!
> Josh
>
|||I have a client with 6500+ databases. Biggest issues are rolling out
changes, size of msdb database (make it BIG to begin with to avoid
fragmentation from autogrowths), and most 3rd party tools break down when
you try to use them against the server due to massive amounts of system data
they typically try to read. I had a developer build a multi-threaded
"database shotgun" for doing maintenance stuff. HUGE help!
TheSQLGuru
President
Indicium Resources, Inc.
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegr oups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>
|||> they typically try to read. I had a developer build a multi-threaded
> "database shotgun" for doing maintenance stuff. HUGE help!
I use SQL Farm Combine for this.
http://www.sqlfarms.com/
|||On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Hmm, can you elaborate on this?
Just speculating wildly.
If they are a single database, there is at least a *chance* customers,
users, sessions, etc will share pages!
>Perhaps plan caching is a bigger issue?
Ugh, you're right!
But then, wouldn't this be quickly obvious to anyone running
hundreds++ of databases? And the initial comments mostly seem to be
that it runs pretty well.
Josh
|||> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
Agreed. To give a more precise answer, we would have to know the data model after coalescing into
one database.
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
True. But "run pretty well" doesn't mean that it cannot run better... :-)
Of course, this as well depends on the app. If stored procedures are used, then I expect to se some
benefit of having, for a particular proc, only one plan in cache instead of one per database. If
procedures are not used, then chances are that we mostly have exact text matching of the query
string in cache, and re-use would be minimal even if all is in the same database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:9acib3h5ct5mkgtoectjg2s9aqe8k5633k@.4ax.com...
> On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Just speculating wildly.
> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
>
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
> Josh
>
>
Maximum Recommended Databases
I've read MSDN that the theoretical maximum number of databases per
SQL server instance is 32,767. But I'd like to know if there is a
real world recommendation from Microsoft. I know this sounds like a
ridiculous question, but I've inherited an instance with approximately
1,000 databases (and growing) and I'm trying to determine when I
should expect major performance issues (don't laugh). I have a plan
for reducing the number of databases, but it won't happen over night.
I know a lot of this depends on database usage, but I'm just looking
for any information I can find. Thanks in advance!I have a client with over 5000 databases and still growing. The usage
pattern is such that only a few dozen or even a few hundred are heavily used
at any one time. The system is performing quite well.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||I don't think I'm going to provide any new information that you don't
already have. But this is kind of like asking how big can a database be, or
how many rows can I get into a table? As the number of databases goes up,
the usage pattern really determines how detrimental each additional database
is to performance (let's leave administration costs out of this for now).
So many factors are involved that it's impossible for anyone to come up with
a real number that would apply to your situation and my situation
identically, even if you could feasibly perform all the real-world tests
required to come up with such a number...
When should you expect performance issues? When your usage patterns exceed
your hardware's capabilities. It is absolutely impossible to tell you that
it will happen when you add 5 or 500 more databases. In fact, it could
conceivably happen after you /*remove*/ databases ... if users of the
remaining databases coincidentally change their usage patterns.
I have relatively high-volume systems with over 500 databases, and they work
very well. Would I want to throw another 500 databases on there? Not in
one shot, I don't think. But it's not the number itself that scares me.
It's the fact that 10 or 15 of those (given my current scenario) would
likely end up high transaction hitters that *could* start to destabilize the
system. On the other hand, if they're all duds, then 500 could be added
easily without any impact to the system.
Aaron Bertrand
SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
wrote:
>I've read MSDN that the theoretical maximum number of databases per
>SQL server instance is 32,767. But I'd like to know if there is a
>real world recommendation from Microsoft. I know this sounds like a
>ridiculous question, but I've inherited an instance with approximately
>1,000 databases (and growing) and I'm trying to determine when I
>should expect major performance issues (don't laugh).
The critical problem would probably be that they will never share
pages in a working set, so caching will be less effective. But that's
if they're all active at once, which is unlikely ... isn't it?
A few more open file handles at the OS level, ... nah. Whatever
insight you can get on data locality and access patterns is probably
best, but hey, that's true even if it's all a single database, too!
Josh|||> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective.
Hmm, can you elaborate on this? When you have several databases, the data fo
r each customer (or
whatever differentiate these databases) obviously need to be in separate tab
les, per customer. So,
data for one customer is on a separate set of pages compared to some other c
ustomer meaning data
retrieved for one customer cannot be used when querying from some other cust
omer. But the same thing
would happen if the same set of table were in one database (except for very
small tables where mixed
extents come into play). But if this is re-modeled and all customers share t
he same table, we have a
different situation...
> A few more open file handles at the OS level
Interesting you mention this, since this can impact caching. With many datab
ases, setting auto close
might be needed (especially in 32 bit due to OS limitation and open file han
dles). And when a
database is auto closed, data in cache is removed (I know for sure this appl
ies to plans, I'd have
to do a test to verify it applies to data).
Perhaps plan caching is a bigger issue? Assuming best practices are followed
and stored procedures
are used. Now, we would end up with duplicate procedures, one set for each d
atabase. Even if we
don't use stored procedures, we won't be able to re-use cached plans across
databases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:799fb3l6bsc2its7r7mfma3kjqr65gekh1@.
4ax.com...
> On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
> wrote:
>
> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective. But that's
> if they're all active at once, which is unlikely ... isn't it?
> A few more open file handles at the OS level, ... nah. Whatever
> insight you can get on data locality and access patterns is probably
> best, but hey, that's true even if it's all a single database, too!
> Josh
>|||I have a client with 6500+ databases. Biggest issues are rolling out
changes, size of msdb database (make it BIG to begin with to avoid
fragmentation from autogrowths), and most 3rd party tools break down when
you try to use them against the server due to massive amounts of system data
they typically try to read. I had a developer build a multi-threaded
"database shotgun" for doing maintenance stuff. HUGE help!
TheSQLGuru
President
Indicium Resources, Inc.
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||> they typically try to read. I had a developer build a multi-threaded
> "database shotgun" for doing maintenance stuff. HUGE help!
I use SQL Farm Combine for this.
http://www.sqlfarms.com/|||On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Hmm, can you elaborate on this?
Just speculating wildly.
If they are a single database, there is at least a *chance* customers,
users, sessions, etc will share pages!
>Perhaps plan caching is a bigger issue?
Ugh, you're right!
But then, wouldn't this be quickly obvious to anyone running
hundreds++ of databases? And the initial comments mostly seem to be
that it runs pretty well.
Josh|||> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
Agreed. To give a more precise answer, we would have to know the data model
after coalescing into
one database.
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
True. But "run pretty well" doesn't mean that it cannot run better... :-)
Of course, this as well depends on the app. If stored procedures are used, t
hen I expect to se some
benefit of having, for a particular proc, only one plan in cache instead of
one per database. If
procedures are not used, then chances are that we mostly have exact text mat
ching of the query
string in cache, and re-use would be minimal even if all is in the same data
base.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:9acib3h5ct5mkgtoectjg2s9aqe8k5633k@.
4ax.com...
> On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Just speculating wildly.
> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
>
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
> Josh
>
>
SQL server instance is 32,767. But I'd like to know if there is a
real world recommendation from Microsoft. I know this sounds like a
ridiculous question, but I've inherited an instance with approximately
1,000 databases (and growing) and I'm trying to determine when I
should expect major performance issues (don't laugh). I have a plan
for reducing the number of databases, but it won't happen over night.
I know a lot of this depends on database usage, but I'm just looking
for any information I can find. Thanks in advance!I have a client with over 5000 databases and still growing. The usage
pattern is such that only a few dozen or even a few hundred are heavily used
at any one time. The system is performing quite well.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||I don't think I'm going to provide any new information that you don't
already have. But this is kind of like asking how big can a database be, or
how many rows can I get into a table? As the number of databases goes up,
the usage pattern really determines how detrimental each additional database
is to performance (let's leave administration costs out of this for now).
So many factors are involved that it's impossible for anyone to come up with
a real number that would apply to your situation and my situation
identically, even if you could feasibly perform all the real-world tests
required to come up with such a number...
When should you expect performance issues? When your usage patterns exceed
your hardware's capabilities. It is absolutely impossible to tell you that
it will happen when you add 5 or 500 more databases. In fact, it could
conceivably happen after you /*remove*/ databases ... if users of the
remaining databases coincidentally change their usage patterns.
I have relatively high-volume systems with over 500 databases, and they work
very well. Would I want to throw another 500 databases on there? Not in
one shot, I don't think. But it's not the number itself that scares me.
It's the fact that 10 or 15 of those (given my current scenario) would
likely end up high transaction hitters that *could* start to destabilize the
system. On the other hand, if they're all duds, then 500 could be added
easily without any impact to the system.
Aaron Bertrand
SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
wrote:
>I've read MSDN that the theoretical maximum number of databases per
>SQL server instance is 32,767. But I'd like to know if there is a
>real world recommendation from Microsoft. I know this sounds like a
>ridiculous question, but I've inherited an instance with approximately
>1,000 databases (and growing) and I'm trying to determine when I
>should expect major performance issues (don't laugh).
The critical problem would probably be that they will never share
pages in a working set, so caching will be less effective. But that's
if they're all active at once, which is unlikely ... isn't it?
A few more open file handles at the OS level, ... nah. Whatever
insight you can get on data locality and access patterns is probably
best, but hey, that's true even if it's all a single database, too!
Josh|||> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective.
Hmm, can you elaborate on this? When you have several databases, the data fo
r each customer (or
whatever differentiate these databases) obviously need to be in separate tab
les, per customer. So,
data for one customer is on a separate set of pages compared to some other c
ustomer meaning data
retrieved for one customer cannot be used when querying from some other cust
omer. But the same thing
would happen if the same set of table were in one database (except for very
small tables where mixed
extents come into play). But if this is re-modeled and all customers share t
he same table, we have a
different situation...
> A few more open file handles at the OS level
Interesting you mention this, since this can impact caching. With many datab
ases, setting auto close
might be needed (especially in 32 bit due to OS limitation and open file han
dles). And when a
database is auto closed, data in cache is removed (I know for sure this appl
ies to plans, I'd have
to do a test to verify it applies to data).
Perhaps plan caching is a bigger issue? Assuming best practices are followed
and stored procedures
are used. Now, we would end up with duplicate procedures, one set for each d
atabase. Even if we
don't use stored procedures, we won't be able to re-use cached plans across
databases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:799fb3l6bsc2its7r7mfma3kjqr65gekh1@.
4ax.com...
> On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
> wrote:
>
> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective. But that's
> if they're all active at once, which is unlikely ... isn't it?
> A few more open file handles at the OS level, ... nah. Whatever
> insight you can get on data locality and access patterns is probably
> best, but hey, that's true even if it's all a single database, too!
> Josh
>|||I have a client with 6500+ databases. Biggest issues are rolling out
changes, size of msdb database (make it BIG to begin with to avoid
fragmentation from autogrowths), and most 3rd party tools break down when
you try to use them against the server due to massive amounts of system data
they typically try to read. I had a developer build a multi-threaded
"database shotgun" for doing maintenance stuff. HUGE help!
TheSQLGuru
President
Indicium Resources, Inc.
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||> they typically try to read. I had a developer build a multi-threaded
> "database shotgun" for doing maintenance stuff. HUGE help!
I use SQL Farm Combine for this.
http://www.sqlfarms.com/|||On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Hmm, can you elaborate on this?
Just speculating wildly.
If they are a single database, there is at least a *chance* customers,
users, sessions, etc will share pages!
>Perhaps plan caching is a bigger issue?
Ugh, you're right!
But then, wouldn't this be quickly obvious to anyone running
hundreds++ of databases? And the initial comments mostly seem to be
that it runs pretty well.
Josh|||> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
Agreed. To give a more precise answer, we would have to know the data model
after coalescing into
one database.
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
True. But "run pretty well" doesn't mean that it cannot run better... :-)
Of course, this as well depends on the app. If stored procedures are used, t
hen I expect to se some
benefit of having, for a particular proc, only one plan in cache instead of
one per database. If
procedures are not used, then chances are that we mostly have exact text mat
ching of the query
string in cache, and re-use would be minimal even if all is in the same data
base.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:9acib3h5ct5mkgtoectjg2s9aqe8k5633k@.
4ax.com...
> On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Just speculating wildly.
> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
>
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
> Josh
>
>
Maximum Recommended Databases
I've read MSDN that the theoretical maximum number of databases per
SQL server instance is 32,767. But I'd like to know if there is a
real world recommendation from Microsoft. I know this sounds like a
ridiculous question, but I've inherited an instance with approximately
1,000 databases (and growing) and I'm trying to determine when I
should expect major performance issues (don't laugh). I have a plan
for reducing the number of databases, but it won't happen over night.
I know a lot of this depends on database usage, but I'm just looking
for any information I can find. Thanks in advance!I have a client with over 5000 databases and still growing. The usage
pattern is such that only a few dozen or even a few hundred are heavily used
at any one time. The system is performing quite well.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||I don't think I'm going to provide any new information that you don't
already have. But this is kind of like asking how big can a database be, or
how many rows can I get into a table? As the number of databases goes up,
the usage pattern really determines how detrimental each additional database
is to performance (let's leave administration costs out of this for now).
So many factors are involved that it's impossible for anyone to come up with
a real number that would apply to your situation and my situation
identically, even if you could feasibly perform all the real-world tests
required to come up with such a number...
When should you expect performance issues? When your usage patterns exceed
your hardware's capabilities. It is absolutely impossible to tell you that
it will happen when you add 5 or 500 more databases. In fact, it could
conceivably happen after you /*remove*/ databases ... if users of the
remaining databases coincidentally change their usage patterns.
I have relatively high-volume systems with over 500 databases, and they work
very well. Would I want to throw another 500 databases on there? Not in
one shot, I don't think. But it's not the number itself that scares me.
It's the fact that 10 or 15 of those (given my current scenario) would
likely end up high transaction hitters that *could* start to destabilize the
system. On the other hand, if they're all duds, then 500 could be added
easily without any impact to the system.
--
Aaron Bertrand
SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
wrote:
>I've read MSDN that the theoretical maximum number of databases per
>SQL server instance is 32,767. But I'd like to know if there is a
>real world recommendation from Microsoft. I know this sounds like a
>ridiculous question, but I've inherited an instance with approximately
>1,000 databases (and growing) and I'm trying to determine when I
>should expect major performance issues (don't laugh).
The critical problem would probably be that they will never share
pages in a working set, so caching will be less effective. But that's
if they're all active at once, which is unlikely ... isn't it?
A few more open file handles at the OS level, ... nah. Whatever
insight you can get on data locality and access patterns is probably
best, but hey, that's true even if it's all a single database, too!
Josh|||> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective.
Hmm, can you elaborate on this? When you have several databases, the data for each customer (or
whatever differentiate these databases) obviously need to be in separate tables, per customer. So,
data for one customer is on a separate set of pages compared to some other customer meaning data
retrieved for one customer cannot be used when querying from some other customer. But the same thing
would happen if the same set of table were in one database (except for very small tables where mixed
extents come into play). But if this is re-modeled and all customers share the same table, we have a
different situation...
> A few more open file handles at the OS level
Interesting you mention this, since this can impact caching. With many databases, setting auto close
might be needed (especially in 32 bit due to OS limitation and open file handles). And when a
database is auto closed, data in cache is removed (I know for sure this applies to plans, I'd have
to do a test to verify it applies to data).
Perhaps plan caching is a bigger issue? Assuming best practices are followed and stored procedures
are used. Now, we would end up with duplicate procedures, one set for each database. Even if we
don't use stored procedures, we won't be able to re-use cached plans across databases...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:799fb3l6bsc2its7r7mfma3kjqr65gekh1@.4ax.com...
> On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
> wrote:
>>I've read MSDN that the theoretical maximum number of databases per
>>SQL server instance is 32,767. But I'd like to know if there is a
>>real world recommendation from Microsoft. I know this sounds like a
>>ridiculous question, but I've inherited an instance with approximately
>>1,000 databases (and growing) and I'm trying to determine when I
>>should expect major performance issues (don't laugh).
> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective. But that's
> if they're all active at once, which is unlikely ... isn't it?
> A few more open file handles at the OS level, ... nah. Whatever
> insight you can get on data locality and access patterns is probably
> best, but hey, that's true even if it's all a single database, too!
> Josh
>|||I have a client with 6500+ databases. Biggest issues are rolling out
changes, size of msdb database (make it BIG to begin with to avoid
fragmentation from autogrowths), and most 3rd party tools break down when
you try to use them against the server due to massive amounts of system data
they typically try to read. I had a developer build a multi-threaded
"database shotgun" for doing maintenance stuff. HUGE help!
--
TheSQLGuru
President
Indicium Resources, Inc.
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||> they typically try to read. I had a developer build a multi-threaded
> "database shotgun" for doing maintenance stuff. HUGE help!
I use SQL Farm Combine for this.
http://www.sqlfarms.com/|||On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>> The critical problem would probably be that they will never share
>> pages in a working set, so caching will be less effective.
>Hmm, can you elaborate on this?
Just speculating wildly.
If they are a single database, there is at least a *chance* customers,
users, sessions, etc will share pages!
>Perhaps plan caching is a bigger issue?
Ugh, you're right!
But then, wouldn't this be quickly obvious to anyone running
hundreds++ of databases? And the initial comments mostly seem to be
that it runs pretty well.
Josh|||> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
Agreed. To give a more precise answer, we would have to know the data model after coalescing into
one database.
>>Perhaps plan caching is a bigger issue?
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
True. But "run pretty well" doesn't mean that it cannot run better... :-)
Of course, this as well depends on the app. If stored procedures are used, then I expect to se some
benefit of having, for a particular proc, only one plan in cache instead of one per database. If
procedures are not used, then chances are that we mostly have exact text matching of the query
string in cache, and re-use would be minimal even if all is in the same database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:9acib3h5ct5mkgtoectjg2s9aqe8k5633k@.4ax.com...
> On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>> The critical problem would probably be that they will never share
>> pages in a working set, so caching will be less effective.
>>Hmm, can you elaborate on this?
> Just speculating wildly.
> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
>>Perhaps plan caching is a bigger issue?
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
> Josh
>
>
SQL server instance is 32,767. But I'd like to know if there is a
real world recommendation from Microsoft. I know this sounds like a
ridiculous question, but I've inherited an instance with approximately
1,000 databases (and growing) and I'm trying to determine when I
should expect major performance issues (don't laugh). I have a plan
for reducing the number of databases, but it won't happen over night.
I know a lot of this depends on database usage, but I'm just looking
for any information I can find. Thanks in advance!I have a client with over 5000 databases and still growing. The usage
pattern is such that only a few dozen or even a few hundred are heavily used
at any one time. The system is performing quite well.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||I don't think I'm going to provide any new information that you don't
already have. But this is kind of like asking how big can a database be, or
how many rows can I get into a table? As the number of databases goes up,
the usage pattern really determines how detrimental each additional database
is to performance (let's leave administration costs out of this for now).
So many factors are involved that it's impossible for anyone to come up with
a real number that would apply to your situation and my situation
identically, even if you could feasibly perform all the real-world tests
required to come up with such a number...
When should you expect performance issues? When your usage patterns exceed
your hardware's capabilities. It is absolutely impossible to tell you that
it will happen when you add 5 or 500 more databases. In fact, it could
conceivably happen after you /*remove*/ databases ... if users of the
remaining databases coincidentally change their usage patterns.
I have relatively high-volume systems with over 500 databases, and they work
very well. Would I want to throw another 500 databases on there? Not in
one shot, I don't think. But it's not the number itself that scares me.
It's the fact that 10 or 15 of those (given my current scenario) would
likely end up high transaction hitters that *could* start to destabilize the
system. On the other hand, if they're all duds, then 500 could be added
easily without any impact to the system.
--
Aaron Bertrand
SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
wrote:
>I've read MSDN that the theoretical maximum number of databases per
>SQL server instance is 32,767. But I'd like to know if there is a
>real world recommendation from Microsoft. I know this sounds like a
>ridiculous question, but I've inherited an instance with approximately
>1,000 databases (and growing) and I'm trying to determine when I
>should expect major performance issues (don't laugh).
The critical problem would probably be that they will never share
pages in a working set, so caching will be less effective. But that's
if they're all active at once, which is unlikely ... isn't it?
A few more open file handles at the OS level, ... nah. Whatever
insight you can get on data locality and access patterns is probably
best, but hey, that's true even if it's all a single database, too!
Josh|||> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective.
Hmm, can you elaborate on this? When you have several databases, the data for each customer (or
whatever differentiate these databases) obviously need to be in separate tables, per customer. So,
data for one customer is on a separate set of pages compared to some other customer meaning data
retrieved for one customer cannot be used when querying from some other customer. But the same thing
would happen if the same set of table were in one database (except for very small tables where mixed
extents come into play). But if this is re-modeled and all customers share the same table, we have a
different situation...
> A few more open file handles at the OS level
Interesting you mention this, since this can impact caching. With many databases, setting auto close
might be needed (especially in 32 bit due to OS limitation and open file handles). And when a
database is auto closed, data in cache is removed (I know for sure this applies to plans, I'd have
to do a test to verify it applies to data).
Perhaps plan caching is a bigger issue? Assuming best practices are followed and stored procedures
are used. Now, we would end up with duplicate procedures, one set for each database. Even if we
don't use stored procedures, we won't be able to re-use cached plans across databases...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:799fb3l6bsc2its7r7mfma3kjqr65gekh1@.4ax.com...
> On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
> wrote:
>>I've read MSDN that the theoretical maximum number of databases per
>>SQL server instance is 32,767. But I'd like to know if there is a
>>real world recommendation from Microsoft. I know this sounds like a
>>ridiculous question, but I've inherited an instance with approximately
>>1,000 databases (and growing) and I'm trying to determine when I
>>should expect major performance issues (don't laugh).
> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective. But that's
> if they're all active at once, which is unlikely ... isn't it?
> A few more open file handles at the OS level, ... nah. Whatever
> insight you can get on data locality and access patterns is probably
> best, but hey, that's true even if it's all a single database, too!
> Josh
>|||I have a client with 6500+ databases. Biggest issues are rolling out
changes, size of msdb database (make it BIG to begin with to avoid
fragmentation from autogrowths), and most 3rd party tools break down when
you try to use them against the server due to massive amounts of system data
they typically try to read. I had a developer build a multi-threaded
"database shotgun" for doing maintenance stuff. HUGE help!
--
TheSQLGuru
President
Indicium Resources, Inc.
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegroups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>|||> they typically try to read. I had a developer build a multi-threaded
> "database shotgun" for doing maintenance stuff. HUGE help!
I use SQL Farm Combine for this.
http://www.sqlfarms.com/|||On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>> The critical problem would probably be that they will never share
>> pages in a working set, so caching will be less effective.
>Hmm, can you elaborate on this?
Just speculating wildly.
If they are a single database, there is at least a *chance* customers,
users, sessions, etc will share pages!
>Perhaps plan caching is a bigger issue?
Ugh, you're right!
But then, wouldn't this be quickly obvious to anyone running
hundreds++ of databases? And the initial comments mostly seem to be
that it runs pretty well.
Josh|||> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
Agreed. To give a more precise answer, we would have to know the data model after coalescing into
one database.
>>Perhaps plan caching is a bigger issue?
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
True. But "run pretty well" doesn't mean that it cannot run better... :-)
Of course, this as well depends on the app. If stored procedures are used, then I expect to se some
benefit of having, for a particular proc, only one plan in cache instead of one per database. If
procedures are not used, then chances are that we mostly have exact text matching of the query
string in cache, and re-use would be minimal even if all is in the same database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:9acib3h5ct5mkgtoectjg2s9aqe8k5633k@.4ax.com...
> On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>> The critical problem would probably be that they will never share
>> pages in a working set, so caching will be less effective.
>>Hmm, can you elaborate on this?
> Just speculating wildly.
> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
>>Perhaps plan caching is a bigger issue?
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
> Josh
>
>
Maximum practical number of databases per SQL Server 2000 Instance
Does anyone know what the maximum practical number of databases that could be
created and used per a SQL Server 2000 Enterprise Instance. I know that the
theoretical limit in BooksOnLine is 32K. But my application is dying way
before that.
Thanks in advance
Cesar
Exactly what is dying? It's usually not the number of databases but more
what you are doing with them and how you have your resources configured.
Andrew J. Kelly SQL MVP
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> Does anyone know what the maximum practical number of databases that could
> be
> created and used per a SQL Server 2000 Enterprise Instance. I know that
> the
> theoretical limit in BooksOnLine is 32K. But my application is dying way
> before that.
> Thanks in advance
> Cesar
|||We have hundreds of databases on our cluster. It creates some interesting
issue (starting and stopping the service takes a looooong time, for
example). But it runs pretty smoothly, all things considered.
Bob Castleman
SuccessWare Software
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> Does anyone know what the maximum practical number of databases that could
> be
> created and used per a SQL Server 2000 Enterprise Instance. I know that
> the
> theoretical limit in BooksOnLine is 32K. But my application is dying way
> before that.
> Thanks in advance
> Cesar
|||Hi
There are some KB articles on what needs to changed in SQL Server for it to
work well with more than 1'000 DB's. It comes down to having enough memory
available for the file structures. I don't have the KB at hand.
The more DB's you have, the more difficult it is to manage them with
Enterprise Manager. And one big issue, they all share TempDB, so if the
applications are written badly, you have a nice bottleneck.
I have tested to 600 DB's. Shutdown does take a while, and so does startup.
Enterprise Manager took 32 seconds to render the screen.
If I were you, and it is possible in your scenario, I would run multiple
instances with about 100 DB's per instance. Then, applying a SP or a hotfix
does not affect every DB, neither does a failure.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Castleman" <nomail@.here> wrote in message
news:#xuo4wu4EHA.2124@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> We have hundreds of databases on our cluster. It creates some interesting
> issue (starting and stopping the service takes a looooong time, for
> example). But it runs pretty smoothly, all things considered.
> Bob Castleman
> SuccessWare Software
> "Cesar" <Cesar@.discussions.microsoft.com> wrote in message
> news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
could[vbcol=seagreen]
way
>
created and used per a SQL Server 2000 Enterprise Instance. I know that the
theoretical limit in BooksOnLine is 32K. But my application is dying way
before that.
Thanks in advance
Cesar
Exactly what is dying? It's usually not the number of databases but more
what you are doing with them and how you have your resources configured.
Andrew J. Kelly SQL MVP
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> Does anyone know what the maximum practical number of databases that could
> be
> created and used per a SQL Server 2000 Enterprise Instance. I know that
> the
> theoretical limit in BooksOnLine is 32K. But my application is dying way
> before that.
> Thanks in advance
> Cesar
|||We have hundreds of databases on our cluster. It creates some interesting
issue (starting and stopping the service takes a looooong time, for
example). But it runs pretty smoothly, all things considered.
Bob Castleman
SuccessWare Software
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> Does anyone know what the maximum practical number of databases that could
> be
> created and used per a SQL Server 2000 Enterprise Instance. I know that
> the
> theoretical limit in BooksOnLine is 32K. But my application is dying way
> before that.
> Thanks in advance
> Cesar
|||Hi
There are some KB articles on what needs to changed in SQL Server for it to
work well with more than 1'000 DB's. It comes down to having enough memory
available for the file structures. I don't have the KB at hand.
The more DB's you have, the more difficult it is to manage them with
Enterprise Manager. And one big issue, they all share TempDB, so if the
applications are written badly, you have a nice bottleneck.
I have tested to 600 DB's. Shutdown does take a while, and so does startup.
Enterprise Manager took 32 seconds to render the screen.
If I were you, and it is possible in your scenario, I would run multiple
instances with about 100 DB's per instance. Then, applying a SP or a hotfix
does not affect every DB, neither does a failure.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Castleman" <nomail@.here> wrote in message
news:#xuo4wu4EHA.2124@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> We have hundreds of databases on our cluster. It creates some interesting
> issue (starting and stopping the service takes a looooong time, for
> example). But it runs pretty smoothly, all things considered.
> Bob Castleman
> SuccessWare Software
> "Cesar" <Cesar@.discussions.microsoft.com> wrote in message
> news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
could[vbcol=seagreen]
way
>
Maximum practical number of databases per SQL Server 2000 Instance
Does anyone know what the maximum practical number of databases that could be
created and used per a SQL Server 2000 Enterprise Instance. I know that the
theoretical limit in BooksOnLine is 32K. But my application is dying way
before that.
Thanks in advance
CesarExactly what is dying? It's usually not the number of databases but more
what you are doing with them and how you have your resources configured.
--
Andrew J. Kelly SQL MVP
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> Does anyone know what the maximum practical number of databases that could
> be
> created and used per a SQL Server 2000 Enterprise Instance. I know that
> the
> theoretical limit in BooksOnLine is 32K. But my application is dying way
> before that.
> Thanks in advance
> Cesar|||We have hundreds of databases on our cluster. It creates some interesting
issue (starting and stopping the service takes a looooong time, for
example). But it runs pretty smoothly, all things considered.
Bob Castleman
SuccessWare Software
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> Does anyone know what the maximum practical number of databases that could
> be
> created and used per a SQL Server 2000 Enterprise Instance. I know that
> the
> theoretical limit in BooksOnLine is 32K. But my application is dying way
> before that.
> Thanks in advance
> Cesar|||Hi
There are some KB articles on what needs to changed in SQL Server for it to
work well with more than 1'000 DB's. It comes down to having enough memory
available for the file structures. I don't have the KB at hand.
The more DB's you have, the more difficult it is to manage them with
Enterprise Manager. And one big issue, they all share TempDB, so if the
applications are written badly, you have a nice bottleneck.
I have tested to 600 DB's. Shutdown does take a while, and so does startup.
Enterprise Manager took 32 seconds to render the screen.
If I were you, and it is possible in your scenario, I would run multiple
instances with about 100 DB's per instance. Then, applying a SP or a hotfix
does not affect every DB, neither does a failure.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Castleman" <nomail@.here> wrote in message
news:#xuo4wu4EHA.2124@.TK2MSFTNGP15.phx.gbl...
> We have hundreds of databases on our cluster. It creates some interesting
> issue (starting and stopping the service takes a looooong time, for
> example). But it runs pretty smoothly, all things considered.
> Bob Castleman
> SuccessWare Software
> "Cesar" <Cesar@.discussions.microsoft.com> wrote in message
> news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> > Does anyone know what the maximum practical number of databases that
could
> > be
> > created and used per a SQL Server 2000 Enterprise Instance. I know that
> > the
> > theoretical limit in BooksOnLine is 32K. But my application is dying
way
> > before that.
> >
> > Thanks in advance
> >
> > Cesar
>|||Thanks for all of your suggestions.
The error I was getting was something like "Out of Memory". I think I know
how to fix it. I was creating thousands of small DBs and by default they
stay in mostly in memory and eventually my machine runs out of memory. When
I enabled the "AutoClose" property my small DBs, that flushes them from
memory and they reside entirely on the HD. That allowed me to create 10K DBs
in my test environment without any problems or SQL Server process to increase
in memory usage.
Thanks
Cesar
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> There are some KB articles on what needs to changed in SQL Server for it to
> work well with more than 1'000 DB's. It comes down to having enough memory
> available for the file structures. I don't have the KB at hand.
> The more DB's you have, the more difficult it is to manage them with
> Enterprise Manager. And one big issue, they all share TempDB, so if the
> applications are written badly, you have a nice bottleneck.
> I have tested to 600 DB's. Shutdown does take a while, and so does startup.
> Enterprise Manager took 32 seconds to render the screen.
> If I were you, and it is possible in your scenario, I would run multiple
> instances with about 100 DB's per instance. Then, applying a SP or a hotfix
> does not affect every DB, neither does a failure.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Bob Castleman" <nomail@.here> wrote in message
> news:#xuo4wu4EHA.2124@.TK2MSFTNGP15.phx.gbl...
> > We have hundreds of databases on our cluster. It creates some interesting
> > issue (starting and stopping the service takes a looooong time, for
> > example). But it runs pretty smoothly, all things considered.
> >
> > Bob Castleman
> > SuccessWare Software
> > "Cesar" <Cesar@.discussions.microsoft.com> wrote in message
> > news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> > > Does anyone know what the maximum practical number of databases that
> could
> > > be
> > > created and used per a SQL Server 2000 Enterprise Instance. I know that
> > > the
> > > theoretical limit in BooksOnLine is 32K. But my application is dying
> way
> > > before that.
> > >
> > > Thanks in advance
> > >
> > > Cesar
> >
> >
>
>
created and used per a SQL Server 2000 Enterprise Instance. I know that the
theoretical limit in BooksOnLine is 32K. But my application is dying way
before that.
Thanks in advance
CesarExactly what is dying? It's usually not the number of databases but more
what you are doing with them and how you have your resources configured.
--
Andrew J. Kelly SQL MVP
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> Does anyone know what the maximum practical number of databases that could
> be
> created and used per a SQL Server 2000 Enterprise Instance. I know that
> the
> theoretical limit in BooksOnLine is 32K. But my application is dying way
> before that.
> Thanks in advance
> Cesar|||We have hundreds of databases on our cluster. It creates some interesting
issue (starting and stopping the service takes a looooong time, for
example). But it runs pretty smoothly, all things considered.
Bob Castleman
SuccessWare Software
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> Does anyone know what the maximum practical number of databases that could
> be
> created and used per a SQL Server 2000 Enterprise Instance. I know that
> the
> theoretical limit in BooksOnLine is 32K. But my application is dying way
> before that.
> Thanks in advance
> Cesar|||Hi
There are some KB articles on what needs to changed in SQL Server for it to
work well with more than 1'000 DB's. It comes down to having enough memory
available for the file structures. I don't have the KB at hand.
The more DB's you have, the more difficult it is to manage them with
Enterprise Manager. And one big issue, they all share TempDB, so if the
applications are written badly, you have a nice bottleneck.
I have tested to 600 DB's. Shutdown does take a while, and so does startup.
Enterprise Manager took 32 seconds to render the screen.
If I were you, and it is possible in your scenario, I would run multiple
instances with about 100 DB's per instance. Then, applying a SP or a hotfix
does not affect every DB, neither does a failure.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Castleman" <nomail@.here> wrote in message
news:#xuo4wu4EHA.2124@.TK2MSFTNGP15.phx.gbl...
> We have hundreds of databases on our cluster. It creates some interesting
> issue (starting and stopping the service takes a looooong time, for
> example). But it runs pretty smoothly, all things considered.
> Bob Castleman
> SuccessWare Software
> "Cesar" <Cesar@.discussions.microsoft.com> wrote in message
> news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> > Does anyone know what the maximum practical number of databases that
could
> > be
> > created and used per a SQL Server 2000 Enterprise Instance. I know that
> > the
> > theoretical limit in BooksOnLine is 32K. But my application is dying
way
> > before that.
> >
> > Thanks in advance
> >
> > Cesar
>|||Thanks for all of your suggestions.
The error I was getting was something like "Out of Memory". I think I know
how to fix it. I was creating thousands of small DBs and by default they
stay in mostly in memory and eventually my machine runs out of memory. When
I enabled the "AutoClose" property my small DBs, that flushes them from
memory and they reside entirely on the HD. That allowed me to create 10K DBs
in my test environment without any problems or SQL Server process to increase
in memory usage.
Thanks
Cesar
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> There are some KB articles on what needs to changed in SQL Server for it to
> work well with more than 1'000 DB's. It comes down to having enough memory
> available for the file structures. I don't have the KB at hand.
> The more DB's you have, the more difficult it is to manage them with
> Enterprise Manager. And one big issue, they all share TempDB, so if the
> applications are written badly, you have a nice bottleneck.
> I have tested to 600 DB's. Shutdown does take a while, and so does startup.
> Enterprise Manager took 32 seconds to render the screen.
> If I were you, and it is possible in your scenario, I would run multiple
> instances with about 100 DB's per instance. Then, applying a SP or a hotfix
> does not affect every DB, neither does a failure.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Bob Castleman" <nomail@.here> wrote in message
> news:#xuo4wu4EHA.2124@.TK2MSFTNGP15.phx.gbl...
> > We have hundreds of databases on our cluster. It creates some interesting
> > issue (starting and stopping the service takes a looooong time, for
> > example). But it runs pretty smoothly, all things considered.
> >
> > Bob Castleman
> > SuccessWare Software
> > "Cesar" <Cesar@.discussions.microsoft.com> wrote in message
> > news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> > > Does anyone know what the maximum practical number of databases that
> could
> > > be
> > > created and used per a SQL Server 2000 Enterprise Instance. I know that
> > > the
> > > theoretical limit in BooksOnLine is 32K. But my application is dying
> way
> > > before that.
> > >
> > > Thanks in advance
> > >
> > > Cesar
> >
> >
>
>
Monday, March 12, 2012
maximum of DBprocess already allocated
Hi all,
Sql server 7.0
our client is getting the below error while accessing the web application
Maximum number of DBPROCESSes already allocated
when we check the server from our side its working fine.
please let us know is the solution for this.
TIATHis should help . . . (watch wraparound)
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q164/1/71.asp&NoWebContent=1&NoWebContent=1|||hi thanks for the reply
the error we are getting is given more detailed below
DBPool - Failed to initialize RWDBConnection
Unknown Exception caught in DBConnPool::createConnection() -- RWDatabase::connection() or validation of the connection returned failed
Database middleware messages:
ErrorCode: vendorLib Msg VENDORLIB] Vendor Library Error: Maximum number of DBPROCESSes already allocated
Pls help!!!!
Sql server 7.0
our client is getting the below error while accessing the web application
Maximum number of DBPROCESSes already allocated
when we check the server from our side its working fine.
please let us know is the solution for this.
TIATHis should help . . . (watch wraparound)
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q164/1/71.asp&NoWebContent=1&NoWebContent=1|||hi thanks for the reply
the error we are getting is given more detailed below
DBPool - Failed to initialize RWDBConnection
Unknown Exception caught in DBConnPool::createConnection() -- RWDatabase::connection() or validation of the connection returned failed
Database middleware messages:
ErrorCode: vendorLib Msg VENDORLIB] Vendor Library Error: Maximum number of DBPROCESSes already allocated
Pls help!!!!
maximum number of views
What is the maximum number of views that you can create
for a SQL Server 2000 database?
Only limited by the maximum number of objects you can create in a =
database - which is huge (documented in BOL) under "maximum capacity =
specifications". If i remember right it is about 2 billion in theory =
but something else will get you first, so for sake of argument I would =
sy there is no practical limit.
I'm interested why this would be an issue?
Mike John
"Derek Ruesch" <anonymous@.discussions.microsoft.com> wrote in message =
news:31c901c470fa$1c952d90$a301280a@.phx.gbl...
> What is the maximum number of views that you can create=20
> for a SQL Server 2000 database?
|||Mike,
I have 31 views currently in my database and I plan on
adding another 20 views or so. I just wanted to make sure
that I could have this many views.
Derek
>--Original Message--
>Only limited by the maximum number of objects you can
create in a database - which is huge (documented in BOL)
under "maximum capacity specifications". If i remember
right it is about 2 billion in theory but something else
will get you first, so for sake of argument I would sy
there is no practical limit.
>I'm interested why this would be an issue?
>Mike John
>"Derek Ruesch" <anonymous@.discussions.microsoft.com>
wrote in message news:31c901c470fa$1c952d90
$a301280a@.phx.gbl...
>.
>
|||No problem whatsoever from a system limit point of view. I was a bit =
concerned you planned on creating the odd 10000 or so, which would have =
impled a very odd design!
Mike John
"Derek Ruesch" <anonymous@.discussions.microsoft.com> wrote in message =
news:321101c47101$615f7b90$a301280a@.phx.gbl...[vbcol=seagreen]
> Mike,
>=20
> I have 31 views currently in my database and I plan on=20
> adding another 20 views or so. I just wanted to make sure=20
> that I could have this many views.
>=20
> Derek
>=20
> create in a database - which is huge (documented in BOL)=20
> under "maximum capacity specifications". If i remember=20
> right it is about 2 billion in theory but something else=20
> will get you first, so for sake of argument I would sy=20
> there is no practical limit.
> wrote in message news:31c901c470fa$1c952d90
> $a301280a@.phx.gbl...
for a SQL Server 2000 database?
Only limited by the maximum number of objects you can create in a =
database - which is huge (documented in BOL) under "maximum capacity =
specifications". If i remember right it is about 2 billion in theory =
but something else will get you first, so for sake of argument I would =
sy there is no practical limit.
I'm interested why this would be an issue?
Mike John
"Derek Ruesch" <anonymous@.discussions.microsoft.com> wrote in message =
news:31c901c470fa$1c952d90$a301280a@.phx.gbl...
> What is the maximum number of views that you can create=20
> for a SQL Server 2000 database?
|||Mike,
I have 31 views currently in my database and I plan on
adding another 20 views or so. I just wanted to make sure
that I could have this many views.
Derek
>--Original Message--
>Only limited by the maximum number of objects you can
create in a database - which is huge (documented in BOL)
under "maximum capacity specifications". If i remember
right it is about 2 billion in theory but something else
will get you first, so for sake of argument I would sy
there is no practical limit.
>I'm interested why this would be an issue?
>Mike John
>"Derek Ruesch" <anonymous@.discussions.microsoft.com>
wrote in message news:31c901c470fa$1c952d90
$a301280a@.phx.gbl...
>.
>
|||No problem whatsoever from a system limit point of view. I was a bit =
concerned you planned on creating the odd 10000 or so, which would have =
impled a very odd design!
Mike John
"Derek Ruesch" <anonymous@.discussions.microsoft.com> wrote in message =
news:321101c47101$615f7b90$a301280a@.phx.gbl...[vbcol=seagreen]
> Mike,
>=20
> I have 31 views currently in my database and I plan on=20
> adding another 20 views or so. I just wanted to make sure=20
> that I could have this many views.
>=20
> Derek
>=20
> create in a database - which is huge (documented in BOL)=20
> under "maximum capacity specifications". If i remember=20
> right it is about 2 billion in theory but something else=20
> will get you first, so for sake of argument I would sy=20
> there is no practical limit.
> wrote in message news:31c901c470fa$1c952d90
> $a301280a@.phx.gbl...
maximum number of views
What is the maximum number of views that you can create
for a SQL Server 2000 database?Only limited by the maximum number of objects you can create in a =database - which is huge (documented in BOL) under "maximum capacity =specifications". If i remember right it is about 2 billion in theory =but something else will get you first, so for sake of argument I would =sy there is no practical limit.
I'm interested why this would be an issue?
Mike John
"Derek Ruesch" <anonymous@.discussions.microsoft.com> wrote in message =news:31c901c470fa$1c952d90$a301280a@.phx.gbl...
> What is the maximum number of views that you can create > for a SQL Server 2000 database?|||Mike,
I have 31 views currently in my database and I plan on
adding another 20 views or so. I just wanted to make sure
that I could have this many views.
Derek
>--Original Message--
>Only limited by the maximum number of objects you can
create in a database - which is huge (documented in BOL)
under "maximum capacity specifications". If i remember
right it is about 2 billion in theory but something else
will get you first, so for sake of argument I would sy
there is no practical limit.
>I'm interested why this would be an issue?
>Mike John
>"Derek Ruesch" <anonymous@.discussions.microsoft.com>
wrote in message news:31c901c470fa$1c952d90
$a301280a@.phx.gbl...
>> What is the maximum number of views that you can create
>> for a SQL Server 2000 database?
>.
>|||No problem whatsoever from a system limit point of view. I was a bit =concerned you planned on creating the odd 10000 or so, which would have =impled a very odd design!
Mike John
"Derek Ruesch" <anonymous@.discussions.microsoft.com> wrote in message =news:321101c47101$615f7b90$a301280a@.phx.gbl...
> Mike,
> > I have 31 views currently in my database and I plan on > adding another 20 views or so. I just wanted to make sure > that I could have this many views.
> > Derek
> > >--Original Message--
> >Only limited by the maximum number of objects you can > create in a database - which is huge (documented in BOL) > under "maximum capacity specifications". If i remember > right it is about 2 billion in theory but something else > will get you first, so for sake of argument I would sy > there is no practical limit.
> >
> >I'm interested why this would be an issue?
> >
> >Mike John
> >
> >"Derek Ruesch" <anonymous@.discussions.microsoft.com> > wrote in message news:31c901c470fa$1c952d90
> $a301280a@.phx.gbl...
> >> What is the maximum number of views that you can create > >> for a SQL Server 2000 database?
> >.
> >
for a SQL Server 2000 database?Only limited by the maximum number of objects you can create in a =database - which is huge (documented in BOL) under "maximum capacity =specifications". If i remember right it is about 2 billion in theory =but something else will get you first, so for sake of argument I would =sy there is no practical limit.
I'm interested why this would be an issue?
Mike John
"Derek Ruesch" <anonymous@.discussions.microsoft.com> wrote in message =news:31c901c470fa$1c952d90$a301280a@.phx.gbl...
> What is the maximum number of views that you can create > for a SQL Server 2000 database?|||Mike,
I have 31 views currently in my database and I plan on
adding another 20 views or so. I just wanted to make sure
that I could have this many views.
Derek
>--Original Message--
>Only limited by the maximum number of objects you can
create in a database - which is huge (documented in BOL)
under "maximum capacity specifications". If i remember
right it is about 2 billion in theory but something else
will get you first, so for sake of argument I would sy
there is no practical limit.
>I'm interested why this would be an issue?
>Mike John
>"Derek Ruesch" <anonymous@.discussions.microsoft.com>
wrote in message news:31c901c470fa$1c952d90
$a301280a@.phx.gbl...
>> What is the maximum number of views that you can create
>> for a SQL Server 2000 database?
>.
>|||No problem whatsoever from a system limit point of view. I was a bit =concerned you planned on creating the odd 10000 or so, which would have =impled a very odd design!
Mike John
"Derek Ruesch" <anonymous@.discussions.microsoft.com> wrote in message =news:321101c47101$615f7b90$a301280a@.phx.gbl...
> Mike,
> > I have 31 views currently in my database and I plan on > adding another 20 views or so. I just wanted to make sure > that I could have this many views.
> > Derek
> > >--Original Message--
> >Only limited by the maximum number of objects you can > create in a database - which is huge (documented in BOL) > under "maximum capacity specifications". If i remember > right it is about 2 billion in theory but something else > will get you first, so for sake of argument I would sy > there is no practical limit.
> >
> >I'm interested why this would be an issue?
> >
> >Mike John
> >
> >"Derek Ruesch" <anonymous@.discussions.microsoft.com> > wrote in message news:31c901c470fa$1c952d90
> $a301280a@.phx.gbl...
> >> What is the maximum number of views that you can create > >> for a SQL Server 2000 database?
> >.
> >
maximum number of views
What is the maximum number of views that you can create
for a SQL Server 2000 database?Only limited by the maximum number of objects you can create in a =
database - which is huge (documented in BOL) under "maximum capacity =
specifications". If i remember right it is about 2 billion in theory =
but something else will get you first, so for sake of argument I would =
sy there is no practical limit.
I'm interested why this would be an issue?
Mike John
"Derek Ruesch" <anonymous@.discussions.microsoft.com> wrote in message =
news:31c901c470fa$1c952d90$a301280a@.phx.gbl...
> What is the maximum number of views that you can create=20
> for a SQL Server 2000 database?|||Mike,
I have 31 views currently in my database and I plan on
adding another 20 views or so. I just wanted to make sure
that I could have this many views.
Derek
>--Original Message--
>Only limited by the maximum number of objects you can
create in a database - which is huge (documented in BOL)
under "maximum capacity specifications". If i remember
right it is about 2 billion in theory but something else
will get you first, so for sake of argument I would sy
there is no practical limit.
>I'm interested why this would be an issue?
>Mike John
>"Derek Ruesch" <anonymous@.discussions.microsoft.com>
wrote in message news:31c901c470fa$1c952d90
$a301280a@.phx.gbl...
>.
>|||No problem whatsoever from a system limit point of view. I was a bit =
concerned you planned on creating the odd 10000 or so, which would have =
impled a very odd design!
Mike John
"Derek Ruesch" <anonymous@.discussions.microsoft.com> wrote in message =
news:321101c47101$615f7b90$a301280a@.phx.gbl...[vbcol=seagreen]
> Mike,
>=20
> I have 31 views currently in my database and I plan on=20
> adding another 20 views or so. I just wanted to make sure=20
> that I could have this many views.
>=20
> Derek
>=20
> create in a database - which is huge (documented in BOL)=20
> under "maximum capacity specifications". If i remember=20
> right it is about 2 billion in theory but something else=20
> will get you first, so for sake of argument I would sy=20
> there is no practical limit.
> wrote in message news:31c901c470fa$1c952d90
> $a301280a@.phx.gbl...
for a SQL Server 2000 database?Only limited by the maximum number of objects you can create in a =
database - which is huge (documented in BOL) under "maximum capacity =
specifications". If i remember right it is about 2 billion in theory =
but something else will get you first, so for sake of argument I would =
sy there is no practical limit.
I'm interested why this would be an issue?
Mike John
"Derek Ruesch" <anonymous@.discussions.microsoft.com> wrote in message =
news:31c901c470fa$1c952d90$a301280a@.phx.gbl...
> What is the maximum number of views that you can create=20
> for a SQL Server 2000 database?|||Mike,
I have 31 views currently in my database and I plan on
adding another 20 views or so. I just wanted to make sure
that I could have this many views.
Derek
>--Original Message--
>Only limited by the maximum number of objects you can
create in a database - which is huge (documented in BOL)
under "maximum capacity specifications". If i remember
right it is about 2 billion in theory but something else
will get you first, so for sake of argument I would sy
there is no practical limit.
>I'm interested why this would be an issue?
>Mike John
>"Derek Ruesch" <anonymous@.discussions.microsoft.com>
wrote in message news:31c901c470fa$1c952d90
$a301280a@.phx.gbl...
>.
>|||No problem whatsoever from a system limit point of view. I was a bit =
concerned you planned on creating the odd 10000 or so, which would have =
impled a very odd design!
Mike John
"Derek Ruesch" <anonymous@.discussions.microsoft.com> wrote in message =
news:321101c47101$615f7b90$a301280a@.phx.gbl...[vbcol=seagreen]
> Mike,
>=20
> I have 31 views currently in my database and I plan on=20
> adding another 20 views or so. I just wanted to make sure=20
> that I could have this many views.
>=20
> Derek
>=20
> create in a database - which is huge (documented in BOL)=20
> under "maximum capacity specifications". If i remember=20
> right it is about 2 billion in theory but something else=20
> will get you first, so for sake of argument I would sy=20
> there is no practical limit.
> wrote in message news:31c901c470fa$1c952d90
> $a301280a@.phx.gbl...
Maximum number of updates within a Transaction
Good day to all
SQL Server 2000.
I have a problem with a piece of code, which updates some tables using transaction. This process brings the program to a halt when updating large files.
With smaller files, the process finishes without problems.
I have noticed that, if a comment out the "begin transaction" and respective "commit", the same code executes without problems, even when updating large files.
I suspect that there is a limit on the number of records a transaction can hold before a commit is issued. I am surprised however, that SQL Server halts, without messages or warnings.
Is this a configuration issue? If there is a limit on the number of records a transaction can hold, what is this limit:? Anything I can do to have a warning form SQL Server when a situation like this is reached (or indeed to avoid this situation) ?
Thank you in advance for any help
Cecil Ricardo
hi cecil,
my guess is that the transaction is being involved in a recursive operation which means it triggers something and then it triggers back the transaction in a continous loop. thats why it halts your system
With out the begin transaction and commit transaction clause recursive operations end until 32 layers deep. Which cause your batch or sp to commit sucessfully
regards,
joey
|||
There are no limits to the number of rows you can commit in a transaction. It is bound by the size of your transaction log file(s). Did you check for any error messages in the errorlog? Note that the delay you may have observed was probably due to the commit operation itself. Depending on the number of changes you did within the transaction a commit/rollback can take a long time. And the semantics for using begin / commit for say 100 insert statements is different from executing 100 individual insert statements. So you cannot really compare the two approaches. By default, if you do not specify a begin/commit transaction the statement runs in auto-commit mode meaning each statement is a transaction by itself. Often, you will get better performance by using begin transaction/commit due to buffering of log writes. The batch size however depends on lot of factors. So do the following:
1. If you really need to use a transaction for updating a large table then ensure that you have enough space in your drives to account for log growth. Time for commit/rollback depends on the changes
2. If you just need to update a large number of rows then you can use a batch mechanims where you commit only say 1000 rows at a time and use a simple loop. You can use SET ROWCOUNT to restrict the rows affected by DML statements in older versions of SQL Server and use TOP clause in SQL Server 2005
3. Transactional consistency and semantics is different if you run a bunch of DML statements within a transaction vs running each DML statement separately. They are not the same so you need to be aware of the differences. See Books Online topics on auto-commit transactions and user specified transactions.
|||I have checked that. It is not the case.
But thank you for the valuable information about the end of recursive operations ending at 32 layers deep.
Thank you Joey
|||Thank you so much for the comprehensive explanation, Umachandar.
I have applyied your suggestion aof commiting smaller chunks of rows. It worked fine.
Maximum number of tables?
What is the maximum number of tables that SQL Server 2000 can cope with?Joe wrote:
> What is the maximum number of tables that SQL Server 2000 can cope with?
No specification for tables only, but it can contain 2,147,483,647
objects overall.|||From BOL:
"Database objects include all tables, views, stored procedures, extended
stored procedures, triggers, rules, defaults, and constraints. The sum of
the number of all these objects in a database cannot exceed 2,147,483,647."
... But you said, "cope with". That's a different question. The question
isn't how many tables--but what you're doing with those tables. I can bring
a big server to its knees with a single large, very badly designed table.
So coping is not about number of objects, but how they're used.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <joe@.hotmail.com> wrote in message
news:%23cEOzRcoFHA.3960@.TK2MSFTNGP12.phx.gbl...
> What is the maximum number of tables that SQL Server 2000 can cope with?
>
> What is the maximum number of tables that SQL Server 2000 can cope with?
No specification for tables only, but it can contain 2,147,483,647
objects overall.|||From BOL:
"Database objects include all tables, views, stored procedures, extended
stored procedures, triggers, rules, defaults, and constraints. The sum of
the number of all these objects in a database cannot exceed 2,147,483,647."
... But you said, "cope with". That's a different question. The question
isn't how many tables--but what you're doing with those tables. I can bring
a big server to its knees with a single large, very badly designed table.
So coping is not about number of objects, but how they're used.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <joe@.hotmail.com> wrote in message
news:%23cEOzRcoFHA.3960@.TK2MSFTNGP12.phx.gbl...
> What is the maximum number of tables that SQL Server 2000 can cope with?
>
Maximum number of tables?
What is the maximum number of tables that SQL Server 2000 can cope with?
Joe wrote:
> What is the maximum number of tables that SQL Server 2000 can cope with?
No specification for tables only, but it can contain 2,147,483,647
objects overall.
|||From BOL:
"Database objects include all tables, views, stored procedures, extended
stored procedures, triggers, rules, defaults, and constraints. The sum of
the number of all these objects in a database cannot exceed 2,147,483,647."
... But you said, "cope with". That's a different question. The question
isn't how many tables--but what you're doing with those tables. I can bring
a big server to its knees with a single large, very badly designed table.
So coping is not about number of objects, but how they're used.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Joe" <joe@.hotmail.com> wrote in message
news:%23cEOzRcoFHA.3960@.TK2MSFTNGP12.phx.gbl...
> What is the maximum number of tables that SQL Server 2000 can cope with?
>
Joe wrote:
> What is the maximum number of tables that SQL Server 2000 can cope with?
No specification for tables only, but it can contain 2,147,483,647
objects overall.
|||From BOL:
"Database objects include all tables, views, stored procedures, extended
stored procedures, triggers, rules, defaults, and constraints. The sum of
the number of all these objects in a database cannot exceed 2,147,483,647."
... But you said, "cope with". That's a different question. The question
isn't how many tables--but what you're doing with those tables. I can bring
a big server to its knees with a single large, very badly designed table.
So coping is not about number of objects, but how they're used.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Joe" <joe@.hotmail.com> wrote in message
news:%23cEOzRcoFHA.3960@.TK2MSFTNGP12.phx.gbl...
> What is the maximum number of tables that SQL Server 2000 can cope with?
>
Maximum number of tables?
What is the maximum number of tables that SQL Server 2000 can cope with?Joe wrote:
> What is the maximum number of tables that SQL Server 2000 can cope with?
No specification for tables only, but it can contain 2,147,483,647
objects overall.|||From BOL:
"Database objects include all tables, views, stored procedures, extended
stored procedures, triggers, rules, defaults, and constraints. The sum of
the number of all these objects in a database cannot exceed 2,147,483,647."
... But you said, "cope with". That's a different question. The question
isn't how many tables--but what you're doing with those tables. I can bring
a big server to its knees with a single large, very badly designed table.
So coping is not about number of objects, but how they're used.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <joe@.hotmail.com> wrote in message
news:%23cEOzRcoFHA.3960@.TK2MSFTNGP12.phx.gbl...
> What is the maximum number of tables that SQL Server 2000 can cope with?
>
> What is the maximum number of tables that SQL Server 2000 can cope with?
No specification for tables only, but it can contain 2,147,483,647
objects overall.|||From BOL:
"Database objects include all tables, views, stored procedures, extended
stored procedures, triggers, rules, defaults, and constraints. The sum of
the number of all these objects in a database cannot exceed 2,147,483,647."
... But you said, "cope with". That's a different question. The question
isn't how many tables--but what you're doing with those tables. I can bring
a big server to its knees with a single large, very badly designed table.
So coping is not about number of objects, but how they're used.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <joe@.hotmail.com> wrote in message
news:%23cEOzRcoFHA.3960@.TK2MSFTNGP12.phx.gbl...
> What is the maximum number of tables that SQL Server 2000 can cope with?
>
Maximum number of tables?
What is the maximum number of tables that SQL Server 2000 can cope with?Joe wrote:
> What is the maximum number of tables that SQL Server 2000 can cope with?
No specification for tables only, but it can contain 2,147,483,647
objects overall.|||From BOL:
"Database objects include all tables, views, stored procedures, extended
stored procedures, triggers, rules, defaults, and constraints. The sum of
the number of all these objects in a database cannot exceed 2,147,483,647."
... But you said, "cope with". That's a different question. The question
isn't how many tables--but what you're doing with those tables. I can bring
a big server to its knees with a single large, very badly designed table.
So coping is not about number of objects, but how they're used.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <joe@.hotmail.com> wrote in message
news:%23cEOzRcoFHA.3960@.TK2MSFTNGP12.phx.gbl...
> What is the maximum number of tables that SQL Server 2000 can cope with?
>
> What is the maximum number of tables that SQL Server 2000 can cope with?
No specification for tables only, but it can contain 2,147,483,647
objects overall.|||From BOL:
"Database objects include all tables, views, stored procedures, extended
stored procedures, triggers, rules, defaults, and constraints. The sum of
the number of all these objects in a database cannot exceed 2,147,483,647."
... But you said, "cope with". That's a different question. The question
isn't how many tables--but what you're doing with those tables. I can bring
a big server to its knees with a single large, very badly designed table.
So coping is not about number of objects, but how they're used.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <joe@.hotmail.com> wrote in message
news:%23cEOzRcoFHA.3960@.TK2MSFTNGP12.phx.gbl...
> What is the maximum number of tables that SQL Server 2000 can cope with?
>
maximum number of tables per select?
Does anyone know what is the maximum number of tables per select statement
in Oracle and SQL Server 2000?
Thanks a lot,
Lixin
I don't know it for Oracle, but for SQL Server 2000 it's 256.
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||I don't know it for Oracle, but for SQL Server 2000 it's 256.
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||SQL Server 2000: 256
For Oracle: you probably want to check in an Oracle group for that one.
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||SQL Server 2000: 256
For Oracle: you probably want to check in an Oracle group for that one.
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||That would be 256. Here are all the max capacities for SQL Server.
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||That would be 256. Here are all the max capacities for SQL Server.
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||Thanks all of you guys.
256 limitation caused me problem. I will post separately.
Lixin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eb%23SMoVXEHA.1656@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I don't know it for Oracle, but for SQL Server 2000 it's 256.
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
statement
>
|||Thanks all of you guys.
256 limitation caused me problem. I will post separately.
Lixin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eb%23SMoVXEHA.1656@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I don't know it for Oracle, but for SQL Server 2000 it's 256.
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
statement
>
in Oracle and SQL Server 2000?
Thanks a lot,
Lixin
I don't know it for Oracle, but for SQL Server 2000 it's 256.
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||I don't know it for Oracle, but for SQL Server 2000 it's 256.
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||SQL Server 2000: 256
For Oracle: you probably want to check in an Oracle group for that one.
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||SQL Server 2000: 256
For Oracle: you probably want to check in an Oracle group for that one.
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||That would be 256. Here are all the max capacities for SQL Server.
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||That would be 256. Here are all the max capacities for SQL Server.
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||Thanks all of you guys.
256 limitation caused me problem. I will post separately.
Lixin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eb%23SMoVXEHA.1656@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I don't know it for Oracle, but for SQL Server 2000 it's 256.
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
statement
>
|||Thanks all of you guys.
256 limitation caused me problem. I will post separately.
Lixin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eb%23SMoVXEHA.1656@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I don't know it for Oracle, but for SQL Server 2000 it's 256.
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
statement
>
Subscribe to:
Posts (Atom)