[dba-SQLServer] Very strange behavior

Arthur... I have built my defrag indexes scripts for Sql 2005 following this
model... that I wrote up on my blog
http://sqlthis.blogspot.com/2005/04/defrag-those-indexes-maintenance.html

the initial simply finds the fragmented indexes... I have a much more
complete script that I can post later today, but here is the basic portion
on the defrag section... in where I simply defrag...



SET NOCOUNT ON

DECLARE @TableName AS VARCHAR(500),
        @TableOwner AS VARCHAR(500),
        @Rows AS BIGINT,
        @aAvg AS DECIMAL(10,3),
        @SQL AS VARCHAR (1000)

WHILE EXISTS(SELECT * FROM db_defrag)
BEGIN
    SELECT TOP 1
        @TableOwner = TableOwner,
        @TableName = TableName --TableName,
AVG(avg_fragmentation_in_percent)aAvg, AVG(ROWS) aRows,
        , at Rows = AVG(ROWS), @aAvg = AVG([avg_fragmentation_in_percent])
        FROM db_defrag
    GROUP BY TableOwner, TableName
    ORDER BY AVG(ROWS) DESC

    SET @SQL = 'BEGIN TRY ' +
    'ALTER INDEX ALL ON ' + @TableOwner + '.[' + @TableName + '] REBUILD
WITH(MAXDOP=2, ONLINE=ON)'
    + ' END TRY '  +
    + ' BEGIN CATCH '
    + 'ALTER INDEX ALL ON ' + @TableOwner + '.[' + @TableName + '] REBUILD
WITH(MAXDOP=2, ONLINE=OFF)'
    + ' END CATCH '

    IF GETDATE() <  '5/4/08 23:59'--CONVERT(VARCHAR(12), GETDATE(), 110) +
' 22:00'
    BEGIN
        PRINT 'Defragging TABLE ' + @TableName
        EXECUTE(@SQL)
        PRINT 'Clearing from db_defrag table'
        DELETE FROM db_defrag WHERE TableName = @TableName
        INSERT INTO db_defrag_complete(TableOwner, TableName, ROWS)
VALUES(@TableOwner, @TableName, @Rows)
        SET @SQL = 'UPDATE STATISTICS ' + @TableOwner + '.' + @TableName
        EXEC(@SQL)
    END
    ELSE
    BEGIN
        PRINT 'Skipping TABLE ' + @TableName
        DELETE FROM db_defrag WHERE TableName = @TableName
        INSERT INTO db_defrag_skipped(TableOwner, TableName, ROWS,
[avg_fragmentation_in_percent]) VALUES(@TableOwner, @TableName, @Rows,
@aAvg)
    END

END

-Francisco
http://sqlthis.blogspot.com | Tsql and More...


On Wed, Sep 10, 2008 at 9:14 PM, Arthur Fuller <fuller.artful at gmail.com>wrote:

> In the simple case of Northwind you are correct, but try it against
> Adventureworks and you will see the problem. Try running these statements:
>
>
> EXEC
> sp_MSForeachtable ('ALTER INDEX ALL ON ' + schema_name() + '.' ? + 'REBUILD
> WITH ( PAD_INDEX = OFF, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON
> )')
>
> EXEC
> sp_MSForeachtable ('ALTER INDEX ALL ON ? REBUILD WITH ( PAD_INDEX = OFF,
> SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON )')
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>