Sql-server – Rebuilding indexes on a list of tables

sql serversql-server-2008-r2t-sql

I have a large number of tables that may change, may have infrequent changes in index, etc. I may well hand this off to somebody else and I don't want indexes to become overly fragmented, slowing the system to a crawl. So, I began hunting and found this:

http://sqlmag.com/querying/querying-sysindexes-system-table

With some changes to the "WHERE" criteria I was able to isolate my table of tables/indexes

SELECT USER_NAME(OBJECTPROPERTY(i.id, 'OwnerID')) OwnerName,
       OBJECT_NAME( i.id ) [TableName],
       i.name AS [IndexName],
       CASE INDEXPROPERTY( i.id , i.name , 'IsClustered')
             WHEN 1 THEN 'YES' ELSE 'NO' END [IsClustered],
       CASE INDEXPROPERTY(i.id, i.name, 'IsUnique')
                WHEN 1 THEN 'YES' ELSE 'NO' END [IsUnique],
      STATS_DATE( i.id , i.indid ) AS LastUpdatedDate
FROM sysindexes i
WHERE USER_NAME(OBJECTPROPERTY(i.id, 'OwnerID')) = 'dbo'
    and INDEXPROPERTY( i.id , i.name , 'IsClustered') = 1
ORDER BY OwnerName, TableName, IndexName

I know that I can loop through this result set using dynamic SQL and apply this:

ALTER INDEX Index_Name ON Table_Name REBUILD PARTITION = ALL WITH (
    PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )

Where "Index_Name" and "Table_Name" are substituted for each time the line is run, and while this seems fine and good I can't help but wonder if there is a better solution. Can I avoid cursors and dynamic SQL in this case?

Best Answer

(Edited to include earlier comments for clarity of the answer.)

A better plan for database maintenance is to implement Ola Hallengren's Maintenance Plan. This is free and can be downloaded from https://ola.hallengren.com

You can configure the settings to suit your needs. Brent Ozar suggests some minor parameter tweaks at: http://www.brentozar.com/archive/2014/12/tweaking-defaults-ola-hallengrens-maintenance-scripts/

Because you are using SQL Server Express, which has no SQL Server Agent, you will need to use an alternate approach to running those jobs.

If you go to Ola Hallengren's site, https://ola.hallengren.com/frequently-asked-questions.html, you can look at his answer:

Therefore, the execution of the stored procedures must be scheduled by using cmd files and Windows Scheduled Tasks. Follow these steps.

  1. Download MaintenanceSolution.sql.

  2. Execute MaintenanceSolution.sql. This script creates the stored procedures that you need.

  3. Create cmd files to execute the stored procedures; for example a script:

    sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt

  4. In Windows Scheduled Tasks, create tasks to call the cmd files.

  5. Schedule the tasks.

  6. Start the tasks and verify that they are completing successfully.

Mladen Prajdić also wrote a solution some years ago using Service Broker, which is available in SQL Server Express. See the links at:

If you like that solution then you should be able to keep all the configuration inside the SQL Server.

Related Question