Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to detect bad indexes?

Posted on 2000-03-20
5
435 Views
Last Modified: 2012-05-04
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
Comment
Question by:raykata2ddotcom
5 Comments
 
LVL 2

Expert Comment

by:tschill120198
ID: 2637317
How often are you updating statistics?  
0
 
LVL 4

Expert Comment

by:wqw
ID: 2637321
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
 
LVL 8

Accepted Solution

by:
chigrik earned 300 total points
ID: 2637385
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
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2639139
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
 

Author Comment

by:raykata2ddotcom
ID: 2666660
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question