Is there anyway other than dbcc checkdb ('db_name') to detect bad indexes? I am repeatedly experiencing the following type of problem with tables in a one to many relationship that is enforced by triggers instead of foreign key relationships.
My parent table is called tblWell and looks like this:
1)WellID - integer, non clustered primary key
2)Source - integer, non unique non clustered index
3)about 50 other columns
This table contains about 2.2 million rows.
My child table is called tblWellRLog and looks like this:
1)WellID - integer, non unique non clustered index
2)RecNbr - integer, non unique non clustered index
3)about 10 other columns
The non clustered primary key is the combination of WellID and RecNbr
This table contains about 110,000 rows.
My problem is often with the index on the Source column in tblWell. I can run queries with where clause conditions on WellID and/or Source and everything works efficiently. But when I run a query with an inner join from tblWell to tblWellRLog on the WellID columns and a criteria on the Source column things grind to a halt. After 4 minutes I ususally give up and kill the query. I then run dbcc checkdb on the entire database and no errors are reported. So then I delete the index on the Source column in tblWell and then re-create the index on the Source column in table well. I re-run the inner join query and it returns the desired rows in an acceptable time of 10 to 15 seconds.
About every 2 weeks the index on the Source column seems to get corrupted and I only find out about it when my queries start grinding to a halt. The problem is also occuring on other indexes.
Any one have any ideas as to what is happening and why? Is there anyway other than DBCC CHECKDB ('db_name')?