Rebuilding Indexes with T-SQL

In my last position, I ended up writing a lot of T-SQL. I have recently taken on a new position and I am the company's sole DBA, so there is less T-SQL, but it certainly has not gone away completely. Case in point, SQL Server's maintenance plans are pretty cool and very simple to implement, but they are not bullet proof. Last week I kept getting an alert an index rebuild job had failed. I took a look at the maintenance plan, troubleshot it, and even rebuilt it but it continued to fail. I then decided to look at the underlying T-SQL associated w/ this job. It was failing on one particular index rebuild and succeeding on the rest of them. However, this still shows up in the job as a failure. I decided to script out the index rebuild and since then I have had no issues with the job. I opted to leave the CATCH block empty. Depending on how important the job is, it may be wise to have something in the CATCH that writes to a table so that one can be aware of exactly which index rebuilds failed.Additionally, I opted to sort in the tempdb as opposed to the DB itself. Once again, that one is your call. It will depend on how your environment is set up as to whether or not this is a good idea.

USE [DB_NAME_GOES_HERE]

DECLARE @SQL VARCHAR(3000)
, @START INT
, @LAST INT
, @TABLE_NAME VARCHAR(200)
, @IX_NAME VARCHAR(200)

CREATE TABLE #TEMP(ID INT IDENTITY(1,1), [TABLE_NAME] VARCHAR(200)
, [IX_NAME] VARCHAR(200))

INSERT #TEMP
SELECT '[' + S.NAME + '].[' + T.NAME + ']' AS [TABLE_NAME], I.NAME AS [IX_NAME]
FROM SYS.INDEXES I
INNER JOIN sys.tables T
ON I.object_id = T.object_id
INNER JOIN sys.schemas S
ON T.schema_id = S.schema_id
WHERE I.INDEX_ID > 0
ORDER BY T.NAME, I.NAME

SET @START = (SELECT MIN(ID) FROM #TEMP)
SET @LAST = (SELECT MAX(ID) FROM #TEMP)

WHILE @START <= @LAST
BEGIN

SET @TABLE_NAME = (SELECT [TABLE_NAME] FROM #TEMP WHERE ID = @START)
SET @IX_NAME = (SELECT [IX_NAME] FROM #TEMP WHERE ID = @START)

SET @SQL = ' BEGIN TRY
ALTER INDEX [' + @IX_NAME + '] ON ' + @TABLE_NAME + '
REBUILD WITH ( PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF
, SORT_IN_TEMPDB = ON )
END TRY

BEGIN CATCH ' +
-- If you want something to happen in the Catch block
-- , place that code here
' END CATCH'

EXEC (@SQL)

SET @START = @START + 1
END

DROP TABLE #TEMP

Great Quote of the Day

This concept is simple and primary, but often overlooked:
"...it is a good idea to create non-clustered indexes on all foreign keys. You will always run lots of queries, and also use the JOIN operator, based on your primary and foreign keys."
- Wagner Crivelini
From the article SQL and the JOIN Operator