SQL question

Discussion in 'MCSD' started by Session, May 3, 2004.

  1. Session

    Session Guest

    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
     
    Session, May 3, 2004
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:79cd01c4313a$90827730$...
    > 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
    >
    >
     
    Cowboy \(Gregory A. Beamer\), May 3, 2004
    #2
    1. Advertising

  3. 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.aspx?scid=kb;en-us;315512&Product=sql2k

    Thanks and Regards,
    (Imay) Imayavaramban R

    This posting is provided 'AS IS' with no warranties, and confers no rights.
     
    Imayavaramban R, May 3, 2004
    #3
  4. 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
    >
    >
    >
     
    =?Utf-8?B?c2F1cmFiaGRvdG5ldA==?=, May 3, 2004
    #4
  5. Again NDA comes to mind.

    On Mon, 3 May 2004 11:15:07 -0700, "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
    >



    Kline Sphere (Chalk) MCNGP #3
     
    The Poster Formerly Known as Kline Sphere, May 4, 2004
    #5
  6. >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
     
    The Poster Formerly Known as Kline Sphere, May 4, 2004
    #6
  7. >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
     
    The Poster Formerly Known as Kline Sphere, May 4, 2004
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Brian Whiting
    Replies:
    2
    Views:
    746
    Brian Whiting
    Dec 29, 2005
  2. saucer
    Replies:
    0
    Views:
    1,167
    saucer
    Jul 14, 2003
  3. Tommy
    Replies:
    1
    Views:
    1,848
    Mr. Arnold
    Nov 29, 2007
  4. Vicky
    Replies:
    0
    Views:
    694
    Vicky
    Apr 6, 2007
  5. Replies:
    0
    Views:
    1,475
Loading...

Share This Page