Automate rebuilding of Indexes in SQL Server Express 2005

Question

My team is working on a .NET web application whose data storage is managed through SQL Server 2005 Express. Is it possible to automate a task in the database so that it rebuilds certain indexes automatically on an interval following deployment/install? If so, how is this achieved? I thought it would be under tasks for the database, but I do not see it.

Generally in SQL Server you might use a maintenance plan scheduled as a SQL Server Agent job for this. Express doesn't come with SQL Agent though so you will need to use the Windows Scheduler. — Martin Smith, Dec 12 '12 at 13:56
Ah, thank you for the info. Is Windows Scheduler something that can be packaged and auto-configured on startup/install? — Christian, Dec 12 '12 at 14:12

score 0 · Answer 1 · answered Apr 04 '13 at 06:08

When I service my databases, this script helps me remove index’s fragmentation. Perhaps, it will be useful to you:

SET NOCOUNT ON;

DECLARE 
      @SQL NVARCHAR(MAX)
    , @IndexName SYSNAME
    , @Output VARCHAR(200)
    , @ServerVersion VARCHAR(100)

SELECT @ServerVersion = CAST(SERVERPROPERTY('Edition') AS VARCHAR(100))

DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
    SELECT
        'ALTER INDEX [' + ix.name + N'] ON [' + SCHEMA_NAME(t.[schema_id]) + '].[' + t.name + '] ' + 
        CASE
            WHEN ps.avg_fragmentation_in_percent > 50 THEN
                CASE WHEN @ServerVersion LIKE 'Enterprise%' OR @ServerVersion LIKE 'Developer%' THEN
                  'REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON ' + CASE WHEN ix.fill_factor > 0 THEN ', FILLFACTOR = ' + CAST(ix.fill_factor AS VARCHAR(3)) ELSE '' END + ') '
                ELSE
                  'REBUILD WITH (SORT_IN_TEMPDB = ON' + CASE WHEN ix.fill_factor > 0 THEN ', FILLFACTOR = ' + CAST(ix.fill_factor AS VARCHAR(3)) ELSE '' END + ') '
            END
            ELSE 'REORGANIZE ' 
        END + 
        CASE
            WHEN ps.partition_number > 1 THEN N' PARTITION = ' + CAST(ps.partition_number AS NVARCHAR(MAX))
            ELSE N''
        END + ';', ix.name
    FROM sys.indexes ix
    JOIN sys.objects t ON t.[object_id] = ix.[object_id]
    JOIN (
        SELECT 
              [object_id]
            , index_id
            , avg_fragmentation_in_percent
            , partition_number
        FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED')
        WHERE page_count > 100
            AND avg_fragmentation_in_percent > 10
    ) ps ON t.[object_id] = ps.[object_id] AND ix.index_id = ps.index_id

OPEN cur

FETCH NEXT FROM cur INTO @SQL, @IndexName

WHILE @@FETCH_STATUS = 0 BEGIN

    SELECT @Output = CONVERT(NVARCHAR(15), GETDATE(), 114) + ': ' + @IndexName
    RAISERROR(@Output, 0, 1) WITH NOWAIT

    EXEC (@SQL)

    FETCH NEXT FROM cur INTO @SQL, @IndexName

END 

CLOSE cur 
DEALLOCATE cur

Automate rebuilding of Indexes in SQL Server Express 2005

1 Answers1