Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Index Rebuild, Reorganise and statistics update

    Can I get some opinions on the scripts I have written for index maintenance please?

    The scripts can be found here:
    index rebuild and reorganise.sql
    update statistics

    I've not had to write something like this before and after fiddling with maintenance plans for 10 mins I decided it might be best to roll my own solution (as I could control the index rebuilds, instead of rebuilding ALL).

    Comments and criticism appreciated
    Last edited by gvee; 07-01-13 at 06:59.
    George
    Home | Blog

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Clean and simple.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Good enough for production?
    George
    Home | Blog

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Do you really want to rebuild a heap table?

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Very good point...

    Amended WHERE clause on index rebuild file
    Code:
    AND    index_type_desc <> 'HEAP'
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There is one option that I'd add that can be a lifesaver in a disk constrained system. I'd add an option that would:
    1. store the initial database recovery mode
    2. Set the database recovery mode to simple
    3. After each index rebuild, backup the transaction log to NUL: to discard the log
    4. restore the initial database recovery mode after the script has run.

    Keep in mind that for the most part I'm just picking nits:
    1. EOL is really Char(13) + Char(10) so I usually define a CHAR(2) variable that contains the EOL and use it to build commands.
    2. I prefer the syntax: DECLARE @debug bit = 1 -- When 1 print commands instead of executing them
    3. You use sys.indexes instead of dbo.sysindexes which means you can only run on SQL 2005 and later.
    4. If you rebuild the clustered index on a table, updating any other index or the statistics is meaningless
    Good job on these scripts, they will be handy!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Cheers chaps.

    I will look in to your points Pat. I should have a bit of a test environment to play with next week in order to sort that out. I have to say that I would be a bit [over?] worried by changing the logging mode in a job (which is what this will turn out to be eventually). Is my worry warranted?

    As for your nit-picking:
    1) Like it, will do. They were only really in there to help me debug when commands are printed.
    2) Can't use that on 2005 instances unfortunately, otherwise I would have done!
    3) I'm in an environment with no instances lower than 2005 *happy dance*. However I may look in to making a modified version if the need or request arises
    4) Hadn't considered this... Are you saying that when the clustered index is rebuilt no other indexes on that base object will need their stats updating? Can you elaborate on this please?
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would never schedule a job like this is a sorely disk constrained environment. Consider that a special case and don't worry about it. As long as you did a full backup before the reindexing started (as a fallback if things go dreadfully wrong) and another full backup once it was complete (to restart the log chain), I would have no reservations about changing the recovery model (logging mode) in a job.

    I tested the DECLARE syntax using compatibility mode 90, but you are correct that it does not actually work when I tried to run it on a SQL 2005 server.

    Paul Randal is the authority on indexing, and his comments are here. When in doubt about SQL storage and architecture, see Paul!

    The short answer about index rebuilding boils down to "it depends" so I shouldn't have offered a blanket statement. In most cases for our clients, that is true but that observation probably doesn't hold for everyone.

    Your code is safe and through, stick with it!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    With regards to the index statistics being rebuilt on clustered index rebuild I have some interesting code for you:
    Code:
    -- Set up table
    IF Object_ID('test_index_stats', 'U') IS NOT NULL
      BEGIN
        DROP TABLE dbo.test_index_stats;
      END;
    CREATE TABLE dbo.test_index_stats (
       a int
     , b char(800)
     , CONSTRAINT x_clustered_index PRIMARY KEY CLUSTERED (a)
         WITH FILLFACTOR = 100
    );
    
    -- Create a "wide" non-clustered index
    CREATE NONCLUSTERED INDEX x_nonclustered_index
      ON dbo.test_index_stats (a, b)
        WITH FILLFACTOR = 100;
    
    -- Let's make sweet, sweet data
    INSERT INTO dbo.test_index_stats (a, b)
      SELECT DISTINCT
             number
           , Cast(number As char(4))
      FROM   master.dbo.spt_values
      WHERE  type = 'P'
      AND    number BETWEEN 1 AND 1000;
    
    -- Update stats on both indexes
    UPDATE STATISTICS dbo.test_index_stats (x_clustered_index);
    UPDATE STATISTICS dbo.test_index_stats (x_nonclustered_index);
    
    -- Let's make *more* sweet, sweet data
    INSERT INTO dbo.test_index_stats (a, b)
      SELECT DISTINCT
             number
           , Cast(number As char(4))
      FROM   master.dbo.spt_values
      WHERE  type = 'P'
      AND    number BETWEEN 1001 AND 2000;
    
    -- Show the stats
    SELECT index_type_desc
         , avg_fragmentation_in_percent
         , Stats_Date(object_id, index_id) As stats_updated
    FROM   sys.dm_db_index_physical_stats(DB_ID(), Object_ID('dbo.test_index_stats', 'U'), NULL, NULL, NULL)
    ORDER
        BY index_type_desc;
    
    -- Deliberate (illustrative) wait
    WAITFOR DELAY '00:00:03';
    
    -- Rebuild the clustered index
    ALTER INDEX x_clustered_index ON dbo.test_index_stats REBUILD;
    
    -- Show the stats
    SELECT index_type_desc
         , avg_fragmentation_in_percent
         , Stats_Date(object_id, index_id) As stats_updated
    FROM   sys.dm_db_index_physical_stats(DB_ID(), Object_ID('dbo.test_index_stats', 'U'), NULL, NULL, NULL)
    ORDER
        BY index_type_desc;
    
    -- Tidy up
    IF Object_ID('test_index_stats', 'U') IS NOT NULL
      BEGIN
        DROP TABLE dbo.test_index_stats;
      END;
    Results:
    Code:
    index_type_desc     avg_fragmentation_in_percent stats_updated
    ------------------- ---------------------------- -----------------------
    CLUSTERED INDEX     3.58744394618834             2013-07-01 17:19:30.613
    NONCLUSTERED INDEX  27.8026905829596             2013-07-01 17:19:30.617
    
    index_type_desc     avg_fragmentation_in_percent stats_updated
    ------------------- ---------------------------- -----------------------
    CLUSTERED INDEX     0                            2013-07-01 17:19:33.743
    NONCLUSTERED INDEX  27.8026905829596             2013-07-01 17:19:33.737
    Now... if we comment out the second INSERT statement and re-run the script the results are a bit different
    Code:
    index_type_desc     avg_fragmentation_in_percent stats_updated
    ------------------- ---------------------------- -----------------------
    CLUSTERED INDEX     6.25                         2013-07-01 17:20:13.803
    NONCLUSTERED INDEX  29.4642857142857             2013-07-01 17:20:13.817
    
    index_type_desc     avg_fragmentation_in_percent stats_updated
    ------------------- ---------------------------- -----------------------
    CLUSTERED INDEX     0                            2013-07-01 17:20:16.833
    NONCLUSTERED INDEX  29.4642857142857             2013-07-01 17:20:13.817
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Disk space isn't a huge issue but backup tape space is!

    So
    1) full backup
    2) change recovery model to simple
    3) rebuild/reorg
    4) truncate log
    5) change recovery model back to original
    ?

    Cheers for the blog link: will read it when I get another half hour on this task :P

    P.S. nobody has said anything against me using sp_msforeachdb. I feel like I should care more about using an undocumented proc and should go a different route (cursor and EXEC (@cmd)?) that's a bit more portable... but I kind of like that dirty little proc!
    George
    Home | Blog

  11. #11
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    I'm in the process of rewriting the rebuild and update stats scripts. I built a table in master dbo and a sproc to run against. The table has a composite primary key and I use it for the update states join. So far performance using a physical table on a TOP 1 select reduced the query time from 3,4 mins to 1,3 mins. Went from table scans to index seeks. There is a lot of code in the update states I threw out. I'll send it along once I've had time to test the results.

    P.S. I was getting rid of the sp_msforeachdb also. Won't need it. Besides looping through db's we might not have included in the first place.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I use sp_MSforeachdb and sp_MSforeachtable with both glee and abandon. Even if Microsoft should choose to endure the pain of removing them at some point in the future I can rebuild them in seconds. They are included in SQL 2014 CTP1, so they won't go away any time soon!

    My plan for managing the logging would go something like:
    1. Full database backup so you have a "Before" image.
    2. Save initial recovery model
    3. Set recovery model to SIMPLE (this is not strictly needed if you jettision the log)
    4. Loop as needed
    5. Do nasty logging things (like reindexing in your example)
    6. Jettision log using BACKUP DATABASE myDb TO DEVICE = 'NUL:'
    7. Loop back for more fun
    8. Restore initial recovery model (if you changed it)
    9. Full database backup to restart the log chain and to have an "after" image.
    Depending on where your tight spot is, you may be able to do many things before you are compelled to jettison your log.

    If you don't need to change the recovery model, I would leave it "as is" to minimize risk. For tight constraints, you have little or no choice. If you need to use SIMPLE, that's fine but if you don't need it then I'd take the conservative approach and just "ride on" with the default recovery mode and jettison the log as needed.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by gvee View Post
    Can I get some opinions on the scripts I have written for index maintenance please?

    The scripts can be found here:
    index rebuild and reorganise.sql
    update statistics

    I've not had to write something like this before and after fiddling with maintenance plans for 10 mins I decided it might be best to roll my own solution (as I could control the index rebuilds, instead of rebuilding ALL).

    Comments and criticism appreciated
    Here's my revision of the stats code:

    Code:
    IF Object_ID('tempdb..#index_stats', 'U') IS NOT NULL
      BEGIN
        DROP TABLE #index_stats;
      END;
    CREATE TABLE #index_stats (
    id				int identity(1,1),
    usecmd			varchar(2000)
    )  
    
    EXECUTE master.sys.sp_MSforeachdb
    '
    USE [?];
    DECLARE @cmd nvarchar(2000)
    
    SET @cmd = "SELECT ''USE '' + DB_Name() + '';'' + Char(13)
           + ''UPDATE STATISTICS '' + s.name + ''.'' + o.name + '' ('' + i.name + '');''
           + Char(13)
    FROM   sys.indexes As i 
    INNER JOIN sys.objects As o ON o.object_id = i.object_id
    INNER JOIN sys.schemas As s ON s.schema_id = o.schema_id
    WHERE DateDiff(dd, Stats_Date(i.object_id, i.index_id), Current_Timestamp) >= 1 -- Current threshold in days
    AND   DB_Name(DB_ID()) NOT IN (''tempdb'', ''msdb'',''master'',''model'') /* Exclude system databases */
    AND   s.name <> ''sys''"
    
    INSERT INTO #index_stats (usecmd)    
    EXECUTE (@cmd)
    '
    SET NOCOUNT ON
    DECLARE @cnt int
    SELECT @cnt = count(*) FROM #index_stats
    
    WHILE @cnt !=0
    BEGIN
    	DECLARE @cmd nvarchar(2000)
    	SELECT @cmd = usecmd FROM #index_stats ORDER BY id DESC
    	EXECUTE (@cmd)
    	-- Debugger below
    	-- SELECT convert(char(5),@cnt) + ' = ' + (SELECT TOP 1 convert(char(5),id) FROM #index_stats ORDER BY id DESC)
    	DELETE FROM #index_stats WHERE id = @cnt
    	SET @cnt = @cnt - 1
    END

Posting Permissions

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