Corrupt Indexes

  • Each night I am extracting some data from a source onto a SQL Server 2000. Each night I am using T-SQL scripts to extract the data and put into a table on the SQL server, there are about 2.2 million records. There are three indexes on the table but one index is specifically for a report that takes 12 mins to run without the index but 2 mins with the index. However each night I am truncating the table and re-populating it with the source data. Lately the report has started to take 12 mins again. I have drop and re-created the index and it took 2 mins.

    I am assuming that the index had been corrupt.

    Does any one had any thoughts on this subject and any suggestions for me to avoid this in the future

  • This may not be due to corruption of index.

    This might be due to the fact that the statistics have not been updated on the index.

    Sql*Server maintains statistics on each index and decides which index to use when a query need to be executed depending on statistics.

    In your case, I think the statistics has not been updated when the table is repopulated. This is done automatically and you do not have much control over it. However you can force update of statistics on a table using UPDATE STATISTICS command

    Try issuing UPDATE STATISTICS before running the report.

  • I agree. If he chooses auto create statistics and auto update statistics in database option, will this work? To me this doesn't work. Could you explain why not.

    Thanks!

    Robert

  • Thanks Rajesh

    This has worked and has got the report back to 2 mins. I have not set the DB to auto update I have scripted it into the extraction, so when it has finished inserting the 2.2 million records it will then update the stats for the whole DB

    Thanks again

  • Just an update to Robert's Query

    1. auto update statistics is periodical

    Sql*Server periodically launches this routine.

    Hence if your report runs before sql*server launches this auto update statistics routine, then the statistics

    will not be up to date for the report.

    2. Uses Sampling to decide whether statistics on a index is out of date.

    So some times sql*server might think that the statistics are still up to date, eventhough there are lot of

    changes happened on the table(due to sampling).

    Auto Create Statistics:

    I would not recommend to use it, as this starts creating indexes for each column and might become big issue for maintenance

  • Thanks Rajes, I will follow your idea.

    Robert

  • Laughing. As a DBA I'm shy about auto-anything. On your job that repopulates the table add a task to reindex or sp_updatestats.

    Yes, autoupdate works on a sampling of the table. You can set it to work on Full Scan, but since you are truncate and repopulating every night, that just doesn't give you anything.

    Patrick

    Quand on parle du loup, on en voit la queue

  • I have created this script that seems to have solved the problem. It takes about 5 mins to run at the end of my daily processes on a 12GB database

    SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON SET ANSI_WARNINGS ON

    GO

    if exists (select * from sysobjects where id = object_id(N'[dbo].[DWH_UPDATE_STATS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure [dbo].[DWH_UPDATE_STATS]

    GO

    /****************************************************************************************************************/

    /**/

    /* File: DWH_UPDATE_STATS.SQL*/

    /*Description:THIS SCRIPT WILL UPDATE ALL THE STATS IN A DB TO A SAMPLE OF 50 PERCENT*/

    /*Warning:*/

    /*Author: PAUL EAST*/

    /*Copyright: EASTY CORP*/

    /*Date and Time: 30/04/2003 12:05*/

    /**/

    /****************************************************************************************************************/

    CREATE PROCEDURE DWH_UPDATE_STATS AS

    DECLARE @dbsid varbinary(85)

    DECLARE @resample CHAR(8)

    SELECT @dbsid = sid

    FROM master.dbo.sysdatabases

    WHERE name = db_name()

    -- required so it can update stats on on ICC/IVs

    set ansi_nulls on

    set quoted_identifier on

    set ansi_warnings on

    set ansi_padding on

    set arithabort on

    set concat_null_yields_null on

    set numeric_roundabort off

    DECLARE @exec_stmt nvarchar(540)

    DECLARE @tablename sysname

    DECLARE @uid smallint

    DECLARE @user_name sysname

    DECLARE @tablename_header varchar(267)

    DECLARE ms_crs_tnames CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT name, uid FROM sysobjects WHERE type = 'U'

    OPEN ms_crs_tnames

    FETCH NEXT FROM ms_crs_tnames INTO @tablename, @uid

    WHILE (@@fetch_status <> -1)

    BEGIN

    IF (@@fetch_status <> -2)

    BEGIN

    SELECT @user_name = user_name(@uid)

    SELECT @tablename_header = 'Updating ' + @user_name +'.'+ RTRIM(@tablename)

    PRINT @tablename_header

    SELECT @exec_stmt = 'UPDATE STATISTICS ' + quotename( @user_name , '[')+'.' + quotename( @tablename, '[')

    SET @exec_stmt = @exec_stmt + ' WITH SAMPLE 50 PERCENT'

    EXEC (@exec_stmt)

    END

    FETCH NEXT FROM ms_crs_tnames INTO @tablename, @uid

    END

    PRINT ' '

    PRINT ' '

    raiserror(15005,-1,-1)

    DEALLOCATE ms_crs_tnames

    RETURN

    GO

    SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON SET ANSI_WARNINGS ON

    GO

    GRANT EXECUTE ON [dbo].[DWH_UPDATE_STATS] TO [public]

    GO

    What it does, is that it updates the stats with a sample of 50% for each table. It is simply a slightly modified version of the SP sp_updatestats

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply