Difference between DEFRAG and REORGANIZE

  • Rob Reid-246754

    Hall of Fame

    Points: 3901

    Hi

    I am simplifying a proc we have that we cannot use half of due to not having enterprise edition or online rebuilds of indexes (2012).

    It seems it has been doing nothing for ages due to the 2 threshold parameters and the fragmentation levels being over the DEFRAG, so it should REBUILD but can't.

    Therefore I just want a simple job that DEFRAGS/REORGANIZES on all tables over X% fragmentation.

    I just want to know after reading http://sqlperformance.com/2013/07/sql-statistics/statistics-updates whether I should call

    EXEC sp_updatestats

    after my DEFRAG code

    or use a

    EXEC('UPDATE STATISTICS ' + @tablename + ' ' + @StatisticName)

    command in a loop that gets those values e.g

    INSERT INTO @tbls (tblschema, tblname,statname)

    SELECT [sch].[name], [so].[name], [ss].[name] AS [TableName]

    FROM [sys].[stats] [ss]

    JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]

    JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]

    OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id], [ss].[stats_id]) sp

    WHERE [so].[type] = 'U'

    AND [sp].[modification_counter] > 0 AND ss.user_created = 0 AND [sp].[modification_counter]/[sp].[rows] > 0.10

    ORDER BY [sp].[last_updated] DESC;

    SELECT @rowId = MIN(rowId), @maxRowId = MAX(rowId) FROM @tbls;

    WHILE @rowId <= @maxRowId

    BEGIN

    SELECT @tblschema = tblschema, @tblname = tblname, @statname = statname

    FROM @tbls WHERE rowId = @rowId;

    SET @stmt = 'UPDATE STATISTICS [' + @tblschema + '].[' + @tblname + '] ' + @statname

    PRINT @stmt;

    EXECUTE (@stmt);

    SET @rowId = @rowId + 1

    END

    or is there is a difference between the two?

    I am unsure which one is best to use or is there no difference at all between the two bits of code?

    Thanks for any help in advance.

    Rob

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Use update statistics with the FULLSCAN option.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Rob Reid-246754

    Hall of Fame

    Points: 3901

    Hi

    Sorry, could you give me an example of what you mean.

    Is this inside the loop of statistics I am doing (with the EXEC) or is it something else e.g EXEC sp_updatestats with a parameter added to it?

    And what is the difference between the two?

    Thanks for your help.

    Rob

  • Rob Reid-246754

    Hall of Fame

    Points: 3901

    e.g like

    UPDATE STATISTICS [dbo].[tbl_TRAFFIC_VISITORS] _WA_Sys_0000000C_1DE57479 WITH FULLSCAN

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Rob Reid-246754 (4/21/2015)


    e.g like

    UPDATE STATISTICS [dbo].[tbl_TRAFFIC_VISITORS] _WA_Sys_0000000C_1DE57479 WITH FULLSCAN

    Yup, exactly like that, although you don't need to specify the stats name, specify the table name and all stats on that table will be done in one go.

    sp_updatestats uses a sampled update, which means the stats may not be as accurate as they could be.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Rob Reid-246754

    Hall of Fame

    Points: 3901

    Hi Gail

    So I guess I need to change my SQL for the loop then as it is showing multiple indexes / statistics for each table. I have tables in that loop appearing 10+ times.

    What should the SQL be for my loop?

    At the moment it is

    SELECT [sch].[name], [so].[name], [ss].[name] AS [TableName]

    FROM [sys].[stats] [ss]

    JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]

    JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]

    OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id], [ss].[stats_id]) sp

    WHERE [so].[type] = 'U'

    AND [sp].[modification_counter] > 0

    AND ss.user_created = 0

    AND [sp].[modification_counter]/[sp].[rows] > 0.10 -- is this the correct amount?

    ORDER BY [sp].[last_updated] DESC;

    Thanks for your help!

  • Rob Reid-246754

    Hall of Fame

    Points: 3901

    Don't worry I was being a numpty. I could just DISTINCT on table but I would like to know if the

    AND [sp].[modification_counter]/[sp].[rows] > 0.10

    is the right sum is your eyes?

  • free_mascot

    One Orange Chip

    Points: 27168

    As Gila has mention UPDATE STATISTICS with FULLSCAN is good. Refer following link for your reference:

    http://sqlperformance.com/2013/07/sql-statistics/statistics-updates

    https://msdn.microsoft.com/en-IN/library/ms187348.aspx

    https://msdn.microsoft.com/en-IN/library/ms173804.aspx

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Rob Reid-246754

    Hall of Fame

    Points: 3901

    Thanks

Viewing 9 posts - 1 through 9 (of 9 total)

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