none
Engine Tuning Advisor é 100% confiavel ? RRS feed

  • Pergunta

  • Pessoal,

    Estava com um problema em uma determinada query, e usei o DTA para auxiliar na performance, ele sugeriu alguns indices e estastiticas adicionais, e eu fui nas sugestões, criei todos, e realmente melhoro a performance, mas a pergunta é a seguinte:

    Sempre que o DTA fizer sugestões, posso criar os indices e estatiticas sem medo ??

    sexta-feira, 15 de junho de 2012 20:26

Respostas

  • DBA_BH.... Poder.... não pode... Não dá para criar todos os índices sugeridos, é uma ferramenta que ajuda demais, mas nunca é 100%, nesse caso, ela está analisando somente uma query e você tem que levar em conta outros fatores como Inserts, Updates e Deletes da tabela antes de criar um índice que pode mais atrapalhar do que ajudar. O ideal é você fazer uma "faxina" periódica nos índices, ver quais estão valendo a pena e quais não, analisar a necessidade de novos, etc.

    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco

    sexta-feira, 15 de junho de 2012 20:43
  • Sim, pode... como o banco é mais pesquisa do que IUD não vai fazer tão mal, só se lembre que dependendo do tamanho da tabela e do índice, o arquivo de bd pode crescer consideravelmente. Mas voltando a relação Reads x Writes, eu normalmente uso essas duas querys:

    A primeira me dá uma noção (utilizando os índices já existentes) de como se comporta na média a relação de Reads e Writes

    SELECT
        CAST(SUM(user_seeks + user_scans + user_lookups) AS decimal) / CAST(SUM(user_updates) + SUM(user_seeks + user_scans + user_lookups) AS decimal) * 100 AS ReadPercent ,
        CAST(SUM(user_updates) AS decimal) / CAST(SUM(user_updates) + SUM(user_seeks + user_scans + user_lookups) AS decimal) * 100 AS WriteRatio
    FROM
        sys.dm_db_index_usage_stats

    E para cada índice individualmente, no caso abaixo, eu mostro todos os índices que tem uma média menor que 20% de leitura e 80% de IUD, é uma média que eu cheguei para os bancos em que trabalhava, todos os índices dessa lista eu desabilitava. Mas esse valor varia de banco para banco e não tem uma regra geral, vai do bom senso.

    With ctIndices as (
    	SELECT
         OBJECT_NAME(a.object_id) AS TableName,
         c.name AS IndexName ,
         ( SELECT
               used / 128
           FROM
               sysindexes b
           WHERE
               b.id = a.object_id AND b.name = c.name AND c.index_id = b.indid ) AS Size_MB,
         ( a.user_seeks + a.user_scans + a.user_lookups ) AS Hits,
         CAST(CASE WHEN ( a.user_seeks + a.user_scans + a.user_lookups ) = 0 THEN 0
                   ELSE CONVERT(real , ( a.user_seeks + a.user_scans + a.user_lookups )) * 100 / CASE( a.user_seeks + a.user_scans + a.user_lookups + a.user_updates )
                                                                                                   WHEN 0 THEN 1
                                                                                                   ELSE CONVERT(real , ( a.user_seeks + a.user_scans + a.user_lookups + a.user_updates ))
                                                                                                 END
              END AS decimal(18,2)) ReadRatio ,
         CAST(CASE WHEN a.user_updates = 0 THEN 0
                   ELSE CONVERT(real , a.user_updates) * 100 / CASE( a.user_seeks + a.user_scans + a.user_lookups + a.user_updates )
                                                                 WHEN 0 THEN 1
                                                                 ELSE CONVERT(real , ( a.user_seeks + a.user_scans + a.user_lookups + a.user_updates ))
                                                               END
              END AS decimal(18,2)) AS [WriteRatio] ,
         a.user_updates AS Updates, 
         a.last_user_update AS Update_Date,
         a.user_seeks , a.user_scans , a.user_lookups , a.user_updates
     FROM
         sys.dm_db_index_usage_stats a JOIN sysobjects AS o
             ON ( a.OBJECT_ID = o.id ) JOIN sys.indexes AS c
             ON ( a.OBJECT_ID = c.OBJECT_ID AND a.index_id = c.index_id )
     WHERE
         o.type = 'U'			-- exclude system tables
         AND c.is_unique = 0			-- no unique indexes
         AND c.type = 2				-- nonclustered indexes only
         AND c.is_primary_key = 0		-- no primary keys
         AND c.is_unique_constraint = 0		-- no unique constraints
         AND c.is_disabled = 0			-- only active indexes
         AND a.database_id = DB_id()           -- for current database only
    )
    Select * 
      from ctIndices
     Where ReadRatio < 20.00
    Order by ReadRatio ASC, IndexName
    

     

    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco

    sexta-feira, 15 de junho de 2012 20:55
  • Fausto,

    EXCELENTEEEE !!!!Ajudou 100% !!!

    Segue os resultados:

    1ª Query:

    ReadPercent: 99,16%

    WriteRadio: 0,83%

    Isto mostra que a maioria dos indices estao sendo usados para consultas, certo??

    No caso da segunda query, foram listados 22 indices.

    Todos deram Read 0% e alguns deram write 100. Devo desabilitar estes indices certo?

    O banco não possui um plano de manutençao desses indices, eu estava pensando em criar um plano fazendo um rebuild dos indices, mas nesses casos, os indices desabilitados voltariam a ativa. Como devo fazer ???

    Você sabe me dizer uma query que lista a quantidade de indice do banco ou de cada tabela ???

    No seu outro tópico de Fill Factor, eu passei um link que tem uma rotina para rebuild com calculo de Fill Factor... Essa rotina faz o rebuild índice por índice e no cursor "curIndices" ele lista somente os índices habilitados, você pode adaptar a rotina para fazer somente o rebuild sem levar em conta o Fill Factor


    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco

    segunda-feira, 18 de junho de 2012 12:23

Todas as Respostas

  • DBA_BH.... Poder.... não pode... Não dá para criar todos os índices sugeridos, é uma ferramenta que ajuda demais, mas nunca é 100%, nesse caso, ela está analisando somente uma query e você tem que levar em conta outros fatores como Inserts, Updates e Deletes da tabela antes de criar um índice que pode mais atrapalhar do que ajudar. O ideal é você fazer uma "faxina" periódica nos índices, ver quais estão valendo a pena e quais não, analisar a necessidade de novos, etc.

    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco

    sexta-feira, 15 de junho de 2012 20:43
  • Fausto,

    Eu fui na ideia no DTA pois o ambiente não é um ambiente tão transacional, é um ambiente voltado mais para consultas e inserts e updates não são tao frequentes....  Neste caso posso confiar ??

    sexta-feira, 15 de junho de 2012 20:45
  • Sim, pode... como o banco é mais pesquisa do que IUD não vai fazer tão mal, só se lembre que dependendo do tamanho da tabela e do índice, o arquivo de bd pode crescer consideravelmente. Mas voltando a relação Reads x Writes, eu normalmente uso essas duas querys:

    A primeira me dá uma noção (utilizando os índices já existentes) de como se comporta na média a relação de Reads e Writes

    SELECT
        CAST(SUM(user_seeks + user_scans + user_lookups) AS decimal) / CAST(SUM(user_updates) + SUM(user_seeks + user_scans + user_lookups) AS decimal) * 100 AS ReadPercent ,
        CAST(SUM(user_updates) AS decimal) / CAST(SUM(user_updates) + SUM(user_seeks + user_scans + user_lookups) AS decimal) * 100 AS WriteRatio
    FROM
        sys.dm_db_index_usage_stats

    E para cada índice individualmente, no caso abaixo, eu mostro todos os índices que tem uma média menor que 20% de leitura e 80% de IUD, é uma média que eu cheguei para os bancos em que trabalhava, todos os índices dessa lista eu desabilitava. Mas esse valor varia de banco para banco e não tem uma regra geral, vai do bom senso.

    With ctIndices as (
    	SELECT
         OBJECT_NAME(a.object_id) AS TableName,
         c.name AS IndexName ,
         ( SELECT
               used / 128
           FROM
               sysindexes b
           WHERE
               b.id = a.object_id AND b.name = c.name AND c.index_id = b.indid ) AS Size_MB,
         ( a.user_seeks + a.user_scans + a.user_lookups ) AS Hits,
         CAST(CASE WHEN ( a.user_seeks + a.user_scans + a.user_lookups ) = 0 THEN 0
                   ELSE CONVERT(real , ( a.user_seeks + a.user_scans + a.user_lookups )) * 100 / CASE( a.user_seeks + a.user_scans + a.user_lookups + a.user_updates )
                                                                                                   WHEN 0 THEN 1
                                                                                                   ELSE CONVERT(real , ( a.user_seeks + a.user_scans + a.user_lookups + a.user_updates ))
                                                                                                 END
              END AS decimal(18,2)) ReadRatio ,
         CAST(CASE WHEN a.user_updates = 0 THEN 0
                   ELSE CONVERT(real , a.user_updates) * 100 / CASE( a.user_seeks + a.user_scans + a.user_lookups + a.user_updates )
                                                                 WHEN 0 THEN 1
                                                                 ELSE CONVERT(real , ( a.user_seeks + a.user_scans + a.user_lookups + a.user_updates ))
                                                               END
              END AS decimal(18,2)) AS [WriteRatio] ,
         a.user_updates AS Updates, 
         a.last_user_update AS Update_Date,
         a.user_seeks , a.user_scans , a.user_lookups , a.user_updates
     FROM
         sys.dm_db_index_usage_stats a JOIN sysobjects AS o
             ON ( a.OBJECT_ID = o.id ) JOIN sys.indexes AS c
             ON ( a.OBJECT_ID = c.OBJECT_ID AND a.index_id = c.index_id )
     WHERE
         o.type = 'U'			-- exclude system tables
         AND c.is_unique = 0			-- no unique indexes
         AND c.type = 2				-- nonclustered indexes only
         AND c.is_primary_key = 0		-- no primary keys
         AND c.is_unique_constraint = 0		-- no unique constraints
         AND c.is_disabled = 0			-- only active indexes
         AND a.database_id = DB_id()           -- for current database only
    )
    Select * 
      from ctIndices
     Where ReadRatio < 20.00
    Order by ReadRatio ASC, IndexName
    

     

    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco

    sexta-feira, 15 de junho de 2012 20:55
  • Fausto,

    EXCELENTEEEE !!!!Ajudou 100% !!!

    Segue os resultados:

    1ª Query:

    ReadPercent: 99,16%

    WriteRadio: 0,83%

    Isto mostra que a maioria dos indices estao sendo usados para consultas, certo??

    No caso da segunda query, foram listados 22 indices.

    Todos deram Read 0% e alguns deram write 100. Devo desabilitar estes indices certo?

    O banco não possui um plano de manutençao desses indices, eu estava pensando em criar um plano fazendo um rebuild dos indices, mas nesses casos, os indices desabilitados voltariam a ativa. Como devo fazer ???

    Você sabe me dizer uma query que lista a quantidade de indice do banco ou de cada tabela ???

    segunda-feira, 18 de junho de 2012 11:15
  • Faz muito tempo que vc criou esses 22 índices? Porque se faz pouco tempo deixa mais uns dias para ter uma coleta de estatística mais apurada.

    Mas a rigor esses índices eu desabilito, não apago para não criar novamente mais para frente.


    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco

    segunda-feira, 18 de junho de 2012 12:19
  • Fausto,

    EXCELENTEEEE !!!!Ajudou 100% !!!

    Segue os resultados:

    1ª Query:

    ReadPercent: 99,16%

    WriteRadio: 0,83%

    Isto mostra que a maioria dos indices estao sendo usados para consultas, certo??

    No caso da segunda query, foram listados 22 indices.

    Todos deram Read 0% e alguns deram write 100. Devo desabilitar estes indices certo?

    O banco não possui um plano de manutençao desses indices, eu estava pensando em criar um plano fazendo um rebuild dos indices, mas nesses casos, os indices desabilitados voltariam a ativa. Como devo fazer ???

    Você sabe me dizer uma query que lista a quantidade de indice do banco ou de cada tabela ???

    No seu outro tópico de Fill Factor, eu passei um link que tem uma rotina para rebuild com calculo de Fill Factor... Essa rotina faz o rebuild índice por índice e no cursor "curIndices" ele lista somente os índices habilitados, você pode adaptar a rotina para fazer somente o rebuild sem levar em conta o Fill Factor


    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco

    segunda-feira, 18 de junho de 2012 12:23
  • Faz muito tempo que vc criou esses 22 índices? Porque se faz pouco tempo deixa mais uns dias para ter uma coleta de estatística mais apurada.

    Mas a rigor esses índices eu desabilito, não apago para não criar novamente mais para frente.


    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco

    Fausto,

    Eu sou novo na empresa, nao fui eu quem criou estes indices, então estou fazendo uma varredura no banco, que por sinal esta muito mal cuidado rs....

    Valeu pelas dicas, como sou iniciante ajudou bastante, estou tendo que aprender meio que na marra, mas ta sendo bacana....

    Vc teria mais alguma dica relevante com relação a indice e performance que possa me ajudar ?

    Dei uma olhada no seu blog, show de bola tbm ....

    Te enviei um e-mail pelo seu site, se puder me responder ficarei grato..



    segunda-feira, 18 de junho de 2012 12:50