Moving Indexes

  • Comments posted to this topic are about the item Moving Indexes

    Thom Bolin - MCITP SQLServer 2005
    NTXDBA Blog[/url]

  • As a new author you have done a very good job. Good article.

  • Great job! I like how you break down each part of the script with brief descriptions.

  • Awesome job Tom.

    I however have a suggestion for all those articles with 15 different code snippets. Could you include a download link where we can get the whole thing with a single copy / paste?

    It would save a lot of time and garantee that we don't screw it up in the process.

  • Get job...

    I agree with Ninja's_RGR'us about a single file to download.

    Thomas

    Thomas LeBlanc, MVP Data Platform Consultant

  • Files are now available for download at the bottom of the article, sorry for the delay.

    Thom Bolin - MCITP SQLServer 2005
    NTXDBA Blog[/url]

  • Thom (9/15/2008)


    Files are now available for download at the bottom of the article, sorry for the delay.

    NP, that was faster than the usual poster :D.

  • Great article. I had an index move task in last week of this month and definitely try your scripts on testing env. and let you know results, how it went through.

    SQL DBA.

  • The complete version for SQL2005/2008 is below. It includes schema... plenty of monkeys use multiple schemas..

    Enjoy.

    SET 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'

    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

    ANDI.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;'

    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

  • There's something I noticed while testing the script, but it seems like the scripts seems to be seeing the statistics as indexes. I don't know if this is the correct approach so i decided to add the next line to the first query.

    and dbo.sysindexes.name not like '_WA_Sys_%'

    Thanks for it by the way. I've been trying to do this for some time but never got the time for it 🙁

  • I think this wipes out the stats : AND I.is_hypothetical = 0

    I'm not sure though, I have not done such tasks in a very long while.

    Check out INDEX_PROPERTIES as well.

  • Stats? I think it would be a good practice to update stats every so often. This is outside the scope of moving indexes... Mmmm?

  • My tests do not show any _WA_ :w00t:

  • I have to say as a novice in the DBA continuum... I really appreciate the detailed comments surrounding the code snippets explaining why each piece was done this way (specifically the WHERE clauses with the system table columns). Frequently, I get bogged down in the details of included code as I am digging through BOL to figure out why someone included specific criteria.

    Great info - thanks!

    J.

  • Excellent article, Thom! I love working with system tables, they hold so much cool information and really show how SQL Server is a meta-data system. Conveniently, I think I'll have an index move in the near future, so your code will really come in handy.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply