What factors that degrade the performance of a SQL Server 2000 Job?

We are currently running a SQL Job that archives data daily at every 10PM. However, the end users complains that from 10PM to 12, the page shows a time out error.

Here’s the pseudocode of the job

  • loading excel data from sharepoint library to table using ssis
  • SQL: Is there a possibility to convert numbers (1,2,3,4…) to letters (A,B,C,D…)
  • What is syncobj in SQL Server
  • Retrieving SQL Relationships as a comma delimited string
  • Table Join performance issue with Entity Framework
  • Is it possible to see the CODE for sp_executesql? Is it TSQL?
  • while @jobArchive = 1 and @countProcecessedItem < @maxItem
         exec ArchiveItems @countProcecessedItem out
         if error occured
              set @jobArchive = 0
         delay '00:10'
    

    The ArchiveItems stored procedure grabs the top 100 item that was created 30 days ago, process and archive them in another database and delete the item in the original table, including other tables that are related with it. finally sets the @countProcecessedItem with the number of item processed. The ArchiveItems also creates and deletes temporary tables it used to hold some records.

    Note: if the information I’ve provide is incomplete, reply and I’ll gladly add more information if possible.

  • Can not delete or modify or see same table foreign key constraint
  • Meaning of square brackets in MS-SQL table designer?
  • Full text search does not work if stop word is included even though stop word list is empty
  • Where value in column containing comma delimited values
  • Using the dangerous IN clause in SQL
  • Validating the existence of 350 million files over a network
  • 3 Solutions collect form web for “What factors that degrade the performance of a SQL Server 2000 Job?”

    Only thing not clear is it the ArchiveItems also delete or not data from database. Deleting rows in SQL Server is a very expensive operation that causes a lot of Locking condition on the database, with possibility to have table and database locks and this typically causes timeout.
    If you’re deleting data what you can do is:

    • Set a “logical” deletion flag on the relevant row and consider it in the query you do to read data
    • Perform deletes in batches. I’ve found that (in my application) deleting about 250 rows in each transaction gives the faster operation, taking a lot less time than issuing 250 delete command in a separate way

    Hope this helps, but archiving and deleting data from SQL Server is a very tough job.

    While the ArchiveItems process is deleting the 100 records, it is locking the table. Make sure you have indexes in place to make the delete run quickly; run a Profiler session during that timeframe and see how long it takes. You may need to add an index on the date field if it is doing a Table Scan or Index Scan to find the records.

    On the end user’s side, you may be able to add a READUNCOMMITTED or NOLOCK hint on the queries; this allows the query to run while the deletes are taking place, but with the possibility of returning records that are about to be deleted.

    Also consider a different timeframe for the job; find the time that has the least user activity, or only do the archiving once a month during a maintenance window.

    As another poster mentioned, slow DELETEs are often caused by not having a suitable index, or a suitable index needs rebuilding.

    During DELETEs it is not uncommon for locks to be escalated ROW -> PAGE -> TABLE. You reduce locking by

    • Adding a ROWLOCK hint (but be aware
      it will likely consume more memory)

    • Randomising the Rows that are
      deleted (makes lock escalation less
      likely)

    • Easiest: Adding a short WAITFOR in
      ArchiveItems

      WHILE someCondition
      BEGIN

      DELETE some rows

      — Give other processes a chance…
      WAITFOR DELAY ‘000:00:00.250’

      END

    I wouldn’t use the NOLOCK hint if the deletes are happening during periods with other activity taking place, and you want to maintain integrity of your data.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.