Monthly Archives: May 2012

Defragment Indices

As records are added to or removed from a table, indices on the table will become fragmented, thus reducing their efficiency both in terms of disk usage and performance. This fragmentation is especially severe if a database is shrunk (one reason why a database should NOT be shrunk, barring exceptional circumstances). SQL Server ships with maintenance tasks that can be configured to defragment indices. However, there are situations when running a maintenance task is not practical, or a greater degree of customization is desired beyond what is configurable within the maintenance task. In such circumstances, a stored procedure can be used to manage the defragmentation.

The example procedure below is created within a maintenance schema named “mx”. Furthermore, it uses the system stored procedure sp_MSforeachdb to span all databases on a server when looking for indices to defragment; it only looks for user tables, even though it includes the system databases; it specifies a fragmentation threshold to qualify a table for defragmentation; and it uses the degree of fragmentation to decide whether to REORGANIZE or REBUILD the index.

-- Create a stored procedure to defragment indices
CREATE PROCEDURE [mx].[uspDefragmentIndices]
    -- Declare variables
        @DBName NVARCHAR(255)
        , @TableName NVARCHAR(255)
        , @SchemaName NVARCHAR(255)
        , @IndexName NVARCHAR(255)
        , @PctFrag DECIMAL
        , @Defrag NVARCHAR(MAX)
    -- If the temporary table already exists, drop it
    IF OBJECT_ID('tempdb..#FragTables') IS NOT NULL
        DROP TABLE #FragTables
    -- Create the temporary table
    CREATE TABLE #FragTables (
        DBName NVARCHAR(255)
        , TableName NVARCHAR(255)
        , SchemaName NVARCHAR(255)
        , IndexName NVARCHAR(255)
        , AvgFragment TINYINT)
    -- Insert fragmented indices into the temporary table
    EXECUTE sp_MSforeachdb
        'INSERT INTO #FragTables (
            , TableName
            , SchemaName
            , IndexName
            , AvgFragment)
            ''?'' AS DBName
            , t.Name AS TableName
            , sc.Name AS SchemaName
            , AS IndexName
            , s.avg_fragmentation_in_percent
                , NULL
                , NULL
                , NULL
                , ''Sampled'') AS s
            INNER JOIN ?.sys.indexes AS i
                ON s.Object_Id = i.Object_id
                AND s.Index_id = i.Index_id
            INNER JOIN ?.sys.tables AS t
                ON i.Object_id = t.Object_Id
            INNER JOIN ?.sys.schemas AS sc
                ON t.schema_id = sc.SCHEMA_ID
            s.avg_fragmentation_in_percent > 20
            AND t.TYPE = ''U''
    -- Declare a cursor to scroll through the fragmented indices
        , TableName
        , SchemaName
        , IndexName
        , AvgFragment
    FROM #FragTables
    -- Open the cursor
    OPEN cIndices;
    -- Populate the cursor
    FETCH NEXT FROM cIndices
    INTO @DBName, @TableName, @SchemaName, @IndexName, @PctFrag
    -- Scroll through the fragmented indices
        -- Begin building the TSQL statement to defragment the index
        SET @Defrag = N'ALTER INDEX [' + @IndexName
            + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] '
        -- Set the defrag type based on the degree of fragmentation
        IF @PctFrag <= 40.0
            SET @Defrag += 'REORGANIZE';
        ELSE IF @PctFrag > 40.0
            SET @Defrag += 'REBUILD';
        -- Display the TSQL statement that was built
        PRINT @Defrag;
        -- Execute the TSQL statement
        EXECUTE sp_executesql @Defrag;
        -- Populate the next record into the cursor
        FETCH NEXT FROM cIndices
        INTO @DBName, @TableName, @SchemaName, @IndexName, @PctFrag
    -- Close the cursor
    CLOSE cIndices;
    -- Release the cursor
    DEALLOCATE cIndices;
    -- Drop the temporary table
    IF OBJECT_ID('tempdb..#FragTables') IS NOT NULL
        DROP TABLE #FragTables

To execute this stored procedure, simply run the following TSQL command. Note that it does not matter from which database the stored procedure is run, as the procedure itself references all databases on the server. This can be modified, of course, by removing the code that captures the indices to be defragmented from within the sp_MSforeachdb stored procedure.

-- Defragment the indices

As this procedure runs, it will display a list of TSQL statements that were executed, giving you an overview of the state of indexes on the server prior to defragmentation. It is often worth saving this list along with other maintenance and change logs.


List all Databases on a Server

Occasionally it is useful to have a record set of all the databases on a server. Thankfully, there is an undocumented system stored procedure that can be used to return just that. The procedure has one required parameter: the command to execute for each database. The procedure also accepts a substitution element as a placeholder for the database name; by default the placeholder is a question mark (?) and can be used as such within the command text.

To demonstrate this stored procedure, execute the following TSQL statement from any connection on your SQL server. It will display the name of each database on the server.

-- Display the name of every database on the server
EXECUTE sp_msforeachdb 'PRINT ''?'';';

This example can easily be extended to create a table and populate it with database names. To do so, create the table in which you would like to store the database names, then replace the PRINT command with an INSERT INTO command referencing the table. To see that usage in action, refer to defragment indices. Also, why not replace the PRINT command with a BACKUP DATABASE command? See it in action here.

Maintenance Schema

As the first post in the “maintenance” section, why not start with an easy one: creation of a maintenance schema. Using a schema to group maintenance tasks allows you to easily assign elevated privileges to them via the controlling user(s) as well as simply ensuring they are displayed with like-purposed entities within SQL Server Management Studio (SSMS).

The basic syntax is simple: specify the database within which you would like to create the schema, then create the schema with the default owner. For this example, we will use a database named “DB” and assign the database owner “dbo” user as the owner.

-- Specify the database to use

-- Create the maintenance schema