Solved

How to detect bad indexes?

Posted on 2000-03-20
5
430 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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

708 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

12 Experts available now in Live!

Get 1:1 Help Now