SQL Server Performance

Updates hanging

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by lbusuioc, Nov 16, 2006.

  1. lbusuioc New Member


    I have couple of SQL Server 2000 databases, each of them having the size around 200GB.
    Recently I started experiencing some problems while inserting/updating data.
    The problem is that it seems like it just hangind and it never finish the insert/update. I tried a simple update that affects just few records and it still hangs without any reason. There is no process locking the update process.
    If I go to the database properties and increase the size of the database and then perform the update/insert all works well. My databases are set for unlimited growth , 10% file growth. I have plenty of space free on the disk.
    In any case, it looks like it is not able to grow by itself.
    I use SQL Server 2000, Enterprise Edition.

    I would appreciate your help.
  2. catullus Member

    Maybe you get timeouts during the growth operation. Growing 10 % on a 200 GB database is 20 Gb, which can take a lot of time. Try changing the autogrowth to a fixed size in Mb, e.g. 100 Mb. If that works, you'll have to decide what a good amount is for growth: with a smaller size the files will have to grow more often, but the performance hit each time will be smaller.
  3. MohammedU New Member

    Is Auto update statatistics enabled? If not try afterenabling it.

    Mohammed.
  4. satya Moderator

    Hey Mohammed, good to see you here...keep up the good work.
    I would also suggest to update the statistics on the tables that are inserted and updated frequently, also recompile the SPs and triggers in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    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