Insert SQL Degradation

Question

I have an SSIS package that loads a database every night. the package took 45 minutes to run over the years (From Sql 2000 to Sql 2005 to Sql 2008). All of a sudden this weekend (Oct 31) my package started taking 8 hours to run. I have changed nothing, the server is the same no newupdates have been added.

I think I have narrowed it down to an INSERT statement

Does anyone have any clue on why this is happening?

Answer 1

Does the destination table have a clustered index on it?

How is the filesystem that holds the data doing?  Are they running out of space? 

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 2

when did you upgrade to SQL Server 2008?  There could be issues with the code with SSIS; what worked with 2000/2005 may not work as well with 2008, check out BOL for more guidance on this.

Another one to check is the database model; has it been altered from Simple to Full or Bulk-Logged?

Has virus scanning been configured to scan the database files?  If so take it off.

Run a checkdb on the database and check for fragmentation; also have the System Administrator check for disk fragmentation.

---

Tony C

Answer 3

Thanks to all who assisted. The issue was INDEX Fragmentation.
using the DBCC REINDEX, the issue was resolved.