Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: Update Statistics On All Tables

    I have recently defragged my SQL server using INDEXDEFRAG. Can somebody please tell me how to update the statistics on all the tables? Thanks in advance.

    Below is the script that I executed to defrag all the tables in my database if anyone needs this.



    /*Perform a 'USE <database name>' to select the database in which to run the script.*/
    -- Declare variables
    SET NOCOUNT ON
    DECLARE @tablename VARCHAR (128)
    DECLARE @execstr VARCHAR (255)
    DECLARE @objectid INT
    DECLARE @indexid INT
    DECLARE @frag DECIMAL
    DECLARE @maxfrag DECIMAL

    -- Decide on the maximum fragmentation to allow
    SELECT @maxfrag = 20.0

    -- Declare cursor
    DECLARE tables CURSOR FOR
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'

    -- Create the table
    CREATE TABLE #fraglist (
    ObjectName CHAR (255),
    ObjectId INT,
    IndexName CHAR (255),
    IndexId INT,
    Lvl INT,
    CountPages INT,
    CountRows INT,
    MinRecSize INT,
    MaxRecSize INT,
    AvgRecSize INT,
    ForRecCount INT,
    Extents INT,
    ExtentSwitches INT,
    AvgFreeBytes INT,
    AvgPageDensity INT,
    ScanDensity DECIMAL,
    BestCount INT,
    ActualCount INT,
    LogicalFrag DECIMAL,
    ExtentFrag DECIMAL)

    -- Open the cursor
    OPEN tables

    -- Loop through all the tables in the database
    FETCH NEXT
    FROM tables
    INTO @tablename

    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- Do the showcontig of all indexes of the table
    INSERT INTO #fraglist
    EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
    WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
    FETCH NEXT
    FROM tables
    INTO @tablename
    END

    -- Close and deallocate the cursor
    CLOSE tables
    DEALLOCATE tables

    -- Declare cursor for list of indexes to be defragged
    DECLARE indexes CURSOR FOR
    SELECT ObjectName, ObjectId, IndexId, LogicalFrag
    FROM #fraglist
    WHERE LogicalFrag >= @maxfrag
    AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

    -- Open the cursor
    OPEN indexes

    -- loop through the indexes
    FETCH NEXT
    FROM indexes
    INTO @tablename, @objectid, @indexid, @frag

    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
    ' + RTRIM(@indexid) + ') - fragmentation currently '
    + RTRIM(CONVERT(varchar(15),@frag)) + '%'
    SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
    ' + RTRIM(@indexid) + ')'
    EXEC (@execstr)

    FETCH NEXT
    FROM indexes
    INTO @tablename, @objectid, @indexid, @frag
    END

    -- Close and deallocate the cursor
    CLOSE indexes
    DEALLOCATE indexes

    -- Delete the temporary table
    DROP TABLE #fraglist
    GO

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    you might want to look at sp_autostats
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Try this

    exec sp_updatestats 'resample'
    Regards
    Kris Zywczyk

  4. #4
    Join Date
    Nov 2005
    Posts
    91
    I have scheduled a job on the SQL Server (2000) to defrag and update the statistics of 2 databases.

    Step 1 executes the Indexdefrag script that I pasted above and step 2 executes the update statistics command (exec sp_updatestats 'resample') to update the statistics on all the tables in the database. The system then applies the same scripts in steps 3 then 4 on another database. For some reason SQL Server only executes step one and hangs on when executing step 2. Can somebody please help troubleshoot my problem. Thanks in advance.

  5. #5
    Join Date
    Nov 2005
    Posts
    91
    Nevermind it works....Thanks for the help guys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •