45M/day. What is the max size of a SQL database? And what is the
size beyond which the server performance will start to go down?dchow wrote:
>Our database size is currently 4G and is incrementing at a rate of
>45M/day. What is the max size of a SQL database? And what is the
>size beyond which the server performance will start to go down?
>
On what hardware?
From where I sit you've probably already passed that point you just
don't know it.
But until end-users start complaining I'd stay where you are while
getting ready to either
archive off what isn't required or move to Oracle, DB2, or Informix on a
UNIX platform.
--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
disk. Didn't have the server and CPU model with me.
On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
<damorgan@.x.washington.edu> wrote:
>dchow wrote:
>>Our database size is currently 4G and is incrementing at a rate of
>>45M/day. What is the max size of a SQL database? And what is the
>>size beyond which the server performance will start to go down?
>>
>>
>>
>On what hardware?
> From where I sit you've probably already passed that point you just
>don't know it.
>But until end-users start complaining I'd stay where you are while
>getting ready to either
>archive off what isn't required or move to Oracle, DB2, or Informix on a
>UNIX platform.|||dchow wrote:
>SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
>disk. Didn't have the server and CPU model with me.
>
You say 50G SCSI hard disk like it is a single disk? Not a good
situation with any RDBMS.
I'd say your are already runnnig far under what other RDBMS products
could do. But does
it matter? Not until end-users start to notice a difference or some
process starts taking longer
than its window allows.
--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||dchow <dchow@.hotmail.com> wrote in message news:<inerpv06qsnkscsnavmf249t71aqv7ge7v@.4ax.com>...
> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
> disk. Didn't have the server and CPU model with me.
>
> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
> <damorgan@.x.washington.edu> wrote:
> >dchow wrote:
> >>Our database size is currently 4G and is incrementing at a rate of
> >>45M/day. What is the max size of a SQL database? And what is the
> >>size beyond which the server performance will start to go down?
> >
<snip
Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
that most people will ever need to consider. In practice the
limitations are storage capacity and your ability to manage and
backup/restore the database. A 4GB database is not large, and 45MB per
day is a growth of about 16GB per year - 20GB is not particularly
large either.
It's not possible to say when performance will go down - it depends on
the load you place on the server. You can use Performance Monitor and
other tools to monitor CPU, disk access, memory use etc. to see if
there's a bottleneck somewhere. Having a single 50GB hard drive seems
rather limiting, if that's what you have - disk space is cheap, so
most people can afford to get extra disks and use RAID (or perhaps a
SAN/NAS) to improve performance by spreading the databases across
multiple disks.
In any case, discussing the size of a database or the hardware it runs
on usually isn't as important as how well it has been designed. If you
have a well designed database which is properly indexed and accessed
using well-written code, then it will perform and scale well up to
very large amounts of data. If you don't, then you can have
performance problems with even small amounts of data.
Simon|||"dchow" <dchow@.hotmail.com> wrote in message
news:mo6rpv4007jep364somgt2p1qrhf2kl02p@.4ax.com...
> Our database size is currently 4G and is incrementing at a rate of
> 45M/day. What is the max size of a SQL database? And what is the
> size beyond which the server performance will start to go down?
Max size is "huge" far larger than anything you'll encounter (heck, just
buying the disks to reach the max size will break most budgets).
That's not a large DB by any means.
What's more important is your hardware and indexes.
If you have fast drives (and keep your log files on a separate physical set
of drives) and good indices, things should be fairly fast.
Our largest DB is 78 GB and that's under SQL 7.0.
Our largest under SQL 2K is 37G and growing and still very fast.
|||"dchow" <dchow@.hotmail.com> wrote in message
news:mo6rpv4007jep364somgt2p1qrhf2kl02p@.4ax.com...
> Our database size is currently 4G and is incrementing at a rate of
> 45M/day. What is the max size of a SQL database? And what is the
> size beyond which the server performance will start to go down?
Max size is "huge" far larger than anything you'll encounter (heck, just
buying the disks to reach the max size will break most budgets).
That's not a large DB by any means.
What's more important is your hardware and indexes.
If you have fast drives (and keep your log files on a separate physical set
of drives) and good indices, things should be fairly fast.
Our largest DB is 78 GB and that's under SQL 7.0.
Our largest under SQL 2K is 37G and growing and still very fast.
|||Thanks Greg. That was what I hope to hear.
On Tue, 28 Oct 2003 15:43:02 GMT, "Greg D. Moore \(Strider\)"
<mooregr@.greenms.com> wrote:
>"dchow" <dchow@.hotmail.com> wrote in message
>news:mo6rpv4007jep364somgt2p1qrhf2kl02p@.4ax.com...
>> Our database size is currently 4G and is incrementing at a rate of
>> 45M/day. What is the max size of a SQL database? And what is the
>> size beyond which the server performance will start to go down?
>Max size is "huge" far larger than anything you'll encounter (heck, just
>buying the disks to reach the max size will break most budgets).
>That's not a large DB by any means.
>What's more important is your hardware and indexes.
>If you have fast drives (and keep your log files on a separate physical set
>of drives) and good indices, things should be fairly fast.
>Our largest DB is 78 GB and that's under SQL 7.0.
>Our largest under SQL 2K is 37G and growing and still very fast.
>
>|||Thanks Greg. That was what I hope to hear.
On Tue, 28 Oct 2003 15:43:02 GMT, "Greg D. Moore \(Strider\)"
<mooregr@.greenms.com> wrote:
>"dchow" <dchow@.hotmail.com> wrote in message
>news:mo6rpv4007jep364somgt2p1qrhf2kl02p@.4ax.com...
>> Our database size is currently 4G and is incrementing at a rate of
>> 45M/day. What is the max size of a SQL database? And what is the
>> size beyond which the server performance will start to go down?
>Max size is "huge" far larger than anything you'll encounter (heck, just
>buying the disks to reach the max size will break most budgets).
>That's not a large DB by any means.
>What's more important is your hardware and indexes.
>If you have fast drives (and keep your log files on a separate physical set
>of drives) and good indices, things should be fairly fast.
>Our largest DB is 78 GB and that's under SQL 7.0.
>Our largest under SQL 2K is 37G and growing and still very fast.
>
>|||Thanks Simon. If fact we have RAID. But because I am not a network
admin guy, I didn't know too much about it. All I know was that I have
50G on the data partition. Having learned that 45MB growth per day is
not particularly large made me more comfortable.
On 28 Oct 2003 01:01:20 -0800, sql@.hayes.ch (Simon Hayes) wrote:
>dchow <dchow@.hotmail.com> wrote in message news:<inerpv06qsnkscsnavmf249t71aqv7ge7v@.4ax.com>...
>> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
>> disk. Didn't have the server and CPU model with me.
>>
>>
>> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
>> <damorgan@.x.washington.edu> wrote:
>>
>> >dchow wrote:
>>> >>Our database size is currently 4G and is incrementing at a rate of
>> >>45M/day. What is the max size of a SQL database? And what is the
>> >>size beyond which the server performance will start to go down?
>> >>
><snip>
>Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
>that most people will ever need to consider. In practice the
>limitations are storage capacity and your ability to manage and
>backup/restore the database. A 4GB database is not large, and 45MB per
>day is a growth of about 16GB per year - 20GB is not particularly
>large either.
>It's not possible to say when performance will go down - it depends on
>the load you place on the server. You can use Performance Monitor and
>other tools to monitor CPU, disk access, memory use etc. to see if
>there's a bottleneck somewhere. Having a single 50GB hard drive seems
>rather limiting, if that's what you have - disk space is cheap, so
>most people can afford to get extra disks and use RAID (or perhaps a
>SAN/NAS) to improve performance by spreading the databases across
>multiple disks.
>In any case, discussing the size of a database or the hardware it runs
>on usually isn't as important as how well it has been designed. If you
>have a well designed database which is properly indexed and accessed
>using well-written code, then it will perform and scale well up to
>very large amounts of data. If you don't, then you can have
>performance problems with even small amounts of data.
>Simon|||Thanks Simon. If fact we have RAID. But because I am not a network
admin guy, I didn't know too much about it. All I know was that I have
50G on the data partition. Having learned that 45MB growth per day is
not particularly large made me more comfortable.
On 28 Oct 2003 01:01:20 -0800, sql@.hayes.ch (Simon Hayes) wrote:
>dchow <dchow@.hotmail.com> wrote in message news:<inerpv06qsnkscsnavmf249t71aqv7ge7v@.4ax.com>...
>> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
>> disk. Didn't have the server and CPU model with me.
>>
>>
>> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
>> <damorgan@.x.washington.edu> wrote:
>>
>> >dchow wrote:
>>> >>Our database size is currently 4G and is incrementing at a rate of
>> >>45M/day. What is the max size of a SQL database? And what is the
>> >>size beyond which the server performance will start to go down?
>> >>
><snip>
>Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
>that most people will ever need to consider. In practice the
>limitations are storage capacity and your ability to manage and
>backup/restore the database. A 4GB database is not large, and 45MB per
>day is a growth of about 16GB per year - 20GB is not particularly
>large either.
>It's not possible to say when performance will go down - it depends on
>the load you place on the server. You can use Performance Monitor and
>other tools to monitor CPU, disk access, memory use etc. to see if
>there's a bottleneck somewhere. Having a single 50GB hard drive seems
>rather limiting, if that's what you have - disk space is cheap, so
>most people can afford to get extra disks and use RAID (or perhaps a
>SAN/NAS) to improve performance by spreading the databases across
>multiple disks.
>In any case, discussing the size of a database or the hardware it runs
>on usually isn't as important as how well it has been designed. If you
>have a well designed database which is properly indexed and accessed
>using well-written code, then it will perform and scale well up to
>very large amounts of data. If you don't, then you can have
>performance problems with even small amounts of data.
>Simon|||Hi
I don't think anyone has mentioned that you should have a maintenance plan
(or equivalent jobs) that backups up the database, checks integrity, and
shrinks the files.
John
If this is in place you should have the ability to recover in case of
disaster and
"dchow" <dchow@.hotmail.com> wrote in message
news:bfjtpvsqke2rc56chrvdvl8i0c8jcpeccp@.4ax.com...
> Thanks Simon. If fact we have RAID. But because I am not a network
> admin guy, I didn't know too much about it. All I know was that I have
> 50G on the data partition. Having learned that 45MB growth per day is
> not particularly large made me more comfortable.
>
> On 28 Oct 2003 01:01:20 -0800, sql@.hayes.ch (Simon Hayes) wrote:
> >dchow <dchow@.hotmail.com> wrote in message
news:<inerpv06qsnkscsnavmf249t71aqv7ge7v@.4ax.com>...
> >> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
> >> disk. Didn't have the server and CPU model with me.
> >>
> >>
> >> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
> >> <damorgan@.x.washington.edu> wrote:
> >>
> >> >dchow wrote:
> >> >> >>Our database size is currently 4G and is incrementing at a rate of
> >> >>45M/day. What is the max size of a SQL database? And what is the
> >> >>size beyond which the server performance will start to go down?
> >> >>
> ><snip>
> >Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
> >that most people will ever need to consider. In practice the
> >limitations are storage capacity and your ability to manage and
> >backup/restore the database. A 4GB database is not large, and 45MB per
> >day is a growth of about 16GB per year - 20GB is not particularly
> >large either.
> >It's not possible to say when performance will go down - it depends on
> >the load you place on the server. You can use Performance Monitor and
> >other tools to monitor CPU, disk access, memory use etc. to see if
> >there's a bottleneck somewhere. Having a single 50GB hard drive seems
> >rather limiting, if that's what you have - disk space is cheap, so
> >most people can afford to get extra disks and use RAID (or perhaps a
> >SAN/NAS) to improve performance by spreading the databases across
> >multiple disks.
> >In any case, discussing the size of a database or the hardware it runs
> >on usually isn't as important as how well it has been designed. If you
> >have a well designed database which is properly indexed and accessed
> >using well-written code, then it will perform and scale well up to
> >very large amounts of data. If you don't, then you can have
> >performance problems with even small amounts of data.
> >Simon|||Hi
I don't think anyone has mentioned that you should have a maintenance plan
(or equivalent jobs) that backups up the database, checks integrity, and
shrinks the files.
John
If this is in place you should have the ability to recover in case of
disaster and
"dchow" <dchow@.hotmail.com> wrote in message
news:bfjtpvsqke2rc56chrvdvl8i0c8jcpeccp@.4ax.com...
> Thanks Simon. If fact we have RAID. But because I am not a network
> admin guy, I didn't know too much about it. All I know was that I have
> 50G on the data partition. Having learned that 45MB growth per day is
> not particularly large made me more comfortable.
>
> On 28 Oct 2003 01:01:20 -0800, sql@.hayes.ch (Simon Hayes) wrote:
> >dchow <dchow@.hotmail.com> wrote in message
news:<inerpv06qsnkscsnavmf249t71aqv7ge7v@.4ax.com>...
> >> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
> >> disk. Didn't have the server and CPU model with me.
> >>
> >>
> >> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
> >> <damorgan@.x.washington.edu> wrote:
> >>
> >> >dchow wrote:
> >> >> >>Our database size is currently 4G and is incrementing at a rate of
> >> >>45M/day. What is the max size of a SQL database? And what is the
> >> >>size beyond which the server performance will start to go down?
> >> >>
> ><snip>
> >Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
> >that most people will ever need to consider. In practice the
> >limitations are storage capacity and your ability to manage and
> >backup/restore the database. A 4GB database is not large, and 45MB per
> >day is a growth of about 16GB per year - 20GB is not particularly
> >large either.
> >It's not possible to say when performance will go down - it depends on
> >the load you place on the server. You can use Performance Monitor and
> >other tools to monitor CPU, disk access, memory use etc. to see if
> >there's a bottleneck somewhere. Having a single 50GB hard drive seems
> >rather limiting, if that's what you have - disk space is cheap, so
> >most people can afford to get extra disks and use RAID (or perhaps a
> >SAN/NAS) to improve performance by spreading the databases across
> >multiple disks.
> >In any case, discussing the size of a database or the hardware it runs
> >on usually isn't as important as how well it has been designed. If you
> >have a well designed database which is properly indexed and accessed
> >using well-written code, then it will perform and scale well up to
> >very large amounts of data. If you don't, then you can have
> >performance problems with even small amounts of data.
> >Simon|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f9f8a79$0$9469$ed9e5944@.reading.news.pipex.n et...
> Hi
> I don't think anyone has mentioned that you should have a maintenance plan
> (or equivalent jobs) that backups up the database, checks integrity, and
> shrinks the files.
I personally do not favor shrinking the file. It adds overhead and as it's
just likely to grow again, there's usually not much point.
> John
> If this is in place you should have the ability to recover in case of
> disaster and
> "dchow" <dchow@.hotmail.com> wrote in message
> news:bfjtpvsqke2rc56chrvdvl8i0c8jcpeccp@.4ax.com...
> > Thanks Simon. If fact we have RAID. But because I am not a network
> > admin guy, I didn't know too much about it. All I know was that I have
> > 50G on the data partition. Having learned that 45MB growth per day is
> > not particularly large made me more comfortable.
> > On 28 Oct 2003 01:01:20 -0800, sql@.hayes.ch (Simon Hayes) wrote:
> > >dchow <dchow@.hotmail.com> wrote in message
> news:<inerpv06qsnkscsnavmf249t71aqv7ge7v@.4ax.com>...
> > >> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
> > >> disk. Didn't have the server and CPU model with me.
> > >>
> > >>
> > >> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
> > >> <damorgan@.x.washington.edu> wrote:
> > >>
> > >> >dchow wrote:
> > >> > >> >>Our database size is currently 4G and is incrementing at a rate of
> > >> >>45M/day. What is the max size of a SQL database? And what is the
> > >> >>size beyond which the server performance will start to go down?
> > >> >>
> > > ><snip>
> > > >Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
> > >that most people will ever need to consider. In practice the
> > >limitations are storage capacity and your ability to manage and
> > >backup/restore the database. A 4GB database is not large, and 45MB per
> > >day is a growth of about 16GB per year - 20GB is not particularly
> > >large either.
> > > >It's not possible to say when performance will go down - it depends on
> > >the load you place on the server. You can use Performance Monitor and
> > >other tools to monitor CPU, disk access, memory use etc. to see if
> > >there's a bottleneck somewhere. Having a single 50GB hard drive seems
> > >rather limiting, if that's what you have - disk space is cheap, so
> > >most people can afford to get extra disks and use RAID (or perhaps a
> > >SAN/NAS) to improve performance by spreading the databases across
> > >multiple disks.
> > > >In any case, discussing the size of a database or the hardware it runs
> > >on usually isn't as important as how well it has been designed. If you
> > >have a well designed database which is properly indexed and accessed
> > >using well-written code, then it will perform and scale well up to
> > >very large amounts of data. If you don't, then you can have
> > >performance problems with even small amounts of data.
> > > >Simon|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f9f8a79$0$9469$ed9e5944@.reading.news.pipex.n et...
> Hi
> I don't think anyone has mentioned that you should have a maintenance plan
> (or equivalent jobs) that backups up the database, checks integrity, and
> shrinks the files.
I personally do not favor shrinking the file. It adds overhead and as it's
just likely to grow again, there's usually not much point.
> John
> If this is in place you should have the ability to recover in case of
> disaster and
> "dchow" <dchow@.hotmail.com> wrote in message
> news:bfjtpvsqke2rc56chrvdvl8i0c8jcpeccp@.4ax.com...
> > Thanks Simon. If fact we have RAID. But because I am not a network
> > admin guy, I didn't know too much about it. All I know was that I have
> > 50G on the data partition. Having learned that 45MB growth per day is
> > not particularly large made me more comfortable.
> > On 28 Oct 2003 01:01:20 -0800, sql@.hayes.ch (Simon Hayes) wrote:
> > >dchow <dchow@.hotmail.com> wrote in message
> news:<inerpv06qsnkscsnavmf249t71aqv7ge7v@.4ax.com>...
> > >> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
> > >> disk. Didn't have the server and CPU model with me.
> > >>
> > >>
> > >> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
> > >> <damorgan@.x.washington.edu> wrote:
> > >>
> > >> >dchow wrote:
> > >> > >> >>Our database size is currently 4G and is incrementing at a rate of
> > >> >>45M/day. What is the max size of a SQL database? And what is the
> > >> >>size beyond which the server performance will start to go down?
> > >> >>
> > > ><snip>
> > > >Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
> > >that most people will ever need to consider. In practice the
> > >limitations are storage capacity and your ability to manage and
> > >backup/restore the database. A 4GB database is not large, and 45MB per
> > >day is a growth of about 16GB per year - 20GB is not particularly
> > >large either.
> > > >It's not possible to say when performance will go down - it depends on
> > >the load you place on the server. You can use Performance Monitor and
> > >other tools to monitor CPU, disk access, memory use etc. to see if
> > >there's a bottleneck somewhere. Having a single 50GB hard drive seems
> > >rather limiting, if that's what you have - disk space is cheap, so
> > >most people can afford to get extra disks and use RAID (or perhaps a
> > >SAN/NAS) to improve performance by spreading the databases across
> > >multiple disks.
> > > >In any case, discussing the size of a database or the hardware it runs
> > >on usually isn't as important as how well it has been designed. If you
> > >have a well designed database which is properly indexed and accessed
> > >using well-written code, then it will perform and scale well up to
> > >very large amounts of data. If you don't, then you can have
> > >performance problems with even small amounts of data.
> > > >Simon|||"Greg D. Moore \(Strider\)" <mooregr@.greenms.com> wrote in message news:<iWXnb.9515$ZC4.5820@.twister.nyroc.rr.com>...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3f9f8a79$0$9469$ed9e5944@.reading.news.pipex.n et...
> > Hi
> > I don't think anyone has mentioned that you should have a maintenance plan
> > (or equivalent jobs) that backups up the database, checks integrity, and
> > shrinks the files.
> I personally do not favor shrinking the file. It adds overhead and as it's
> just likely to grow again, there's usually not much point.
>
Hi Greg
It would depend on the circumstances, and data files and log files
would have different criteria. I would say that until the OP is more
experienced the more cautious approach of shrinking the file is the
better approach (IMO). Once he has built up a monitoring mechanism, he
would not have to be a blind approach.
John|||We do backup nightly and perform integrity check as well as shrinking
the files weekly.
Does shrinking the database increase overhead like Greg said? I
thought shrinking the database is similar to compacting database in
Access. Is there a compacting process in SQL server?
On Wed, 29 Oct 2003 09:38:01 -0000, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:
>Hi
>I don't think anyone has mentioned that you should have a maintenance plan
>(or equivalent jobs) that backups up the database, checks integrity, and
>shrinks the files.
>John
>If this is in place you should have the ability to recover in case of
>disaster and
>"dchow" <dchow@.hotmail.com> wrote in message
>news:bfjtpvsqke2rc56chrvdvl8i0c8jcpeccp@.4ax.com...
>> Thanks Simon. If fact we have RAID. But because I am not a network
>> admin guy, I didn't know too much about it. All I know was that I have
>> 50G on the data partition. Having learned that 45MB growth per day is
>> not particularly large made me more comfortable.
>>
>>
>> On 28 Oct 2003 01:01:20 -0800, sql@.hayes.ch (Simon Hayes) wrote:
>>
>> >dchow <dchow@.hotmail.com> wrote in message
>news:<inerpv06qsnkscsnavmf249t71aqv7ge7v@.4ax.com>...
>> >> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI hard
>> >> disk. Didn't have the server and CPU model with me.
>> >>
>> >>
>> >> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
>> >> <damorgan@.x.washington.edu> wrote:
>> >>
>> >> >dchow wrote:
>> >>> >> >>Our database size is currently 4G and is incrementing at a rate of
>> >> >>45M/day. What is the max size of a SQL database? And what is the
>> >> >>size beyond which the server performance will start to go down?
>> >> >>
>>> ><snip>
>>> >Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
>> >that most people will ever need to consider. In practice the
>> >limitations are storage capacity and your ability to manage and
>> >backup/restore the database. A 4GB database is not large, and 45MB per
>> >day is a growth of about 16GB per year - 20GB is not particularly
>> >large either.
>>> >It's not possible to say when performance will go down - it depends on
>> >the load you place on the server. You can use Performance Monitor and
>> >other tools to monitor CPU, disk access, memory use etc. to see if
>> >there's a bottleneck somewhere. Having a single 50GB hard drive seems
>> >rather limiting, if that's what you have - disk space is cheap, so
>> >most people can afford to get extra disks and use RAID (or perhaps a
>> >SAN/NAS) to improve performance by spreading the databases across
>> >multiple disks.
>>> >In any case, discussing the size of a database or the hardware it runs
>> >on usually isn't as important as how well it has been designed. If you
>> >have a well designed database which is properly indexed and accessed
>> >using well-written code, then it will perform and scale well up to
>> >very large amounts of data. If you don't, then you can have
>> >performance problems with even small amounts of data.
>>> >Simon
>|||"dchow" <dchow@.hotmail.com> wrote in message
news:fghgqvolcc28i5jbvqs039vpc0o567d9tj@.4ax.com...
> We do backup nightly and perform integrity check as well as shrinking
> the files weekly.
> Does shrinking the database increase overhead like Greg said? I
> thought shrinking the database is similar to compacting database in
> Access. Is there a compacting process in SQL server?
No, shrinking the database file merely shrinks the database file.
And this can be a problem for the following reason:
(btw, generally folks shrink the transaction log since that grows and
shrinks a lot).
If you have say two databases on a server:
Both start out with a 25 MB transaction log and grow in 25 MB increments.
(and nightly you shrink it back to 25 MB).
You start out with 200 MB of free disk space, all in one big block.
Log for D1 exceeds 25 MB and grows to 50MB
Ideally this is contiguous space, let's assume it is.
The log for D1 exceeds 50 MB and grows to 75 MB.
This repeats until it reaches 175 MB. So far once nice big contiguous log
file.
Now, D2 exceeds its log of 25 MB and grows. Where's this chunk going to
grow into? The remaining 25 MB of free space. Ok, not, a major problem,
but you've just decreased the likelihood that that space was contiguous with
D2's original log file.
Now, you shrink D1's log back to 25 MB after backing it up.
Meanwhile D2's log grows again. So it's not allocated in the free space
previous used by part of D1's log file. This we already know is not
contiguous to D2's 1st growth. So now D2's log file is spread across 3
separate blocks on the disk.
Now D1's log starts to grow again. It grows into the now free space.
Again, this ends up not being contiguous.
Rinse, lather repeat.
Now, this is a rather derived example and in reality there are things that
will help (the fact that SQL Server will be using virtual logs within your
log file space, you're going to do a lot of linear reads so it'll read all
from one block before moving on, etc.) But, keep in mind that while the
server is expanding the log file, any pending transactions are held up until
the space is allocated since obviously SQL Server can't write them to disk
if the current space is filled.
But, over all, the end effect is you end up with a fragmented log file which
can affect performance.
Is this is a huge problem? Probably not.
But in general you should try to configure your logfiles so they don't need
to expand and shrink.
Do I practice what I preach? Of course not. :-) I have one server that
does nightly rollups. The logfile can expand by a couple of gig overnight
and then shrink again.
Unfortunately, due to lack of enough disk space on this server, I have
another process that runs at a different time that also needs disk space.
So in this case I do compact the transaction log. I'd much rather not
though.
BTW, due to the way SQL Server operates, there's really no exact equivalent
to ACCESS's compaction. However, updating Stats is generally a good idea if
your data changes a lot. And index defrag from time to time might help, but
I don't do that myself. (Though looking into doing it on a few dbs we have.)
Hope that helps.
>
> On Wed, 29 Oct 2003 09:38:01 -0000, "John Bell"
> <jbellnewsposts@.hotmail.com> wrote:
> >Hi
> >I don't think anyone has mentioned that you should have a maintenance
plan
> >(or equivalent jobs) that backups up the database, checks integrity, and
> >shrinks the files.
> >John
> >If this is in place you should have the ability to recover in case of
> >disaster and
> >"dchow" <dchow@.hotmail.com> wrote in message
> >news:bfjtpvsqke2rc56chrvdvl8i0c8jcpeccp@.4ax.com...
> >> Thanks Simon. If fact we have RAID. But because I am not a network
> >> admin guy, I didn't know too much about it. All I know was that I have
> >> 50G on the data partition. Having learned that 45MB growth per day is
> >> not particularly large made me more comfortable.
> >>
> >>
> >> On 28 Oct 2003 01:01:20 -0800, sql@.hayes.ch (Simon Hayes) wrote:
> >>
> >> >dchow <dchow@.hotmail.com> wrote in message
> >news:<inerpv06qsnkscsnavmf249t71aqv7ge7v@.4ax.com>...
> >> >> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI
hard
> >> >> disk. Didn't have the server and CPU model with me.
> >> >>
> >> >>
> >> >> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
> >> >> <damorgan@.x.washington.edu> wrote:
> >> >>
> >> >> >dchow wrote:
> >> >> >> >> >>Our database size is currently 4G and is incrementing at a rate of
> >> >> >>45M/day. What is the max size of a SQL database? And what is the
> >> >> >>size beyond which the server performance will start to go down?
> >> >> >>
> >> >> ><snip>
> >> >> >Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
> >> >that most people will ever need to consider. In practice the
> >> >limitations are storage capacity and your ability to manage and
> >> >backup/restore the database. A 4GB database is not large, and 45MB per
> >> >day is a growth of about 16GB per year - 20GB is not particularly
> >> >large either.
> >> >> >It's not possible to say when performance will go down - it depends on
> >> >the load you place on the server. You can use Performance Monitor and
> >> >other tools to monitor CPU, disk access, memory use etc. to see if
> >> >there's a bottleneck somewhere. Having a single 50GB hard drive seems
> >> >rather limiting, if that's what you have - disk space is cheap, so
> >> >most people can afford to get extra disks and use RAID (or perhaps a
> >> >SAN/NAS) to improve performance by spreading the databases across
> >> >multiple disks.
> >> >> >In any case, discussing the size of a database or the hardware it runs
> >> >on usually isn't as important as how well it has been designed. If you
> >> >have a well designed database which is properly indexed and accessed
> >> >using well-written code, then it will perform and scale well up to
> >> >very large amounts of data. If you don't, then you can have
> >> >performance problems with even small amounts of data.
> >> >> >Simon
> >|||There is no such concept... u need to have sufficient hard drive
space... SQL 2000 can handle any size...
Even in TB
Keyur Shah
Verizon Communications
732-423-0745
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Greg, thanks for the detailed explanation. As I am not a network
adminstrator, I might not know something that a network admin should.
Appreciate if you could tell me how to:
- Configure the logfiles so they don't need to expand and shrink.
- Update Stats.
- Index defrag.
And, do you actually recommend doing shrinking?
Thanks
David
On Wed, 05 Nov 2003 04:16:38 GMT, "Greg D. Moore \(Strider\)"
<mooregr@.greenms.com> wrote:
>"dchow" <dchow@.hotmail.com> wrote in message
>news:fghgqvolcc28i5jbvqs039vpc0o567d9tj@.4ax.com...
>> We do backup nightly and perform integrity check as well as shrinking
>> the files weekly.
>> Does shrinking the database increase overhead like Greg said? I
>> thought shrinking the database is similar to compacting database in
>> Access. Is there a compacting process in SQL server?
>No, shrinking the database file merely shrinks the database file.
>And this can be a problem for the following reason:
>(btw, generally folks shrink the transaction log since that grows and
>shrinks a lot).
>If you have say two databases on a server:
>Both start out with a 25 MB transaction log and grow in 25 MB increments.
>(and nightly you shrink it back to 25 MB).
>You start out with 200 MB of free disk space, all in one big block.
>Log for D1 exceeds 25 MB and grows to 50MB
>Ideally this is contiguous space, let's assume it is.
>The log for D1 exceeds 50 MB and grows to 75 MB.
>This repeats until it reaches 175 MB. So far once nice big contiguous log
>file.
>Now, D2 exceeds its log of 25 MB and grows. Where's this chunk going to
>grow into? The remaining 25 MB of free space. Ok, not, a major problem,
>but you've just decreased the likelihood that that space was contiguous with
>D2's original log file.
>Now, you shrink D1's log back to 25 MB after backing it up.
>Meanwhile D2's log grows again. So it's not allocated in the free space
>previous used by part of D1's log file. This we already know is not
>contiguous to D2's 1st growth. So now D2's log file is spread across 3
>separate blocks on the disk.
>
>Now D1's log starts to grow again. It grows into the now free space.
>Again, this ends up not being contiguous.
>Rinse, lather repeat.
>Now, this is a rather derived example and in reality there are things that
>will help (the fact that SQL Server will be using virtual logs within your
>log file space, you're going to do a lot of linear reads so it'll read all
>from one block before moving on, etc.) But, keep in mind that while the
>server is expanding the log file, any pending transactions are held up until
>the space is allocated since obviously SQL Server can't write them to disk
>if the current space is filled.
>But, over all, the end effect is you end up with a fragmented log file which
>can affect performance.
>Is this is a huge problem? Probably not.
>But in general you should try to configure your logfiles so they don't need
>to expand and shrink.
>Do I practice what I preach? Of course not. :-) I have one server that
>does nightly rollups. The logfile can expand by a couple of gig overnight
>and then shrink again.
>Unfortunately, due to lack of enough disk space on this server, I have
>another process that runs at a different time that also needs disk space.
>So in this case I do compact the transaction log. I'd much rather not
>though.
>
>BTW, due to the way SQL Server operates, there's really no exact equivalent
>to ACCESS's compaction. However, updating Stats is generally a good idea if
>your data changes a lot. And index defrag from time to time might help, but
>I don't do that myself. (Though looking into doing it on a few dbs we have.)
>Hope that helps.
>
>>
>>
>>
>> On Wed, 29 Oct 2003 09:38:01 -0000, "John Bell"
>> <jbellnewsposts@.hotmail.com> wrote:
>>
>> >Hi
>>> >I don't think anyone has mentioned that you should have a maintenance
>plan
>> >(or equivalent jobs) that backups up the database, checks integrity, and
>> >shrinks the files.
>>> >John
>>> >If this is in place you should have the ability to recover in case of
>> >disaster and
>> >"dchow" <dchow@.hotmail.com> wrote in message
>> >news:bfjtpvsqke2rc56chrvdvl8i0c8jcpeccp@.4ax.com...
>> >> Thanks Simon. If fact we have RAID. But because I am not a network
>> >> admin guy, I didn't know too much about it. All I know was that I have
>> >> 50G on the data partition. Having learned that 45MB growth per day is
>> >> not particularly large made me more comfortable.
>> >>
>> >>
>> >> On 28 Oct 2003 01:01:20 -0800, sql@.hayes.ch (Simon Hayes) wrote:
>> >>
>> >> >dchow <dchow@.hotmail.com> wrote in message
>> >news:<inerpv06qsnkscsnavmf249t71aqv7ge7v@.4ax.com>...
>> >> >> SQL server 2000 on IBM server with quad CPU, 4G memory, 50G SCSI
>hard
>> >> >> disk. Didn't have the server and CPU model with me.
>> >> >>
>> >> >>
>> >> >> On Mon, 27 Oct 2003 15:41:17 -0800, Daniel Morgan
>> >> >> <damorgan@.x.washington.edu> wrote:
>> >> >>
>> >> >> >dchow wrote:
>> >> >>> >> >> >>Our database size is currently 4G and is incrementing at a rate of
>> >> >> >>45M/day. What is the max size of a SQL database? And what is the
>> >> >> >>size beyond which the server performance will start to go down?
>> >> >> >>
>> >>> >> ><snip>
>> >>> >> >Maximum DB size in MSSQL is over 1,000,000TB, so it's not something
>> >> >that most people will ever need to consider. In practice the
>> >> >limitations are storage capacity and your ability to manage and
>> >> >backup/restore the database. A 4GB database is not large, and 45MB per
>> >> >day is a growth of about 16GB per year - 20GB is not particularly
>> >> >large either.
>> >>> >> >It's not possible to say when performance will go down - it depends on
>> >> >the load you place on the server. You can use Performance Monitor and
>> >> >other tools to monitor CPU, disk access, memory use etc. to see if
>> >> >there's a bottleneck somewhere. Having a single 50GB hard drive seems
>> >> >rather limiting, if that's what you have - disk space is cheap, so
>> >> >most people can afford to get extra disks and use RAID (or perhaps a
>> >> >SAN/NAS) to improve performance by spreading the databases across
>> >> >multiple disks.
>> >>> >> >In any case, discussing the size of a database or the hardware it runs
>> >> >on usually isn't as important as how well it has been designed. If you
>> >> >have a well designed database which is properly indexed and accessed
>> >> >using well-written code, then it will perform and scale well up to
>> >> >very large amounts of data. If you don't, then you can have
>> >> >performance problems with even small amounts of data.
>> >>> >> >Simon
>> >>
>>
No comments:
Post a Comment