Solved

How to detect bad indexes?

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need some help to cast ntext to nvarchar SQL 2000 7 33
SQL Server - Set Value of Multiple Fields in One Query 10 24
Help Required 3 96
SQL Query assistance 16 25
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

772 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