Solved

How to detect bad indexes?

Posted on 2000-03-20
5
432 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now