• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

How to detect bad indexes?

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')?

Thx
0
raykata2ddotcom
Asked:
raykata2ddotcom
1 Solution
 
tschill120198Commented:
How often are you updating statistics?  
0
 
wqwCommented:
your index is just getting fragmented. have you tried to (re)build it with a low fillfactor say of 50?

another strategy would be to schedule rebuild every weekend. (yaik -- 2.2 mil records will take 1-2 hours probably:-)

</wqw>
0
 
chigrikCommented:
Instead DBCC CHECKDB, you may simply use this stored procedure to rebuild
all indexes. Execute this procedure as schedule task when your server
is not very hard working.

if object_id('dbo.sp_rebuild_index') is not null drop procedure sp_rebuild_index
GO

CREATE PROC sp_rebuild_index
AS
DECLARE
  @procedure varchar(50),
  @fillfactor varchar(3),
  @objectname varchar(30),
  @database varchar(30)

SET NOCOUNT ON
SELECT @fillfactor = "80"
SELECT @database = (SELECT name from master..sysdatabases
  where dbid = (SELECT dbid from master..sysprocesses where spid = @@SPID))

DECLARE fetch_cursor CURSOR FOR
  SELECT name FROM sysobjects WHERE type = "U" ORDER BY name
OPEN fetch_cursor
FETCH NEXT FROM fetch_cursor INTO @objectname  
WHILE (@@fetch_status <> -1)
  BEGIN
    SELECT @procedure = "DBCC DBREINDEX (" + @objectname
    SELECT @procedure = @procedure + ", ''," + @fillfactor + ")"
    print @objectname
    EXECUTE (@procedure)
    FETCH NEXT FROM fetch_cursor INTO @objectname    
  END
DEALLOCATE fetch_cursor
GO

sp_rebuild_index
GO
0
 
Gustavo Perez BuenrostroCommented:
raykata2ddotcom,

Why don't you do it in a single one line?

exec sp_MSforeachtable "dbcc dbreindex ('?')with no_infomsgs"

PD: Note that statement above can be used to create a job wich can be scheduled for execution.
0
 
raykata2ddotcomAuthor Commented:
I've now done a ton more updates and then checked for index fragmentation on Source in tblWell using the following statements:

use sde
declare @table_id int,@index_id int
set @table_id=object_id('tblWell')
select @index_id=indid from sysindexes
where name='Source'
dbcc showcontig (@table_id,@index_id)

The system responded with:

DBCC SHOWCONTIG scanning 'tblWell' table...
Table: 'tblWell' (1794821456); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 118138
- Extents Scanned..............................: 14771
- Extent Switches..............................: 14770
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.98% [14768:14771]
- Extent Scan Fragmentation ...................: 0.95%
- Avg. Bytes Free per Page.....................: 322.4
- Avg. Page Density (full).....................: 96.02%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If I'm reading this right, I don't have index fragmentation, but my queries are still brutally slow. Any suggestions?
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now