SQL Server Performance

database shrink

Discussion in 'Performance Tuning for DBAs' started by jay, Feb 2, 2005.

  1. jay New Member

    I have shrinked the database using Enterprise Manager- Database-All tasks-shrink database. In shrink action , I have selected option - Move pages to the beginning of the file before shrinking.
    Now the performance of the database is very slow.
    Is it because I selected the option - Move pages to the beginning of the file before shrinking ?
    And how to improve the performance of the database ?

  2. thomas New Member

    Has the shrink finished? Shrinking a database is a very intensive I/O operation and will slow things down considerably while running (and may cause blocking).

    If it has already finished and things are slow, it may be because shrinking can cause a lot of internal data and index fragmentation - in order to push all the tables into a smaller space, they get broken up a lot.

    Being able to have plenty of free space in a database is a GOOD thing. If you're doing regular reindexing like you should, you need plenty of free space (1.5 times the size of your largets table) to be able to reindex.

    You should only ever shrink if you really are short of disk free space.

    Tom Pullen
    DBA, Oxfam GB
  3. satya Moderator

    Check which queries are running slow after this SHRINK operation.
    Query execution plan and PROFILER are the best bet to monitor the operation.

    True, both the REINDEXING and SHRINK operations works in opposite ways.
    If you're getting the performance issues then its better to reindex the tables for optimum performance.

    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. mmarovic Active Member

  5. jay New Member


    Shrink was over 4 day's back.I did shrink as there was space problem.Since then the performance issue has started with database.
    The response is slow for almost all queries.

    Besides re-indexing any other alternative is there ?
  6. thomas New Member

    Why can't you reindex? There's no magic wand, I'm afraid. Have you followed Satya's advie re: using Profiler to determine what the problematic queries are and why they're problematic?

    Suggest to your bosses to buy bigger disks. If you have lots of data you need to be able to accommodate it or you're stuck with crappy performance.

    Tom Pullen
    DBA, Oxfam GB
  7. satya Moderator

    ... and get more physical RAM to address intermittent performance issues and ensure to schedule the DBREINDEX to gain the performance.

    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.

Share This Page