LAB: Analisando Índices

LAB: Analisando Índices

  • Comments 17

Próxima tarefa: Colocar todos os artigos encontrados na prática. Data prevista: Final de outubro/2010.

Post original:

A discussão começou com um comentário do Vladimir Magalhães no Twitter: como encontrar índices não utilizados ou pouco utilizados através das Dynamic Management Views (DMV). A teoria diz que a sys.dm_index_operational_stats seria a resposta para resolver esse problema. Uma busca rápida no Bing retornou o seguinte artigo:

Retaining historical index usage statistics for SQL Server (2009)
http://www.mssqltips.com/tip.asp?tip=1749

Será que somente isso é uma análise suficiente?

O intuito desse "Lab Post" é receber feedbacks sobre qual seria a melhor forma de analisar os índices em um banco de dados. Fiquem a vontade para postar seus comentários - não haverá moderação de comentários.

[Atualização 29/09]

1) Query mágina

Igor deixou um comentário intrigante, que existe "uma query mágica sugerida pelo autor que dá uma estatísca, ele diz que de o valro retornado for > que 50.000 o índice compensa muito". Esse é uma ótimo lugar para começar, eu fico imaginando se é alguma coisa relacionada com Missing Index ou se é algum contador do operational stats (ou index usage stats).

2) Informações em Tempo de Execução

Vladmir comentou sobre os MISSING INDEX que estão disponíveis dentro do plano de execução. Outra forma de observar é através das DMV de Missing Index. O comentário ""As MSFT suggest that index advantage over 5000 re-evaluate and over 10000 consider creating it."" é igualmente intrigante.

Por fim, deixou relembrou que nem sempre o otimizador está correto.
Existem casos que o Otimizador é incapaz de determinar os melhores índices, mas apenas um DBA experiente conseguiria realizar. Ou um bug, como por exemplo:

http://www.sqlskills.com/BLOGS/PAUL/post/Missing-index-DMVs-bug-that-could-cost-your-sanity.aspx

3) Detecting Overlapping Indexes in SQL Server 2005

http://blogs.msdn.com/b/mssqlisv/archive/2007/06/29/detecting-overlapping-indexes-in-sql-server-2005.aspx

4) Índices e Fill-factor

Uma pergunta curiosa é determinar o fill-factor de um índice. Vladmir passou o link que monitora os page split usando Extended Events (!!!!), escrito pelo Eladio Rincon.

http://msmvps.com/blogs/eladio_rincon/archive/2008/12/07/using-xevents-extended-events-in-sql-server-2008-to-detect-which-queries-are-causing-page-splits.aspx

5) Index Usage e Operational Stats

Fabricio Lima compartilhou essa experiência:

"Possuo um índice em uma tabela com 50 milhões de registros. Nessa tabela, tenho um campo de situacao que pode ser 0,1,2,3 ou 4. Sempre acompanhei esse índice e vi que tinha poucas utilizações até que um dia resolvi excluir esse índice seguindo o raciocínio de que o índice era muito pouco seletivo, a tabela é grande, o indice é bem pouco utilizado então não valeria a pena manter o índice. Entretanto, existia uma query com um where situacao = 2 dentre outras restrições. A tabela tinha apenas uns 1000 registros com situação = 2, por isso esse índice era muito eficiente para essa query. Assim, no inicio do mês quando essa query é utilizada várias vezes mas somente no inicio do mês, essa query me causou um problema de lentidão muito grande no banco... Na mesma noite tive que recriar o índice."

Entendo, portanto, que as DMV refletem um estado imediato do sistema e podem não corresponder a forma como o sistema funciona ao longo do tempo.

6) Usando o Transaction Log para determinar Page Splits

Grande Laerte sugeriu usar a função não-documentada fn_dblog() para determinar page splits. Por que não? :)

[Atualização: 13/Out/10 - Adicionando uma referência da Kimberly]

From: http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/68816/Default.aspx

I’d also add that MOST cases where I see 40, 50 or 60+ indexes are because of:

1) Indexes are being added without any single person watching over what’s being done (there are too many cooks in the kitchen!)

2) Index recommendations are being made by a variety of tools but no one is analyzing what’s there before they approve (and create) the recommended indexes.

Here’s how I’ll summarize my answer: start with a small number of indexes and then slowly (and iteratively) add indexes that are recommended but never add one without checking your existing indexes to see if you can consolidate or remove an existing index first. This is a bit more challenging and I’ll write a few posts on this coming up soon! Here’s a step by step process I like to follow (especially helpful for developers):

1) Create an effective clustering key. To help you understand the factors that make up a good clustering key, see these posts:

a. Changing the Definition of a Clustered Index: http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12749/Default.aspx

b. Isn’t the Clustering Key Redundant: http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12852/Isnt-the-Clustering-Key-Redundant.aspx

c. Where does that Clustering Key Go? http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12837/Where-Does-the-Clustering-Key-Go.aspx

d. What Happens If I Drop A Clustered Index? http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12748/Default.aspx

2) Create your primary key (if it’s not the clustering key and it’s not always wise to make the primary key clustered)

3) Create your unique keys

4) Add foreign keys as appropriate and then manually create nonclustered indexes on your foreign keys. SQL Server has NEVER automatically created indexes on foreign keys and these can help in a number of ways. They can help improve the performance of the relationship as well as some joins. There are often better indexes for many joins but this is always a good way to start.

5) Then, add a few nonclustered indexes for the columns that are very selective and used frequently in queries. A good example is LastName, FirstName, MiddleInitial. If you’re doing a lot of customer lookups by name, then a single index which combines the columns in the order of what’s most requested – can be added. If the combination is highly selective, SQL Server can use this index to help navigate to the data faster. If the column or combination of columns is not highly selective then the index is not as likely to be used (when the query is requesting other columns of data). This is where things start to get more challenging and we’ll have to tackle these issues in another post. This is a good start. Do not add too many indexes…

6) Slowly and iteratively add only the most important and critical indexes based on workload analysis. More coming up on this too!

  • Acabei de receber uma sugestão (via twitter) para usar sys.dm_db_index_usage_stats. É incrível que os resultados são muito parecidos com sys.dm_db_index_operational_stats!

  • Esqueci de dar os créditos ao @igor_antonio (twitter). Obrigado!

  • Valeu fabricio... As duas são realmente parecidas, mas a usage tem uma visão mais simples..., no Trainning Kit do SQL tem uma query mágica sugerida pelo autor que dá uma estatísca, ele diz que de o valro retornado for > que 50.000 o índice compensa muito... Rodei em um banco de produção e tinha uns retornos de mais de 6.000.000. Mas ainda não testei a fundo.

  • Igor, essa consulta serve para indicar os índices que seriam criados, correto?

    Eu usei essa consulta e uma outra que analisa

    sys.dm_exec_query_stats

    sys.dm_exec_sql_text

    sys.dm_exec_query_plan

    e indica planos de execução com um MISSING INDEX e tentei relacionar as 2.

    Vale lembrar que na primeira consulta a ordem sugerida dos campos nem sempre é a melhor (ou pelo menos eu encontrei alguns casos assim), ou ele sugere 2 índices parecidos onde apenas um pode suprir os dois casos.

    Agora, quanto aos índices "desnecessários" queria trazer um ponto a discussão.

    Por mais que agente olhe essas DMVs, procurando índices que não tem sido utilizados, temos que analisar as regras de negócio.

    Por exemplo, imagine que um índice é listado como desnecessário (pouco ou não utilizado), mas ele é importante para uma rotina que é executada apenas uma vez no mês e é pesada e muito importante para a empresa. Talvez as DMVs indiquem uma coisa, mas apenas o conhecimento das rotinas pode dar uma posição final.

  • Outros pontos que ia esquecendo.

    Vi esse comentário em um post de fórum, o qual não sei se é verdade

    "As MSFT suggest that index advantage over 5000 re-evaluate and over 10000 consider creating it."

    E no mesmo local havia um comentário sobre um post da Kimberly Tripp, onde ela comenta sobre um bug na DMV

    www.sqlskills.com/.../Missing-index-DMVs-bug-that-could-cost-your-sanity.aspx

    E neste post ela menciona o seguinte script para análise de missing indexes.

    blogs.msdn.com/.../are-you-using-sql-s-missing-index-dmvs.aspx

  • Algumas vezes o pessoal recomenda monitorar os índices que estão na dm_db_index_usage_stats, para determinar quais são aqueles utilizados. logo, aqueles que não aparecem são os desnecessários. O problema é exatamente o comentário do Vladimir: "Por exemplo, imagine que um índice é listado como desnecessário (pouco ou não utilizado), mas ele é importante para uma rotina que é executada apenas uma vez no mês e é pesada e muito importante para a empresa".

    Como que seria a análise em dm_db_index_operational_stats? Será que isso resolveria o problema?

    Outra pergunta que me faço é se há como monitorar o page split de forma efetiva.

  • Acabei de receber esse artigo:

    Detecting Overlapping Indexes in SQL Server 2005

    blogs.msdn.com/.../detecting-overlapping-indexes-in-sql-server-2005.aspx

  • Massa essa discussão,

    "Outra pergunta que me faço é se há como monitorar o page split de forma efetiva."

    Isso é uma resposta que gostaria muito de ter a nível de índice para poder avaliar o fillfactor ideal para um índice em um ambiente específico.

  • Bem interessante o script de overlapping indexes, estou fazendo uns testes e está realmente útil

    Quanto ao page split, com Extended Events o Eladio Rincon fez um script para monitorar consultas que causam page splits.

    msmvps.com/.../using-xevents-extended-events-in-sql-server-2008-to-detect-which-queries-are-causing-page-splits.aspx

  • Sobre encontrar índices não utilizados.

    Como as informações das dmv's são zeradas toda vez que o serviço do SQL Server é reiniciado( e no meu caso isso é feito uma vez por mês na atualização de segurança), todo dia eu gravo o histório de utilização dos índices das minhas maiores tabelas em uma tabela física para poder fazer uma análise com um período maior da utilização ou não desses índices, decidindo se serão excluídos ou não. Ja tenho quase um ano de baseline(como ñ tenho problema de espaço, matenho os dados).

    Compartilhando uma experiencia que tive, eu possuo um índice em uma tabela com 50 milhões de registros. Nessa tabela, tenho um campo de situacao que pode ser 0,1,2,3 ou 4. Sempre acompanhei esse índice e vi que tinha poucas utilizações até que um dia resolvi excluir esse índice seguindo o raciocínio de que o índice era muito pouco seletivo, a tabela é grande, o indice é bem pouco utilizado então não valeria a pena manter o índice. Entretanto, existia uma query com um where situacao = 2 dentre outras restrições. A tabela tinha apenas uns 1000 registros com situação = 2, por isso esse índice era muito eficiente para essa query. Assim, no inicio do mês quando essa query é utilizada várias vezes mas somente no inicio do mês, essa query me causou um problema de lentidão muito grande no banco... Na mesma noite tive que recriar o índice.

    Vivendo e aprendendo...

  • Agradeço pelos comentários até o momento. Em especial, sobre as experiências compartilhadas. Essa semana estou investigando um compartamento interessante de PageSplit que afeta a performance e já percebi que a DMV operational_stats pode ajudar aqui. Amanhã vou rodar alguns scripts para capturar mais informações e logo colocarei no blog.

    Se mais alguém tiver comentários, será sempre bem-vindo.

    Ps. Assim que der, pretendo atualizar o conteúdo desse post com as dicas que recebi.

  • Recentemente encontrei dois artigos falando sobre a DMV sys.dm_db_index_physical_stats. Ambos são do Paul Randal e vale a pena dar uma olhada.

    www.sqlmag.com/.../Default.aspx

    www.sqlskills.com/.../Inside-sysdm_db_index_physical_stats.aspx

  • Um comentário muito interessante que vi no MCDBA hoje:

    Gostaria de Indexar indexar algumas tabelas como eu faço para localizar as colunas mais pesquisadas para indexá-las.Tem alguma forma de encontrar isso no catálogo do sistema?

    A pergunta é muito interessante porque não tem como identificar qual a coluna mais pesquisada, apesar de ser possível identificar a coluna mais modificada (UPDATE).

    Para determinar qual a melhor coluna para ser indexada, somente através dos "missing indexes". Uma query mágica seria algo assim:

    SELECT migs.group_handle, mid.*

    FROM sys.dm_db_missing_index_group_stats AS migs

    INNER JOIN sys.dm_db_missing_index_groups AS mig

       ON (migs.group_handle = mig.index_group_handle)

    INNER JOIN sys.dm_db_missing_index_details AS mid

       ON (mig.index_handle = mid.index_handle)

    (Copiei do Books Online)

    É possível determinar qual a tabela mais utilizada através dos comandos:

    select * from sys.dm_db_index_operational_stats

    select * from sys.dm_db_index_usage_stats

  • Grande Catae, quanto ao page split, que acha de usar a fn_dblog. ?

    laertejuniordba.spaces.live.com/.../cns!C16042A4306A1328!589.entry

    Abraços meu amigo

  • Ótima sugestão Laerte e aposto que ninguém pensou nisso ainda. Aqui no blog já ouvi vários comentários sobre DMV, mas nunca sobre o T-Log. Tem um artigo do Paul Randal que fala um pouco sobre isso... mas sem entrar em muitos detalhes.

    www.sqlskills.com/.../How-expensive-are-page-splits-in-terms-of-transaction-log.aspx

    Um dos problemas em usar fn_dblog é o fato de ser não-documentado.

    Mudando de assunto, ontem usei a DMV sys.dm_db_partition_stats para obter o espaço usado pelas partições. É uma boa view para substituir o sp_spaceused.

Page 1 of 2 (17 items) 12
Leave a Comment
  • Please add 4 and 5 and type the answer here:
  • Post