Self explanatory.
Rebuilding Stats: Twice or Not At All
Are you rebuilding statistics twice on your tables? Longtime expert, Andy Warren, shows why you might be asking more work of your SQL Server than is required.
2008-07-29
9,129 reads
Self explanatory.
--------------------------------------------------------------------------- -- Moving non-clustered indexes from one filegroup to another -- SQL Server 2005/2008 version -- By Gregory Shadur (TheMOTU) -- greg_shadur@hotmail.com -- -- Execute the script in the context of a specific database -- Generates DROP/CREATE INDEX script --------------------------------------------------------------------------- ET NOCOUNT ON DECLARE @table_schema_name nvarchar(128), @table_name nvarchar(128), @index_name nvarchar(128), @key_ordinal smallint, @column_name nvarchar(128), @sort_order varchar(50), @is_included_column int, @index_type_desc varchar(50), @unique_type varchar(50), @ignore_dup_key varchar(50), @fill_factor tinyint, @is_padded varchar(50), @allow_row_locks varchar(50), @allow_page_locks varchar(50), ------------------------------------------- @table_schema_name_ws nvarchar(128), @table_name_ws nvarchar(128), @index_name_ws nvarchar(128), @key_ordinal_ws smallint, ------------------------------------------- @text1_create varchar(800), @text1_create_columns varchar(800), @text1_create_end varchar(50), @text2_include_start varchar(800), @text2_include_columns varchar(50), @text2_include_end varchar(50), @text4_with varchar(800), @text4_with_end varchar(50) SET @text1_create_end = ')' SET @text2_include_start = 'INCLUDE (' SET @text2_include_end = ')' SET @text4_with_end = ')' DECLARE C01 CURSOR FOR SELECT S.name AS table_schema_name, O.name AS table_name, I.name AS index_name, CASE IC.key_ordinal WHEN 0 THEN 999 ELSE IC.key_ordinal END AS key_ordinal, C.name AS column_name, CASE IC.is_descending_key WHEN 0 THEN 'ASC' ELSE 'DESC' END AS sort_order, IC.is_included_column, I.type_desc AS index_type_desc, CASE I.is_unique WHEN 1 THEN 'UNIQUE' ELSE '' END AS unique_type, CASE I.ignore_dup_key WHEN 1 THEN 'ON' ELSE 'OFF' END AS ignore_dup_key, I.fill_factor, CASE I.is_padded WHEN 1 THEN 'ON' ELSE 'OFF' END AS is_padded, CASE I.allow_row_locks WHEN 1 THEN 'ON' ELSE 'OFF' END AS allow_row_locks, CASE I.allow_page_locks WHEN 1 THEN 'ON' ELSE 'OFF' END AS allow_page_locks FROM sys.indexes I INNER JOIN sys.objects O ON O.object_id = I.object_id AND O.type_desc = 'USER_TABLE' INNER JOIN sys.schemas S ON S.schema_id = O.schema_id INNER JOIN sys.data_spaces D ON D.data_space_id = I.data_space_id AND D.name = 'PRIMARY' -- Old filrgroup name INNER JOIN sys.index_columns IC ON IC.object_id = I.object_id AND IC.index_id = I.index_id INNER JOIN sys.columns C ON C.object_id = IC.object_id AND C.column_id = IC.column_id WHERE I.type_desc IN ('NONCLUSTERED') AND I.is_hypothetical = 0 AND I.is_primary_key = 0 -- Exclude primary key constraints AND I.is_unique_constraint = 0 -- Exclude unique constraints AND I.is_disabled <> 1 -- Exclude disabled indexes ORDER BY 1, 2, 3, 4 OPEN C01 FETCH C01 INTO @table_schema_name, @table_name, @index_name, @key_ordinal, @column_name, @sort_order, @is_included_column, @index_type_desc, @unique_type, @ignore_dup_key, @fill_factor, @is_padded, @allow_row_locks, @allow_page_locks SELECT @table_schema_name_ws = @table_schema_name SELECT @table_name_ws = @table_name SELECT @index_name_ws = @index_name WHILE @@FETCH_STATUS = 0 BEGIN WHILE @@FETCH_STATUS = 0 AND @table_schema_name_ws = @table_schema_name BEGIN WHILE @@FETCH_STATUS = 0 AND @table_schema_name_ws = @table_schema_name AND @table_name_ws = @table_name BEGIN SET @text1_create_columns = '' SET @text2_include_columns = '' PRINT 'DROP INDEX [' + @index_name_ws + '] ON [' + @table_schema_name_ws + '].[' + @table_name_ws + '];' PRINT ' ' SELECT @text1_create = 'CREATE ' + @unique_type + ' ' + @index_type_desc + ' INDEX [' + @index_name_ws + '] ON [' + @table_schema_name_ws + '].[' + @table_name_ws + '] (' SELECT @text4_with = 'WITH (PAD_INDEX = ' + @is_padded + ', STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, ' + 'IGNORE_DUP_KEY = ' + @ignore_dup_key + ', ONLINE = OFF, ' + 'FILLFACTOR = ' + CAST(@fill_factor AS VARCHAR(50)) + ', ' + 'ALLOW_ROW_LOCKS = ' + @allow_row_locks + ', ' + 'ALLOW_PAGE_LOCKS = ' + @allow_page_locks WHILE @@FETCH_STATUS = 0 AND @table_schema_name_ws = @table_schema_name AND @table_name_ws = @table_name AND @index_name_ws = @index_name BEGIN IF @is_included_column = 1 BEGIN SELECT @text2_include_columns = @text2_include_columns + @column_name + ',' END ELSE BEGIN SELECT @text1_create_columns = @text1_create_columns + @column_name + ',' END FETCH C01 INTO @table_schema_name, @table_name, @index_name, @key_ordinal, @column_name, @sort_order, @is_included_column, @index_type_desc, @unique_type, @ignore_dup_key, @fill_factor, @is_padded, @allow_row_locks, @allow_page_locks END IF @@FETCH_STATUS = 0 SELECT @index_name_ws = @index_name SELECT @text1_create_columns = ' ' + SUBSTRING(@text1_create_columns, 1, (DATALENGTH(@text1_create_columns) - 1)) PRINT @text1_create PRINT @text1_create_columns PRINT @text1_create_end IF DATALENGTH(@text2_include_columns) > 0 BEGIN PRINT @text2_include_start PRINT ' ' + SUBSTRING(@text2_include_columns, 1, (DATALENGTH(@text2_include_columns) - 1)) PRINT @text2_include_end END PRINT @text4_with PRINT @text4_with_end PRINT 'ON INDEX;' -- New filegroup name PRINT '----' END IF @@FETCH_STATUS = 0 SELECT @table_name_ws = @table_name END IF @@FETCH_STATUS = 0 SELECT @table_schema_name_ws = @table_schema_name END CLOSE C01 DEALLOCATE C01