Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Computer Certification > MCSD > SQL question

Reply
Thread Tools

SQL question

 
 
Session
Guest
Posts: n/a
 
      05-03-2004
I got a question on the exam about what the best way to
create a database would be. The two answers I hesitated
on were:

1- set the filegrowth to a static value, such as 5mb
2- set teh filegrowth to a percentage.

Can someone enlighten me on the efficiency aspect of any
of these. Thanks

Session


 
Reply With Quote
 
 
 
 
Cowboy \(Gregory A. Beamer\)
Guest
Posts: n/a
 
      05-03-2004
Without knowing the specifics, here is the why

Setting a static value works best when you know certain activities are going
to take place that consume a specific amount of space. It is also good when
you want a predictable growth pattern X MB versus a sliding scale
(percentage), which is good for getting an alert before a drive is full, for
example. It is more process driven (nightly import) than user base driven.

Percentage is opposite and great, for example, when a user base keeps
growing. It is overkill, over time, when you are driving growth for a
particular process.

This is oversimplified, but it works.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"Session" <(E-Mail Removed)> wrote in message
news:79cd01c4313a$90827730$(E-Mail Removed)...
> I got a question on the exam about what the best way to
> create a database would be. The two answers I hesitated
> on were:
>
> 1- set the filegrowth to a static value, such as 5mb
> 2- set teh filegrowth to a percentage.
>
> Can someone enlighten me on the efficiency aspect of any
> of these. Thanks
>
> Session
>
>



 
Reply With Quote
 
 
 
 
Imayavaramban R
Guest
Posts: n/a
 
      05-03-2004
Hi,

The answer to your query would actually depend on the size of the database
and the pace with which it would grow.

If the database is growing automatically often (daily or multiple times a
week), it is better that we change the growth percentage to a larger
number, such as 25% or 30%. Each time the database has to be increased, SQL
Server will suffer a small performance hit. By increasing the amount the
database grows each time, the less often it will have to grow.

But if the database is very large, 10GB or larger, it is better to use a
fixed growth amount (static) instead of a percentage growth amount. This
is because a percentage growth amount can be large on a large database. For
example, a 10% growth rate on a 10GB database means that when the database
grows, it will increase by 1GB. This may or may not be what you want. For
example, a fixed growth rate, such as 100MB at a time, might be more
appropriate.

Please check under 'Best Practices' in the following page :
http://support.microsoft.com/default...&Product=sql2k

Thanks and Regards,
(Imay) Imayavaramban R

This posting is provided 'AS IS' with no warranties, and confers no rights.
 
Reply With Quote
 
=?Utf-8?B?c2F1cmFiaGRvdG5ldA==?=
Guest
Posts: n/a
 
      05-03-2004
Hi ! Dear,

If you have a database, in which you editing/adding of records is frequent. Use Static Growth.

But if you feel, you will be adding/editing more frequently. Just set the file growth to Percentage.


"Session" wrote:

> I got a question on the exam about what the best way to
> create a database would be. The two answers I hesitated
> on were:
>
> 1- set the filegrowth to a static value, such as 5mb
> 2- set teh filegrowth to a percentage.
>
> Can someone enlighten me on the efficiency aspect of any
> of these. Thanks
>
> Session
>
>
>

 
Reply With Quote
 
The Poster Formerly Known as Kline Sphere
Guest
Posts: n/a
 
      05-04-2004
Again NDA comes to mind.

On Mon, 3 May 2004 11:15:07 -0700, "Session"
<(E-Mail Removed)> wrote:

>I got a question on the exam about what the best way to
>create a database would be. The two answers I hesitated
>on were:
>
>1- set the filegrowth to a static value, such as 5mb
>2- set teh filegrowth to a percentage.
>
>Can someone enlighten me on the efficiency aspect of any
>of these. Thanks
>
>Session
>



Kline Sphere (Chalk) MCNGP #3
 
Reply With Quote
 
The Poster Formerly Known as Kline Sphere
Guest
Posts: n/a
 
      05-04-2004
>But if the database is very large, 10GB or larger, it is better to use a
>fixed growth amount (static) instead of a percentage growth amount. This
>is because a percentage growth amount can be large on a large database. For
>example, a 10% growth rate on a 10GB database means that when the database
>grows, it will increase by 1GB. This may or may not be what you want. For
>example, a fixed growth rate, such as 100MB at a time, might be more
>appropriate.


Even for small databases you should set the file size(s) to reflect
what you feel will be the maximum size of your database. If nothing
else, this shows that you have performed, as part of some analysis
task, a sizing exercise to determine how much data is expected to be
captured over the system's life time. Alerts should be triggered when
the file size(s) cause a file to grow over the max size, so that the
nature of the data can be re-accessed.

In any case, file(s) which are set at very small sizes will grow
rapidly, which may well lead to external fragmentation and result in
some kind of performance lose.

Kline Sphere (Chalk) MCNGP #3
 
Reply With Quote
 
The Poster Formerly Known as Kline Sphere
Guest
Posts: n/a
 
      05-04-2004
>But if you feel, you will be adding/editing more frequently. Just set the file growth to Percentage.

For toy databases yes, because no one tends to give a damn.

Kline Sphere (Chalk) MCNGP #3
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
Do the Self-Paced Training Kits: Microsoft SQL Server 2000 include Eval copy of SQL Server? Brian Whiting Microsoft Certification 2 12-29-2005 04:24 AM
DBI SQL column datatype not jiving with SQL statement requirement dna Perl 1 01-18-2004 04:15 PM
Re: SQL statement working in SQL Server but not in .aspx.cs page William \(Bill\) Vaughn ASP .Net 0 08-21-2003 10:41 PM
Newbie SQL question: Break SQL into different SELECTs or keep as one Jules ASP General 6 07-15-2003 12:47 PM



Advertisments