SQL Server Performance

LARGE DATABASES (AND FEW FREE DISK SPACE)

Discussion in 'Performance Tuning for Hardware Configurations' started by johnny_bigu, Jun 8, 2004.

  1. johnny_bigu New Member

    I have used the W2K´s file attribute: compress.

    It saved more than 50% of my disk space!

    The users say me that the performance is not altered (the performance not changed, are the same).
  2. Argyle New Member

    It's not recommended to compress the actual database files. Backups should be no problem. Using compression will require more cpu from your server.
  3. satya Moderator

    Databases on compressed volumes may cause significant performance overhead. The amount will vary depending on volume of I/O and ratio of reads to writes. However, over 500 percent degradation has been observed under some conditions.

    Reliable transactional recovery of the database requires sector-aligned writes, which compressed volumes do not support.

    If backups to compressed volumes are performed, a complete DBCC should be run before each backup and after each restore. The time required to run the DBCC should be factored into the decision to use this backup technique.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. johnny_bigu New Member

    This db is about billing-like application: a single table have more than 90% of amount data.

    I think that is this case is possible economize disk space and have a few performance degradation. I´ll test the backup/recovery conditions.

    Thks a lot.
  5. satya Moderator

    Ensure to perform DBCC checks alongwith the backup/recovery schedule to ensure the core area is not affected.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. simas New Member

    let us know what you find out - generally we try to avoid compressing (or encrypting) the file system because it is bad for performance of the database.
    Check out six performance tuning articles Brad McGehee published on this site- they are very good ; one of the steps is making sure that file system is not compressed (or encrypted).
    What is your index allocation like- how far away are you from your optimal fillfactors set ion your indexes? Can any space be recovered there? check out DBCC SHOWCONTIG/INDEXDEFRAG in BOL

    simas

Share This Page