none
Indices quando criar? RRS feed

  • Pergunta

  • Bom Dia,

    Localizei a seguinte query na internet com o objetivo de realizar a engenharia reversa de criação de novos índices necessários a melhoria de performance.

    -- Missing Index Script
    -- Original Author: Pinal Dave (C) 2011
    SELECT TOP 25
    dm_mid.database_id
    AS DatabaseID,
    dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
    dm_migs.last_user_seek AS Last_User_Seek,
    OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
    'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
    CASE
    WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
    ELSE ''
    END
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
    +
    ']'
    + ' ON ' + dm_mid.statement
    + ' (' + ISNULL (dm_mid.equality_columns,'')
    +
    CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
    '' END
    + ISNULL (dm_mid.inequality_columns, '')
    +
    ')'
    + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
    FROM sys.dm_db_missing_index_groups dm_mig
    INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
    ON dm_migs.group_handle = dm_mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details dm_mid
    ON dm_mig.index_handle = dm_mid.index_handle
    WHERE dm_mid.database_ID = DB_ID()
    ORDER BY Avg_Estimated_Impact DESC
    GO

    Porém não corro o risco de prejudicar a performance de inserts e updates causando locks no banco de dados?, o que seria pior.

    Indices nonclustered causam este problema? Ou somente indices cluster?

    Existe alguma query específica para medir se a tabela recebe muita escrita?

    Obs: Faço update statistcs e Rebuid online de 30 em 30 minutos em intervalos diferentes durante todo o dia.

    Grato,

    Maurício

    segunda-feira, 15 de outubro de 2012 13:38

Respostas

  • maumauboy,

    pode te ajudar sim. Mas pode não ser suficiente, pois muitas vezes a decisão de se criar um índice se torna até mesmo subjetiva. Ou seja, às vezes mesmo que uma tabela tenha muita operação de escrita você pode decidir criar determinado índice nela, por questões "táticas".

    Mas sim, toda informação que você resgatar pode ser importante na tomada de decisão.


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    • Marcado como Resposta Richard Juhasz sexta-feira, 18 de janeiro de 2013 17:06
    segunda-feira, 15 de outubro de 2012 16:18
  • Rapaz,

    eu uso essse script do Pinal dave, mas antes de criar os índices, veja com a sp_helpindex2 se já existem indices semelhantes na tabela.

    O ideal mesmo, é você capturar dados de alguns dias com o profiler e analisar com o programa CLEAR TRACE, nele você consegue ver quais consultas estão executando mais vezes (execution count) e quais fizeram mais leituras (reads), daí você consegue criar o índice.

    Uma dica, sempre coloque como chave do índice os campos da clausula where, e se possível, na clausula INCLUDE o que será retornado no select, só tome cuidado pra nao sair criando índices compostos pra tudo que é lado, em tabelas grandes isso pode custar muito !

    • Marcado como Resposta Richard Juhasz sexta-feira, 18 de janeiro de 2013 17:06
    quinta-feira, 18 de outubro de 2012 23:08
  • Meu caro,

    Sugiro que você crie índices estudando suas querys e execution plans. Criar índices se baseando em dados estatísticos ou no Profiler pode ser prejudicial, você pode ter um relatório utilizado somente pelo diretor financeiro uma vez por semana ou um execution plan que atualmente é bom pode passar a não ser tão bom. Criar índices também aumenta o tamanho da sua base e quanto mais "includes" você fizer, mais dados estará duplicando. Aumenta também o tempo necessário para manutenção pois o reindex terá mais trabalho pra fazer.

    Se quer melhorar a performance do seu servidor, procure as principais querys e analize seus execution plans para ver se há a necessidade de criar ou alterar um índice. Sugiro o livro do Fabiano Amorim "Complete Showplan Operators" pra te dar um entendimento bacana.

    Abs!


    Luiz Mercante
    MCITP SQL 2008 | MCTS SQL 2008 | MCTS Windows Apps | MCTS Windows Network | MCP 2003
    sqldicas@outlook.com
    http://sqldicas.com.br

    quarta-feira, 24 de outubro de 2012 14:55
    Moderador

Todas as Respostas

  • Sim, todo índice impacta nas operações de escrita.

    Toda informação que você inserir, atualizar ou remover da tabela precisará sofrer o mesmo impacto nos índices.

    Para saber se você cria ou não um índice não há muita mágica: analise as vantagens e desvantagens.

    Perguntas que você deve se fazer:

    "Esta consulta é executada muitas vezes?"

    "Tem alguém importante na empresa que execute esta consulta?" (Às vezes é um relatório que o presidente da empresa utiliza. Obviamente este precisará ser rápido.)

    Compare o tempo que demora as operações DML sem o índice e depois com o índice e veja se valerá à pena mantê-lo.

    Não há uma resposta simples à sua pergunta. Monitorar e monitorar, para se chegar a uma conclusão.


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    segunda-feira, 15 de outubro de 2012 13:54
  • Roberson,


    Obrigado por responder!


    Mudei a query abaixo colocando o número de escritas vs leituras para saber se tem muitas escritas, acha que isso me ajuda?

    -- Missing Index Script
    -- Original Author: Pinal Dave (C) 2011
    SELECT
    dm_mid.database_id AS DatabaseID,
    dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
    dm_migs.last_user_seek AS Last_User_Seek,
    OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
    (SELECT   SUM(user_lookups + user_scans + user_seeks) AS Reads  FROM sys.dm_db_index_usage_stats v  INNER JOIN sys.objects S ON S.object_id = v.object_id and v.object_id = dm_mid.OBJECT_ID  WHERE type = 'U'  GROUP BY S.name) as Reads,
    (SELECT  SUM(user_updates) AS Updates FROM sys.dm_db_index_usage_stats v  INNER JOIN sys.objects S ON S.object_id = v.object_id and v.object_id = dm_mid.OBJECT_ID  WHERE type = 'U'  GROUP BY S.name) as Writes,
    'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
    CASE
    WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
    ELSE ''
    END
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
    + ']'
    + ' ON ' + dm_mid.statement
    + ' (' + ISNULL (dm_mid.equality_columns,'')
    + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
    '' END
    + ISNULL (dm_mid.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
    FROM sys.dm_db_missing_index_groups dm_mig
    INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
    ON dm_migs.group_handle = dm_mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details dm_mid
    ON dm_mig.index_handle = dm_mid.index_handle
    WHERE dm_mid.database_ID = DB_ID()
    order by writes desc

    Exsite um percentual de escritas vs leituras, que possa me ajudar a tomar esta decisão?, se puder contribuir com uma query com o total de execuções me ajudará muito. Pois são muitas queries diferentes sendo executadas durante o dia. Gostaria de uma linha de atuação para isso mas não sei qual tomar.

    Grato,

    Maurício


    segunda-feira, 15 de outubro de 2012 15:05
  • maumauboy,

    pode te ajudar sim. Mas pode não ser suficiente, pois muitas vezes a decisão de se criar um índice se torna até mesmo subjetiva. Ou seja, às vezes mesmo que uma tabela tenha muita operação de escrita você pode decidir criar determinado índice nela, por questões "táticas".

    Mas sim, toda informação que você resgatar pode ser importante na tomada de decisão.


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    • Marcado como Resposta Richard Juhasz sexta-feira, 18 de janeiro de 2013 17:06
    segunda-feira, 15 de outubro de 2012 16:18
  • Rapaz,

    eu uso essse script do Pinal dave, mas antes de criar os índices, veja com a sp_helpindex2 se já existem indices semelhantes na tabela.

    O ideal mesmo, é você capturar dados de alguns dias com o profiler e analisar com o programa CLEAR TRACE, nele você consegue ver quais consultas estão executando mais vezes (execution count) e quais fizeram mais leituras (reads), daí você consegue criar o índice.

    Uma dica, sempre coloque como chave do índice os campos da clausula where, e se possível, na clausula INCLUDE o que será retornado no select, só tome cuidado pra nao sair criando índices compostos pra tudo que é lado, em tabelas grandes isso pode custar muito !

    • Marcado como Resposta Richard Juhasz sexta-feira, 18 de janeiro de 2013 17:06
    quinta-feira, 18 de outubro de 2012 23:08
  • Meu caro,

    Sugiro que você crie índices estudando suas querys e execution plans. Criar índices se baseando em dados estatísticos ou no Profiler pode ser prejudicial, você pode ter um relatório utilizado somente pelo diretor financeiro uma vez por semana ou um execution plan que atualmente é bom pode passar a não ser tão bom. Criar índices também aumenta o tamanho da sua base e quanto mais "includes" você fizer, mais dados estará duplicando. Aumenta também o tempo necessário para manutenção pois o reindex terá mais trabalho pra fazer.

    Se quer melhorar a performance do seu servidor, procure as principais querys e analize seus execution plans para ver se há a necessidade de criar ou alterar um índice. Sugiro o livro do Fabiano Amorim "Complete Showplan Operators" pra te dar um entendimento bacana.

    Abs!


    Luiz Mercante
    MCITP SQL 2008 | MCTS SQL 2008 | MCTS Windows Apps | MCTS Windows Network | MCP 2003
    sqldicas@outlook.com
    http://sqldicas.com.br

    quarta-feira, 24 de outubro de 2012 14:55
    Moderador