Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Rebuild all indexes on all tables in the SQL Server database

One of the key tasks of a DBA is to maintain the database indexes and make sure they are not fragmented. You can use a sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes based on fragmentation. However, sometimes we may need to rebuild all indexes on all tables in the database, especially if you have to change any index property such as fill factor, compression, etc.

For this task, I wrote a following script that accepts parameters to change the properties of the indexes, and dynamically generate and execute ALTER INDEX statements. This script is compatible with SQL Server 2005 and above versions.

Here is this script:

--/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--Arguments				Data Type				Description
--------------			------------			------------
--@FillFactor			[int]					Specifies a percentage that indicates how full the Database Engine should make the leaf level
--												of each index page during index creation or alteration. The valid inputs for this parameter
--												must be an integer value from 1 to 100 The default is 0.
--												For more information, see http://technet.microsoft.com/en-us/library/ms177459.aspx.

--@PadIndex				[varchar](3)			Specifies index padding. The PAD_INDEX option is useful only when FILLFACTOR is specified,
--												because PAD_INDEX uses the percentage specified by FILLFACTOR. If the percentage specified
--												for FILLFACTOR is not large enough to allow for one row, the Database Engine internally
--												overrides the percentage to allow for the minimum. The number of rows on an intermediate
--												index page is never less than two, regardless of how low the value of fillfactor. The valid
--												inputs for this parameter are ON or OFF. The default is OFF.
--												For more information, see http://technet.microsoft.com/en-us/library/ms188783.aspx.

--@SortInTempDB			[varchar](3)			Specifies whether to store temporary sort results in tempdb. The valid inputs for this
--												parameter are ON or OFF. The default is OFF.
--												For more information, see http://technet.microsoft.com/en-us/library/ms188281.aspx.

--@OnlineRebuild		[varchar](3)			Specifies whether underlying tables and associated indexes are available for queries and data
--												modification during the index operation. The valid inputs for this parameter are ON or OFF.
--												The default is OFF.
--												Note: Online index operations are only available in Enterprise edition of Microsoft
--														SQL Server 2005 and above.
--												For more information, see http://technet.microsoft.com/en-us/library/ms191261.aspx.

--@DataCompression		[varchar](4)			Specifies the data compression option for the specified index, partition number, or range of
--												partitions. The options  for this parameter are as follows:
--													> NONE - Index or specified partitions are not compressed.
--													> ROW  - Index or specified partitions are compressed by using row compression.
--													> PAGE - Index or specified partitions are compressed by using page compression.
--												The default is NONE.
--												Note: Data compression feature is only available in Enterprise edition of Microsoft
--														SQL Server 2005 and above.
--												For more information about compression, see http://technet.microsoft.com/en-us/library/cc280449.aspx.

--@MaxDOP				[int]					Overrides the max degree of parallelism configuration option for the duration of the index
--												operation. The valid input for this parameter can be between 0 and 64, but should not exceed
--												number of processors available to SQL Server.
--												For more information, see http://technet.microsoft.com/en-us/library/ms189094.aspx.
--- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*/

-- Ensure a USE <databasename> statement has been executed first.

SET NOCOUNT ON;

DECLARE  @Version							[numeric] (18, 10)
		,@SQLStatementID					[int]
		,@CurrentTSQLToExecute				[nvarchar](max)
		,@FillFactor						[int]		 = 100 -- Change if needed
		,@PadIndex							[varchar](3) = N'OFF' -- Change if needed
		,@SortInTempDB						[varchar](3) = N'OFF' -- Change if needed
		,@OnlineRebuild						[varchar](3) = N'OFF' -- Change if needed
		,@LOBCompaction						[varchar](3) = N'ON' -- Change if needed
		,@DataCompression					[varchar](4) = N'NONE' -- Change if needed
		,@MaxDOP							[int]		 = NULL -- Change if needed
		,@IncludeDataCompressionArgument	[char](1);

IF OBJECT_ID(N'TempDb.dbo.#Work_To_Do') IS NOT NULL
    DROP TABLE #Work_To_Do
CREATE TABLE #Work_To_Do
    (
      [sql_id] [int] IDENTITY(1, 1)
                     PRIMARY KEY ,
      [tsql_text] [varchar](1024) ,
      [completed] [bit]
    )

SET @Version = CAST(LEFT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)), CHARINDEX('.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - 1) + N'.' + REPLACE(RIGHT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)), LEN(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - CHARINDEX('.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)))), N'.', N'') AS [numeric](18, 10))

IF @DataCompression IN (N'PAGE', N'ROW', N'NONE')
	AND (
		@Version >= 10.0
		AND SERVERPROPERTY(N'EngineEdition') = 3
		)
BEGIN
	SET @IncludeDataCompressionArgument = N'Y'
END

IF @IncludeDataCompressionArgument IS NULL
BEGIN
	SET @IncludeDataCompressionArgument = N'N'
END

INSERT INTO #Work_To_Do ([tsql_text], [completed])
SELECT 'ALTER INDEX [' + i.[name] + '] ON' + SPACE(1) + QUOTENAME(t2.[TABLE_CATALOG]) + '.' + QUOTENAME(t2.[TABLE_SCHEMA]) + '.' + QUOTENAME(t2.[TABLE_NAME]) + SPACE(1) + 'REBUILD WITH (' + SPACE(1) + + CASE
		WHEN @PadIndex IS NULL
			THEN 'PAD_INDEX =' + SPACE(1) + CASE i.[is_padded]
					WHEN 1
						THEN 'ON'
					WHEN 0
						THEN 'OFF'
					END
		ELSE 'PAD_INDEX =' + SPACE(1) + @PadIndex
		END + CASE
		WHEN @FillFactor IS NULL
			THEN ', FILLFACTOR =' + SPACE(1) + CONVERT([varchar](3), REPLACE(i.[fill_factor], 0, 100))
		ELSE ', FILLFACTOR =' + SPACE(1) + CONVERT([varchar](3), @FillFactor)
		END + CASE
		WHEN @SortInTempDB IS NULL
			THEN ''
		ELSE ', SORT_IN_TEMPDB =' + SPACE(1) + @SortInTempDB
		END + CASE
		WHEN @OnlineRebuild IS NULL
			THEN ''
		ELSE ', ONLINE =' + SPACE(1) + @OnlineRebuild
		END + ', STATISTICS_NORECOMPUTE =' + SPACE(1) + CASE st.[no_recompute]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END + ', ALLOW_ROW_LOCKS =' + SPACE(1) + CASE i.[allow_row_locks]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END + ', ALLOW_PAGE_LOCKS =' + SPACE(1) + CASE i.[allow_page_locks]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END + CASE
		WHEN @IncludeDataCompressionArgument = N'Y'
			THEN CASE
					WHEN @DataCompression IS NULL
						THEN ''
					ELSE ', DATA_COMPRESSION =' + SPACE(1) + @DataCompression
					END
		ELSE ''
		END + CASE
		WHEN @MaxDop IS NULL
			THEN ''
		ELSE ', MAXDOP =' + SPACE(1) + CONVERT([varchar](2), @MaxDOP)
		END + SPACE(1) + ')'
	,0
FROM [sys].[tables] t1
INNER JOIN [sys].[indexes] i ON t1.[object_id] = i.[object_id]
	AND i.[index_id] > 0
	AND i.[type] IN (1, 2)
INNER JOIN [INFORMATION_SCHEMA].[TABLES] t2 ON t1.[name] = t2.[TABLE_NAME]
	AND t2.[TABLE_TYPE] = 'BASE TABLE'
INNER JOIN [sys].[stats] AS st WITH (NOLOCK) ON st.[object_id] = t1.[object_id]
	AND st.[name] = i.[name]

SELECT @SQLStatementID = MIN([sql_id])
FROM #Work_To_Do
WHERE [completed] = 0

WHILE @SQLStatementID IS NOT NULL
BEGIN
	SELECT @CurrentTSQLToExecute = [tsql_text]
	FROM #Work_To_Do
	WHERE [sql_id] = @SQLStatementID

	PRINT @CurrentTSQLToExecute

	EXEC [sys].[sp_executesql] @CurrentTSQLToExecute

	UPDATE #Work_To_Do
	SET [completed] = 1
	WHERE [sql_id] = @SQLStatementID

	SELECT @SQLStatementID = MIN([sql_id])
	FROM #Work_To_Do
	WHERE [completed] = 0
END

Comments

Leave a comment on the original post [basitaalishan.com, opens in a new window]

Loading comments...