Sudden performance drop - system unuseable 3

[mikelawrence]

Hi,

I had a problem last week with an online report, it went from taking a 3 secs to minutes. I went through and rebuilt the indexes by using the Index Tuning Wizard and it went back to a couple of secs. Strange i thought as nobody had done anything to affect the indexes. Unfortunately the problem has come back on the same and other reports and the index tuning wizard helps not as it says the indexes are all in place in one report and the other report now times out and so the trace does not help. Any ideas as the above makes the system unuseable.

I will be forever in your debt as i am now very stressed!

mike

 

[Patrick2002]

This is the first paragraph of the topic "Rebuilding an index" in the BOL :

When you create an index in the database, the index information used by queries is stored in index pages. The sequential index pages are chained together by pointers from one page to the next. When changes are made to the data that affect the index, the information in the index can become scattered in the database. Rebuilding an index reorganizes the storage of the index data (and table data in the case of a clustered index) to remove fragmentation. This can improve disk performance by reducing the number of page reads required to obtain the requested data

It seems to me that the first thing you should try is to rebuild your existing index. Try that and see if it helps. If it does, make a scheduled job of it that runs once in a while.
Hope this is all it takes and your stress is now relieved (and don't forget : you owe me ....)

 

[mikelawrence]

This sounds plausible but at the risk of appearing a bit of an imbecile i'm not sure how to drop and rebuild indexes. (My stress level is now ok as i restored to last nights copy and that is working fine so it gives me time to learn how to do things properly!)

i think the cause of the problem may be that i created an ID field in a table as an indentity field to help delete some duplicates (as recommended in the FAQ). I didn't rebuild any indexes at this stage as the field was not used in queries. Now i read the index stuff in BOL and it seems fine if you have a procedure to manually create indexes but we just put a trace on and then implement the index optimiser recommendations. There is a flag that can be set on the optimiser to not use existing indexes but would that drop and recreate any that may be necessary?

Now i do owe you but i'd be happy to shoulder an even heavier debt if you could help banish the clouds of my ignorance even further.

ta

mike

 

[ClayG]

Lookup in BooksOnline:
DBCC REINDEX

actually this entry in my BOL takes me to a FAQ page; look
down the BOL index to:

DBCC statements
DBCC REINDEX

You might only need to refresh statistics:
UPDATE STATISTICS

Also, did you check that the report was not being blocked by other locking ?

 

[clarkin]

This might help you:

http://www.sql-server-performance.com/rebuilding_indexes.asp

There are another 4 index-related tip pages at that site, they might give some insight.



Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].