SQL Server Performance

Compress Database

Discussion in 'Getting Started' started by Viral Shah, Sep 23, 2009.

  1. Viral Shah New Member

    Hi All,
    How do I identify if a paticular DB is compressed???
    I am running SQL Server 2000 with SP4.
    Any help would be Appreciated.
    Thanks,
    Viral Shah
  2. FrankKalis Moderator

    Welcome to the forum!
    What exactly do you mean by "compressed"? There is no such feature in SQL Server 2000, if i remeber correctly.
  3. Viral Shah New Member

    Hi Frank,
    Thanks.
    If you can recall: While shrinking DB file there is an option : Compress Pages and then truncate free space from the file.
    In one of the artical by Ken Henderson's Its mentioned that compress SQL Server data file would have greater performance degradation.
    http://blogs.msdn.com/khen1234/archive/2005/04/25/411852.aspx.
    I am trying to troubleshoot the performance issue with one of the critical DB... where in the DB size is hardly 120 GB hosted on SQL Server 2000 SP4 running on Windows 2003 SP2. Its a 2 node Cluster
    Server Configuration
    4 AMD Opteron Processor 2.8 Gz
    16 GB RAM
    DB is running on NAS delivered over ISCSI.
    We always had performance issue with this DB, but recently it got very hype.
    Because of the performance issue we have moved this database to a new NAS, which only hosts this DB and nothing else. This aggregate has 48 Volume and complete aggregate is dedicated for this DB.
    The amazing part is the disk latency is still more than 200+ms, which is quite unacceptable. In one such Instance I have seen latency of 1000MS.
    At this moment I am completely lost.... where to go next.; though we are planning to move to SAN; but does it really make sence???
    Thanks,
    Viral Shah
  4. satya Moderator

    SP_HELPDB will get you the database status whether AUTO SHRINK is ON or not, in this case you need to check any jobs that are schedule to see if the DBCC SHRINKFILE is executed as a part of DB maintenance jobs.

Share This Page