Query para gerar índices nonclustered de tabelas replicadas

Tempo de leitura: 3 minutos

Falaaa Turminhaaa…

Tudo beleza?

Estamos de volta depois de um tempo sem postar no Blog =)

Recentemente, tivemos um caso bem especifico na Power Tuning de um cliente que sentiu problemas de performance em uma base replicada por “Replicação Transacional”.

Pretendo futuramente fazer uma série no Blog sobre Replicação, que particularmente é um assunto que gosto bastante..

Por padrão, os tipos de Replicação do SQL Server não levam os índices NON-CLUSTERED durante o envio das transações (dentre uma série de outras coisas)…

Neste artigo focaremos apenas nessa parte dos índices que foi o cenário na qual precisei atuar.

Existe uma configuração nas propriedades da “Publicação” na qual podemos habilitar para criar os índices NON-CLUSTERED no destino, porém, não considero muito vantajoso habilitar essa configuração por alguns motivos, tais como :

Ao habilitar essa configuração é necessário fazer um novo “Snapshot” da publicação para o SQL replicar os seus índices. Isto significa que você terá que gerar um novo “Snapshot” e praticamente recriar toda a sua replicação… Em um ambiente que replica tabelas pequenas, essa opção até vai bem… Mas em ambientes muito grandes, essa ação pode levar muito tempo, tornando esse processo muito oneroso e inviável.
Essa configuração não é mantida para novos índices, ou seja, ela só funciona no primeiro “Snapshot” . Se você criar um novo índice, este índice só será criado no seu “Assinante” se a configuração for marcada novamente e gerar um novo snapshot.

Enfim, neste cenário que atuamos o banco replicado já rodava por alguns anos e tinha um volume de dados considerável.. Preferimos por não recriar tudo novamente direto pelas configurações por questões estratégicas.

Como uma solução para isso, desenvolvi uma query para gerar o script dos index NON-CLUSTERED somente de tabelas replicadas, assim ficou muito mais fácil de levar apenas os índices pertencentes as tabelas da replicação para o “Assinante”.

Segue abaixo a consulta :

DECLARE @table_name NVARCHAR(128),
        @index_name NVARCHAR(128),
        @index_id INT,
        @index_script NVARCHAR(MAX),
        @schema_name NVARCHAR(200),
        @column_name NVARCHAR(MAX),
        @included_columns NVARCHAR(MAX),
        @validation_index NVARCHAR(MAX),
        @with_properties NVARCHAR(MAX);

DECLARE cursor_indexes CURSOR FOR
SELECT t.name AS table_name,
       i.name AS index_name,
       i.index_id,
       i.object_id,
       SCHEMA_NAME(t.schema_id) AS schema_name
FROM sys.tables t
    JOIN sys.indexes i
        ON t.object_id = i.object_id
WHERE i.type = 2
      AND i.is_primary_key = 0
      AND t.is_replicated = 1
      AND t.name NOT LIKE '%sys%'
ORDER BY t.name,
         i.index_id;

OPEN cursor_indexes;

FETCH NEXT FROM cursor_indexes
INTO @table_name,
     @index_name,
     @index_id,
     @index_script,
     @schema_name;

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @validation_index
        = N'IF NOT EXISTS (SELECT I.name FROM sys.indexes I JOIN sys.tables T ON T.Object_id = I.Object_id '
          + N'WHERE I.name = ''' + @index_name + N''' and T.name = ''' + @table_name + N''')';


    SET @index_script
        = N' CREATE NONCLUSTERED INDEX [' + @index_name + N'] ON [' + @schema_name + N'].[' + @table_name + N'] ';
    SET @column_name = N'';
    SET @included_columns = N'';

    SELECT @column_name = (STUFF(
                           (
                               SELECT ',' + '[' + c.name + ']'
                               FROM sys.columns c
                                   JOIN sys.index_columns ic
                                       ON c.object_id = ic.object_id
                                          AND c.column_id = ic.column_id
                               WHERE ic.object_id = OBJECT_ID(@schema_name+'.'+@table_name)
                                     AND ic.index_id = @index_id
                                     AND ic.is_included_column = 0
                               ORDER BY ic.index_column_id
                               FOR XML PATH('')
                           ),
                           1,
                           1,
                           '('
                                ) + ')'
                          );


    SELECT @included_columns =
    (
        SELECT 'INCLUDE ' + STUFF(
                            (
                                SELECT ',' + '[' + c.name + ']'
                                FROM sys.columns c
                                    JOIN sys.index_columns ic
                                        ON c.object_id = ic.object_id
                                           AND c.column_id = ic.column_id
                                WHERE ic.object_id = OBJECT_ID(@schema_name+'.'+@table_name)
                                      AND ic.index_id = @index_id
                                      AND ic.is_included_column = 1
                                ORDER BY ic.index_column_id
                                FOR XML PATH('')
                            ),
                            1,
                            1,
                            '('
                                 ) + ')'
    );

    --opção valida a partir do SQL Server 2016 SP2
    SET @with_properties = (' WITH (DATA_COMPRESSION = PAGE)');

    SET @index_script
        = @validation_index + CHAR(13) + ISNULL(@index_script,'') + ISNULL(@column_name, '') + ISNULL(@included_columns, '')
          +  @with_properties;

    PRINT @index_script;

    FETCH NEXT FROM cursor_indexes
    INTO @table_name,
         @index_name,
         @index_id,
         @index_script,
         @schema_name;


END;

CLOSE cursor_indexes;
DEALLOCATE cursor_indexes;

100

101

102

103

104

105

106

107

108

109

110

DECLARE @table_name NVARCHAR(128),

@index_name NVARCHAR(128),

@index_id INT,

@index_script NVARCHAR(MAX),

@schema_name NVARCHAR(200),

@column_name NVARCHAR(MAX),

@included_columns NVARCHAR(MAX),

@validation_index NVARCHAR(MAX),

@with_properties NVARCHAR(MAX);

DECLARE cursor_indexes CURSOR FOR

SELECT t.name AS table_name,

i.name AS index_name,

i.index_id,

i.object_id,

SCHEMA_NAME(t.schema_id) AS schema_name

FROM sys.tables t

JOIN sys.indexes i

ON t.object_id = i.object_id

WHERE i.type = 2

AND i.is_primary_key = 0

AND t.is_replicated = 1

AND t.name NOT LIKE '%sys%'

ORDER BY t.name,

i.index_id;

OPEN cursor_indexes;

FETCH NEXT FROM cursor_indexes

INTO @table_name,

@index_name,

@index_id,

@index_script,

@schema_name;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @validation_index

= N'IF NOT EXISTS (SELECT I.name FROM sys.indexes I JOIN sys.tables T ON T.Object_id = I.Object_id '

+ N'WHERE I.name = ''' + @index_name + N''' and T.name = ''' + @table_name + N''')';

SET @index_script

= N' CREATE NONCLUSTERED INDEX [' + @index_name + N'] ON [' + @schema_name + N'].[' + @table_name + N'] ';

SET @column_name = N'';

SET @included_columns = N'';

SELECT @column_name = (STUFF(

(

SELECT ',' + '[' + c.name + ']'

FROM sys.columns c

JOIN sys.index_columns ic

ON c.object_id = ic.object_id

AND c.column_id = ic.column_id

WHERE ic.object_id = OBJECT_ID(@schema_name+'.'+@table_name)

AND ic.index_id = @index_id

AND ic.is_included_column = 0

ORDER BY ic.index_column_id

FOR XML PATH('')

'('

) + ')'

);

SELECT @included_columns =

(

SELECT 'INCLUDE ' + STUFF(

(

SELECT ',' + '[' + c.name + ']'

FROM sys.columns c

JOIN sys.index_columns ic

ON c.object_id = ic.object_id

AND c.column_id = ic.column_id

WHERE ic.object_id = OBJECT_ID(@schema_name+'.'+@table_name)

AND ic.index_id = @index_id

AND ic.is_included_column = 1

ORDER BY ic.index_column_id

FOR XML PATH('')

'('

) + ')'

);

--opção valida a partir do SQL Server 2016 SP2

SET @with_properties = (' WITH (DATA_COMPRESSION = PAGE)');

SET @index_script

= @validation_index + CHAR(13) + ISNULL(@index_script,'') + ISNULL(@column_name, '') + ISNULL(@included_columns, '')

+ @with_properties;

PRINT @index_script;

FETCH NEXT FROM cursor_indexes

INTO @table_name,

@index_name,

@index_id,

@index_script,

@schema_name;

END;

CLOSE cursor_indexes;

DEALLOCATE cursor_indexes;

Ao rodar a query, teremos a sintaxe pronta para criação de todos os índices pertencentes a replicação, basta copiar e colar no seu banco “Assinante”.. Legal né?

É isso ai galerinha, espero que essa consulta possa ajuda-los no dia a dia se passarem por alguma situação semelhante.

Dúvidas, sugestões e correções serão sempre bem vindas..

Espero postar com mais frequência..

Um grande abraço a todos..

Fui.

Gustavo Larocca

Consultor SQL Server

assinante indices publicador replicação snapshot sqlserver

Deixe uma respostaCancelar resposta

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Query para gerar índices nonclustered de tabelas replicadas