SQL Server Performance

Free Space for SQL Server DB

Discussion in 'Performance Tuning for DBAs' started by alimmia, Dec 22, 2005.

  1. alimmia New Member

    My database size is around 250 GB. How much free space should I keep in the database for best performance. Most of the time we update the database tables monthly.

    Thanks,

    Alim Mia
  2. Luis Martin Moderator

    Just live to grow dinamically.
    What kind of recovery model do you have?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  3. alimmia New Member

    Thank you, Luis. My DB is in simple recovery mode. It is in data warehousing environment. The 228 GB database occupies 90 GB free space. Does a database require any free space for good performance.

    Alim
  4. Luis Martin Moderator

    No. I you have your database with auto grow 10%, and you have plenty space in disk, don't worry.
    To keep performance, you have to run some maintenance plan like rebuild indexes and update statistics.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  5. alimmia New Member

    Thanks, Luis!
  6. dtipton New Member

    I disagree on the 10% recommendation.

    With a 250GB database, 10% growth will allocate 25GB of space! This will take significant time at the OS level and is almost certainly more space than you need. Choose a more reasonable fixed rate of growth not a %

    This is also true of small databases. I've seen many instances of physical fragmentation (OS level) caused by a database that grew from 1MB to 1GB by 10% increments.
  7. mmarovic Active Member

    Actually, the best solution (IMO) is to have db size that will never be reached so no expansion will be needed.
  8. johnson_ef Member

    Yes, I am also disagree the 10% of growth. The reason is, it will only give the over-head to the server. better you find out the growth of the data in a day or in a week and mention the size with the 10% of the growth.

    Example:
    in a day the grwoth is 200mb, then put 220 mb in the filegrowth instead of %.

    Secondly, if possible, split the database in multiple files. This will enhance the performance.

    What data warehousing tools are you using? whats the frequency of cubes generation in your environment?

    -Johnson
  9. dtipton New Member

    <<Actually, the best solution (IMO) is to have db size that will never be reached so no expansion will be needed.>>

    Agreed. Of course this requires accurate knowledge about projected growth or a substanial disk drive budget.

    <<Secondly, if possible, split the database in multiple files. This will enhance the performance.>>

    Will multiple files help if you don't have multiple physical disk drives to spread them across? In other words, if a have a single RAID array for my data will it matter if I have one large file or multiple smaller files? Of course it would be nice to have multiple devices, but doesn't the many files strategy depend on the hardware you have to work with?
  10. mmarovic Active Member

    I have no problem with 10% growth even if it takes a long time as long as it will grow just once. Better to reserve more space then to grow frequently.
  11. Luis Martin Moderator

    Yeap, I miss that.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  12. johnson_ef Member

    <<Will multiple files help if you don't have multiple physical disk drives to spread them across? In other words, if a have a single RAID array for my data will it matter if I have one large file or multiple smaller files? Of course it would be nice to have multiple devices, but doesn't the many files strategy depend on the hardware you have to work with?>>



    yes, even if you have only one disk or one DISK Controller, better to limit the size of data files. I found the performance difference after doing so.

    suppose, if one of my table has 30 GB size, but initialy I kept the max-file size for 10 GB. Now the query search is divided into 3 threads and each thread will take proportionate time to get that done and which is much faster than searching one thread in one huge data file.

    Secondly, this will reduce the disk contention as well in terms of I/O.

    -Johnson
  13. dtipton New Member

    <<Now the query search is divided into 3 threads and each thread will take proportionate time to get that done and which is much faster than searching one thread in one huge data file.>>

    I'm no hardware expert so please correct me if I'm wrong, but isn't it true that if I have a single device I can only be searching one location on the disk at a time (single read/write head)? How does having three files (three separate locations on disk) provide reduced I/O contention or speed my query?

    I'm not trying to be argumentative, just trying to understand the advice that is being offered.
  14. satya Moderator

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx#EEAA for your complete understanding.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page