SQL Server Performance

Automated Job reindex, update stats - All Tables

Discussion in 'Contribute Your SQL Server Scripts' started by spiderico, Dec 23, 2004.

  1. spiderico New Member

    These scripts accomplish reindexing/defraging your database and updating the sql statistics. It also allows you to stop and restart the execution of the reindex or update statistics at any time, and when you start the script again, it starts where it left off.
    This script also takes your table fragmentation to 0, which is done by doing a reindex of the primary key indexes.

    There are 3 scripts you need in order for this to work. The first script creates a table (Index_Log) that stores live and historical data about which tables and indexes have been reindexed or had update statistics run. The second script goes through all of your base tables and does a dbreindex on each index. The third script goes through all of your base tables and does an update statistics for each index.

    You can also use the Index_Log table which stores the progress information for each job to view real time stats on the status of each job. I personally, made a php script that displays the progress of the jobs, which does nothing but display the information from the table based on Job Type (Indexing or Update Stats). It also allows me to view the total execution time and a history of every job. What you do with the table for reporting and viewing purposes is up to you, but you need it to run the jobs.

    INSTRUCTIONS: Execute the first script in query analyzer to create the table. Next, Create a job under maintanence, sql agent, Jobs. Cut and paste the second script as step 1 of the job and save the job. Next, create another job for the third script and add it as step 1, and save the job. Now all you have to do is run your jobs whenever you need to, and view the Index_Log table to see the status of the jobs.


    -#############EXECUTE THIS SCRIPT ONE TIME#############-
    --------------------CREATE INDEX_LOG TABLE------------------
    CREATE TABLE [dbo].[Index_Log] (
    [pkIndex_Log] [int] IDENTITY (1, 1) NOT NULL ,
    [Complete] [bit] NULL ,
    [Table_Name] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Index_Name] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Start_Date] [datetime] NULL ,
    [End_Date] [datetime] NULL ,
    [Run_No] [int] NULL ,
    [Run_Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO
    --------------------END CREATE INDEX_LOG TABLE------------------


    -#############SAVE THIS AS STEP 1 FOR JOB 1#############-
    ------------------INDEXING JOB - DBREINDEX---------------------------
    ----------------------------------Insert table values------------------
    --count records in Index_Log where Complete=0
    if (Select count(pkIndex_Log) as Ind_Count from Index_Log where Complete=0 and Run_Type='Indexing') = 0
    Begin
    -----START INSERT-----
    DECLARE @Cur_Run int
    SET @Cur_Run=(select isnull(max(Run_No),0) + 1 from Index_Log)

    DECLARE @TableName sysname
    DECLARE cur_reindex CURSOR FOR

    SELECT table_name
    FROM information_schema.tables
    OPEN cur_reindex
    FETCH NEXT FROM cur_reindex INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DECLARE @IndexName sysname
    DECLARE cur_reindexA CURSOR FOR
    SELECT i.name as index_name
    FROM dbo.sysindexes i
    WHERE id = object_id(@TableName) and i.indid > 0 and i.indid < 255
    and (indexkey_property(object_id(@TableName), i.indid, 1, N'isdescending') is not null)
    and (i.name is not null) and dpages>0
    OPEN cur_reindexA
    FETCH NEXT FROM cur_reindexA INTO @IndexName
    WHILE @@FETCH_STATUS = 0
    BEGIN

    insert into Index_Log (Complete, Table_Name, Index_Name, Run_No) values(0, @TableName, @IndexName, @Cur_Run)

    FETCH NEXT FROM cur_reindexA INTO @IndexName
    END
    CLOSE cur_reindexA
    DEALLOCATE cur_reindexA
    FETCH NEXT FROM cur_reindex INTO @TableName
    END
    CLOSE cur_reindex
    DEALLOCATE cur_reindex
    -----END INSERT-----
    End
    ----------------------------------END Insert table values------------------
    DECLARE @doTableName varchar(75), @doIndexName varchar(75), @dopkIndex_Log int
    DECLARE cur_doindex CURSOR FOR
    select Table_Name, Index_Name, pkIndex_Log from Index_Log where Complete=0 and Run_Type='Indexing'
    OPEN cur_doindex
    FETCH NEXT FROM cur_doindex INTO @doTableName, @doIndexName, @dopkIndex_Log
    WHILE @@FETCH_STATUS = 0
    BEGIN
    update Index_Log set Start_Date=getDate() where pkIndex_Log=@dopkIndex_Log
    DBCC DBREINDEX (@doTableName, @doIndexName, 80, sorted_data_reorg)
    update Index_Log set End_Date=getDate(), Complete=1 where pkIndex_Log=@dopkIndex_Log
    FETCH NEXT FROM cur_doindex INTO @doTableName, @doIndexName, @dopkIndex_Log
    END
    CLOSE cur_doindex
    DEALLOCATE cur_doindex


    -#############SAVE THIS AS STEP 1 FOR JOB 2#############-
    ----------------------------------Insert table values------------------
    --count records in Index_Log where Complete=0
    if (Select count(pkIndex_Log) as Ind_Count from Index_Log where Complete=0 and Run_Type='Up-Stats') = 0
    Begin
    -----START INSERT-----
    DECLARE @Cur_Run int
    SET @Cur_Run=(select isnull(max(Run_No),0) + 1 from Index_Log)

    DECLARE @TableName sysname
    DECLARE cur_reindex CURSOR FOR

    SELECT table_name
    FROM information_schema.tables
    OPEN cur_reindex
    FETCH NEXT FROM cur_reindex INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DECLARE @IndexName sysname
    DECLARE cur_reindexA CURSOR FOR
    SELECT i.name as index_name
    FROM dbo.sysindexes i
    WHERE id = object_id(@TableName) and i.indid > 0 and i.indid < 255
    and (indexkey_property(object_id(@TableName), i.indid, 1, N'isdescending') is not null)
    and (i.name is not null) and dpages>0
    OPEN cur_reindexA
    FETCH NEXT FROM cur_reindexA INTO @IndexName
    WHILE @@FETCH_STATUS = 0
    BEGIN

    insert into Index_Log (Run_Type, Complete, Table_Name, Index_Name, Run_No) values('Up-Stats', 0, @TableName, @IndexName, @Cur_Run)

    FETCH NEXT FROM cur_reindexA INTO @IndexName
    END
    CLOSE cur_reindexA
    DEALLOCATE cur_reindexA
    FETCH NEXT FROM cur_reindex INTO @TableName
    END
    CLOSE cur_reindex
    DEALLOCATE cur_reindex
    -----END INSERT-----
    End
    ----------------------------------END Insert table values------------------

    DECLARE @doTableName varchar(75), @doIndexName varchar(75), @dopkIndex_Log int
    DECLARE cur_doindex CURSOR FOR
    select Table_Name, Index_Name, pkIndex_Log from Index_Log where Complete=0 and Run_Type='Up-Stats'
    OPEN cur_doindex
    FETCH NEXT FROM cur_doindex INTO @doTableName, @doIndexName, @dopkIndex_Log
    WHILE @@FETCH_STATUS = 0
    BEGIN
    update Index_Log set Start_Date=getDate() where pkIndex_Log=@dopkIndex_Log
    exec ('update Statistics ' + @doTableName + ' ' + @doIndexName + ' WITH FULLSCAN')
    update Index_Log set End_Date=getDate(), Complete=1 where pkIndex_Log=@dopkIndex_Log
    FETCH NEXT FROM cur_doindex INTO @doTableName, @doIndexName, @dopkIndex_Log
    END
    CLOSE cur_doindex
    DEALLOCATE cur_doindex

Share This Page