August 20, 2004 at 1:34 pm
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....
August 23, 2004 at 8:00 am
This was removed by the editor as SPAM
August 23, 2004 at 9:55 am
Can you try to rebuild the index and run the process ?
August 23, 2004 at 10:01 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply