SQL Server Odd Behavior with Indexes

• smunshi

  Old Hand

  Points: 349

  More actions

  August 20, 2004 at 1:34 pm

  #102166

  I have an interesting problem with SQL Server indexes. We had a Process that ran at 2:00am till 7:00am each day. This process are all sp's that update, insert and delete data. It used to take five hours to complete this process, until 1 month ago I added two non-clustered indexes to a table that I noted was touched by all the sp's in this Process. What happened was the next day the Process execution took only took 3 hours!! We were all elated and happy. So by adding two non-clustered indexes to one table cut the Processing time by 2 whole hours!!. However, this happiness was short lived, because this week the Server was "bounced" and the Processing time went back to 5 hours!!. I do not have any explanation as to why this SQL Server went back to its old ways. We have a SQL Server 2000 Enterprise Edition running on Windows 2000 Server with 4GB of memory with AWE turned on. I have jobs that run Update Statistics and DBCC DBREINDEX weekly on this database. Any suggestions....

• sa24

  SSCrazy Eights

  Points: 8027

  More actions

  August 23, 2004 at 9:55 am

  #520196

  Can you try to rebuild the index and run the process ?

• Jim P.

  SSCrazy Eights

  Points: 8725

  More actions

  August 23, 2004 at 10:01 am

  #520199

  Have you checked if the indexes are still there?

  Also have you tried running EXEC sp_updatestats against the database?

  
  
  ----------------
  Jim P.

  A little bit of this and a little byte of that can cause bloatware.