SQL Server Performance

SQL batch process that runs nightly that is failing take longer to run the insert and timeout

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by abdulafroze, Mar 12, 2009.

  1. abdulafroze New Member

    I'm having a problem at the moment trying to investigate some performance issue with one of our batches here and I'm wondering would you mind helping me out if possible The problem is that we have a SQL batch process that runs nightly that is failing nightly.
    The batch inserts records into multiple tables at the rate of about 23 inserts a second over a 15 minute period. The inserts are not contained within transactions. What we have noticed is that every 2 minutes there is a spike in the amount of time that the insert takes to run and the timeout is set at 30 seconds. So if the insert runs for longer than 30 seconds the batch fails. A solution is to increase the timeout but I want to know why every 2 minutes SQL decides to take longer to run the insert. Is it because of the physical structure of SQL and how it does it's inserts? Also, it doesn't matter if the insert is a smaller one or a larger one it still takes 30 seconds to run every 2 minutes.
    The transaction recovery model was set to Full but we changed it to be simple and that did not help.
    Any ideas? We are running SQL 2000.
    my Email is a_afroze@yahoo.com I would highly appericate your help on this
    Thanks,
  2. mst New Member

    Could be many things, but confirm that you are not automatically shrinking your tlog or db size; if for example you had autoshrink tlog ON and your growth setting is small, it's possible this process does this:
    Run for 2 mins
    Extend the size of the tlog
    Repeat
  3. satya Moderator

    Welcome to the forums.
    The main issue with SQL performance will be due to defragmentation whether it is an update or insert of data within the database. So in this case I would like to ask what kind of database maintenance jobs are scheduled on the server, reindex and defragmentation methods.
    http://sqlserver-qa.net/blogs/perftune/archive/2008/02/05/3182.aspx a detailed information on finding the root cuase.

Share This Page