3

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?

  • 2
    Yes, the better plan is to implement Ola Hallengren's Maintenance Plan. ola.hallengren.com You can configure settings to suit your needs. Brent Ozar suggests some parameter tweaks at: brentozar.com/archive/2014/12/… – RLF Dec 24 '14 at 18:13
  • @RLF After some poking through I found references to SQL Server Agent. I have the Express version, can I still use your suggested solution? *tags updated – n8. Dec 24 '14 at 18:42
4

(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.

  • I've scheduled scripts in the past, but this solution is somewhat dense so it's really helpful that you pointed out the FAQ and detailed the steps, thank you. For now it's almost quitting time, Merry Christmas Eve! – n8. Dec 24 '14 at 19:10
  • have a +1 for a great answer - Merry Christmas! – Max Vernon Dec 24 '14 at 19:12
2

Clearly, Ola Hallengren's solution mentioned by @RLK in his comment is the way to go.

Having said that, I just wanted to show you a simple way to create the ALTER INDEX statements without the need for a cursor.

DECLARE @cmd VARCHAR(MAX);
SET @cmd = '';

SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) + 'GO' +
    CHAR(13) + CHAR(10) END + 
    'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + USER_NAME(OBJECTPROPERTY(i.id, 
    'OwnerID')) + '.' + QUOTENAME(OBJECT_NAME(i.id)) + ' REBUILD PARTITION = ALL WITH (
    PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF );'
FROM sysindexes i
WHERE USER_NAME(OBJECTPROPERTY(i.id, 'OwnerID')) = 'dbo'
    and INDEXPROPERTY( i.id , i.name , 'IsClustered') = 1;

PRINT @cmd;

Instead of the PRINT @cmd;, you could do EXEC sp_executesql @cmd; to actually run the ALTER INDEX statements.

The T-SQL above is actually not going to work all that well as it is presently since it does not exclude things like service-broker queues, etc. You'd be far better off to go with a proven solution rather than building one yourself.

Since you have SQL Server Express, which doesn't include the SQL Server Agent component, you'll want to schedule this through some other process, such as the Windows Scheduler. You'll need to save the above T-SQL into a text file, and pass it to the command line tool, SQLCMD.exe with appropriate parameters for your environment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.