Finding fragmentation in all databases.

  • I am trying to search for fragmented indexes greater than 10 percent from all databases with the exception of the system tables using SQL Server 2012.

    I tried creating the following script as a sproc in the master folder but when I exec I get no results. I also tried creating a sproc with the same script in one of the user’s database folder, I get results from other database tables but it is not accurate. Where do I put the sproc to get accurate information from all the databases table indexes and do there need to be modification to the script? I am looking for something simple which can be easily modified later.

    I considered using MSforeachDB but hear it may skip some database.

    DECLARE @Database_id VARCHAR(255)

    DECLARE @name VARCHAR(255)

    DECLARE @cmd NVARCHAR(500)

    DECLARE @objectid int;

    DECLARE @indexid int;

    DECLARE @partitioncount bigint;

    DECLARE @schemaname nvarchar(130);

    DECLARE @objectname nvarchar(130);

    DECLARE @indexname nvarchar(130);

    DECLARE @partitionnum bigint;

    DECLARE @partitions bigint;

    DECLARE @fragPercent float;

    DECLARE @command nvarchar(4000);

    DECLARE @dbid smallint;

    DECLARE DatabaseCursor CURSOR FOR SELECT Database_id , name FROM sys.databases

    WHERE database_id > 4 -- exclude system databases

    ORDER BY 1

    OPEN DatabaseCursor

    WHILE (1=1)

    BEGIN

    FETCH NEXT FROM DatabaseCursor INTO @Database_id , @name

    IF @@FETCH_STATUS < 0 BREAK;

    Print N'Database ' + @name

    set @dbid = db_id(@name);

    select @name as Name,

    OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName

    ,OBJECT_NAME(I.OBJECT_ID) AS ObjectName ,I.NAME AS IndexName,

    avg_fragmentation_in_percent AS FragPercent, partition_number AS partitionNum

    INTO #work_to_do

    from sys.dm_db_index_physical_stats (5, null,null,null,'Limited') S

    Join sys.indexes I on

    s.OBJECT_ID = I.OBJECT_ID AND I.index_id = s.index_id

    WHERE avg_fragmentation_in_percent > 10.0 -- Allow limited fragmentation

    AND S.index_id > 0 -- Ignore heaps

    AND page_count > 25; -- Ignore small tables

    -- Declare the cursor for the list of partitions to be processed.

    DECLARE partitions CURSOR FOR SELECT SchemaName, ObjectName, IndexName, fragPercent, partitionNum FROM #work_to_do;

    -- Open the cursor.

    OPEN partitions;

    -- Loop through the partitions.

    WHILE (1=1)

    BEGIN

    FETCH NEXT

    FROM partitions

    INTO @SchemaName, @ObjectName, @IndexName, @fragPercent, @PartitionNum;

    IF @@FETCH_STATUS < 0 BREAK;

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

    IF @fragPercent < 30.0

    Begin

    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'

    Print '@command '+ @command;

    ENd

    IF @fragPercent >= 30.0

    Begin

    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'

    Print '@command '+ @command;

    end

    IF @partitioncount > 1

    Begin

    SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))

    Print '@command '+ @command;

    end

    --EXEC (@command);

    --PRINT N'Executed: ' + @command;

    END --end loop for indexes

    ---- Close and deallocate the cursor.

    CLOSE partitions;

    DEALLOCATE partitions;

    -- Drop the temporary table.

    DROP TABLE #work_to_do;

    END -- end loop for databases

    CLOSE DatabaseCursor

    DEALLOCATE DatabaseCursor

  • you are joining to sys.indexes which will cause the results to only be returned for the current execution database.

    You will need to run the same thing against each database. It doesn't really matter where you place the proc.

    Yes, msforeachdb can miss databases. You can create a cursor to loop through each database and execute a dynamic sql string.

    I am curious to know why you are ignoring heaps.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I am trying to defrag indexes on user databases. I don't believe heap tables have indexes.

  • I believe that a part of your problem is that you've hardcoded the database ID in your code at the following line.

    from sys.dm_db_index_physical_stats ([font="Arial Black"]5[/font], null,null,null,'Limited') S

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • vanessa4biz (2/16/2014)


    I am trying to defrag indexes on user databases. I don't believe heap tables have indexes.

    Heap tables can have indexes. Heaps can become fragmented too and should be given some TLC too.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Jeff Moden (2/16/2014)


    I believe that a part of your problem is that you've hardcoded the database ID in your code at the following line.

    from sys.dm_db_index_physical_stats ([font="Arial Black"]5[/font], null,null,null,'Limited') S

    Yeah, that would be a huge contributor too:w00t:

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • sorry about that I was testing the query earlier and I forgot to change the 5 to @dbid

Viewing 7 posts - 1 through 6 (of 6 total)

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