SQL Server: Bancos de dados e índices
Gerenciar seus índices e ter os índices certos no lugar certo são partes essenciais do gerenciamento da carga de trabalho geral do SQL Server.
Extraído do "SQL Server DMV Starter Pack," publicado pela Red Gate Books (2010).
Glenn Berry, Louis Davidson e Tim Ford
Microsoft divide logicamente objetos de gerenciamento de banco de dados (DMOs) no nível de banco de dados/arquivos em duas categorias:
- Banco de dados-relacionados. Estes contêm exibições de gerenciamento de banco de dados (DMVs) que vamos investigar contagens de página e de linha de tabela e de índice para um determinado banco de dados, bem como a alocação de página no nível do arquivo. Um par de DMVs também são dedicados a investigar o uso de banco de dados TempDB.
- Índice-relacionados. Estes contêm DMVs especificamente relacionados à índices, suas características, como eles são usados para ajudar a identificar os índices potencialmente útil para sua carga de trabalho.
Todos os pontos de vista estas duas categorias têm "sys.dm_db_" no início de seus rótulos. Estes tipos de DMVs podem ajudá-lo a definir uma estratégia eficaz de indexação, como esta é uma das melhores maneiras de garantir que as consultas mais significativas e frequentes são capazes de ler os dados que necessitam de uma maneira lógica e ordenada e assim evitar I/O desnecessárias. Encontrar o equilíbrio correcto entre muitos índices e índices muito poucos — e tendo o "bom" conjunto de índices no lugar — é extremamente importante para obter o melhor desempenho do SQL Server.
Você também precisará monitorar o banco de dados TempDB. TempDB é um recurso global que armazena dados temporários para usuário e objetos internos para todos os usuários conectados a uma determinada instância do SQL Server. Isso inclui, por exemplo, tabelas de trabalho internas usadas para armazenar resultados de cursores, e objetos de usuário como tabelas temporárias e variáveis de tabela.
Localizar índices ausentes
Para descobrir quais índices estão potencialmente faltando em um determinado banco de dados, você precisará usar três DMVs intimamente relacionadas. O primeiro é sys.dm_db_missing_index_group_stats, que é descrita da seguinte forma:
"Retorna informações de resumo sobre os grupos de índices ausentes, excluindo índices espaciais. As informações retornadas por sys.dm_db_missing_index_group_stats atualizadas por cada execução da consulta, e não por cada consulta compilação ou recompilação. Estatísticas de uso não são persistentes e são mantidas apenas até SQL Server é reiniciado. Os administradores de banco de dados devem periodicamente gerar cópias de backup de informações de índice ausente se quiserem manter as estatísticas de uso após o servidor reciclagem."
O segundo DMV é db_missing_index_groups, descritas a seguir:
"Retorna informações sobre quais índices ausentes estão contidos em um grupo de índice ausente específico, excluindo índices espaciais".
Isso é basicamente apenas uma tabela de junção entre sys.dm_db_missing_index_group_stats e nosso terceiro DMV, que é db_missing_index_details, descrito como este:
"Retorna informações detalhadas sobre índices ausentes, excluindo índices espaciais".
Juntando-se essas três DMVs, você começ uma consulta de índice ausente útil (ver Figura 1).
Figura 1 identificando índices potencialmente úteis.
-- Missing Indexes in current database by Index Advantage SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] , migs.last_user_seek , mid.[statement] AS [Database.Schema.Table] , mid.equality_columns , mid.inequality_columns , mid.included_columns , migs.unique_compiles , migs.user_seeks , migs.avg_total_user_cost , migs.avg_user_impact FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK ) INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) ON mig.index_handle = mid.index_handle WHERE mid.database_id = DB_ID() ORDER BY index_advantage DESC ;
Esta consulta usa as estatísticas de padrão de acesso a dados para uma tabela específica para calcular a possível vantagem de adicionar um determinado índice. Índices com um index_advantage mais elevado são aqueles que o SQL Server considera terá o maior impacto positivo na redução da carga de trabalho, com base na redução do custo da consulta e o número projetado de vezes em que eles vão usar o índice.
Tenha em mente que se você fizer uma alteração para um índice para uma determinada tabela, então todas as estatísticas de índice ausentes para aquela tabela são limpo e repovoadas novamente ao longo do tempo. Se você executar essa consulta logo após uma mudança de índice, provavelmente — e imprecisamente — informá-lo que não há nenhum índices ausentes para esta tabela.
Ele tem algumas limitações que você precisa considerar. Em primeiro lugar, ele não especificar sempre a melhor ordem das colunas para um índice. Se houver várias colunas listadas em equality_columns ou inequality_columns, você vai querer olhar para a seletividade de cada uma dessas colunas nos resultados da igualdade e desigualdade para determinar a melhor ordem de coluna para o novo índice de futuro. Em segundo lugar, ele não considera os índices filtrados, que são novos para o SQL Server 2008. Finalmente, está ansioso para sugerir novos índices e colunas incluídas em geral.
Nunca apenas cega você deve adicionar cada índice que esta consulta sugere, especialmente se você tiver uma carga de trabalho (OLTP) de processamento de transações on-line. Em vez disso, você precisará examinar cuidadosamente os resultados da consulta e filtrar manualmente os resultados que não fazem parte da sua carga de trabalho regular.
Comece examinando a coluna last_user_seek. Se o tempo de last_user_seek é alguns dias ou mesmo semanas atrás, em seguida, as consultas que fez SQL Server deseja esse índice são provavelmente de uma consulta aleatória, ad hoc ou parte de uma consulta de relatório executado com pouca freqüência. Por outro lado, se o tempo de last_user_seek foi alguns segundos ou alguns minutos atrás, provavelmente faz parte da sua carga de trabalho regular, e você deve considerar que o índice possível mais cuidadosamente.
Independentemente do que recomenda a esta consulta, sempre Olhe os índices existentes em uma tabela, incluindo suas estatísticas de uso, antes de fazer quaisquer alterações. Lembre-se de uma tabela mais voláteis geralmente deve ter índices menos do que uma tabela mais estática. Você deve ser muito hesitantes adicionar um novo índice em uma tabela (para uma carga de trabalho OLTP) se a tabela já tiver mais de cerca de cinco ou seis índices eficazes.
Não se esqueça de que o procedimento armazenado do sistema, sp_helpindex, não mostra as informações de coluna incluída. Isso significa que você deve usar uma substituição ou simplesmente gerar script a instrução CREATE INDEX para seus índices existentes.
Interrogar o uso do índice
Um das DMVs mais úteis na categoria de indexação é db_index_usage_stats, que é descrita da seguinte forma:
"Conta de retornos dos diferentes tipos de operações de índice e o tempo de cada tipo de operação foi a última. Cada busca individual, pesquisa, pesquisa ou atualização no índice especificado pela execução de uma consulta é contada como um uso desse índice e incrementa o contador correspondente nessa exibição. Informações são relatadas tanto para operações causaram pelas consultas de usuário enviada e para operações causadas por internamente gerado consultas, como varreduras para a recolha de estatísticas."
Essa DMV fornece informação valiosa sobre se e como frequentemente seus índices estão sendo usados, para leituras e gravações. Você também pode interrogar essa DMV para fornecer informações sobre:
- A distribuição da carga de trabalho através de seus índices definidos
- Índices que não são acessados por sua carga de trabalho e por isso são prime candidatos para exclusão
- Índices com um grande número de gravações e zero ou algumas leituras (estas também são candidatos à remoção, após mais investigação).
O primeiro desses três scripts (ver Figura 2) irá listar todas as suas tabelas heap, índices clusterizados e índices não-clusterizados, juntamente com o número de leituras, gravações e o fator de preenchimento para cada índice.
Figura 2 você pode determinar como seus índices estão sendo usados.
--- Index Read/Write stats (all tables in current DB) SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName] , i. name AS [IndexName] , i.index_id , user_seeks + user_scans + user_lookups AS [Reads] , user_updates AS [Writes] , i.type_desc AS [IndexType] , i.fill_factor AS [FillFactor] FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1 AND i.index_id = s.index_id AND s.database_id = DB_ID() ORDER BY OBJECT_NAME(s.[object_id]) , writes DESC , reads DESC ;
Esta é uma consulta útil para melhor compreensão de sua carga de trabalho. Ele pode ajudá-lo a determinar a volatilidade de um índice específico e a relação de leituras para gravações. Isso pode ajudá-lo a refinar e ajustar sua estratégia de indexação. Por exemplo, se você tivesse uma tabela que foi bastante estática (muito poucas gravações em qualquer um dos índices), você poderia se sentir mais confiante sobre como adicionar mais índices listados em suas consultas de índice ausente.
Se você tiver o SQL Server 2008 Enterprise Edition, esta consulta pode ajudá-lo a decidir se seria uma boa idéia para ativar a compactação de dados (página ou linha). Um índice com atividade de gravação muito pouco é provável que seja um candidato melhor para compactação de dados do que um índice mais voláteis.
O próximo script (consulte Figura 3) usa sys. indexes e sys. Objects para encontrar tabelas e índices no banco de dados atual que não aparecem na db_index_usage_stats. Isso significa que esses índices têm tinham nenhum leituras ou gravações, desde que o SQL Server foi iniciado ou desde que o banco de dados atual foi fechado ou desanexado (o que for mais curto).
Figura 3 encontrar índices não usados.
-- List unused indexes SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] , i. name FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id] WHERE i.index_id NOT IN ( SELECT s.index_id FROM sys.dm_db_index_usage_stats AS s WHERE s.[object_id] = i.[object_id] AND i.index_id = s.index_id AND database_id = DB_ID() ) AND o.[type] = ‘U’ ORDER BY OBJECT_NAME(i.[object_id]) ASC ;
Se SQL Server foi executado tempo suficiente que você tem uma carga de trabalho completa e representativa, há uma boa chance desses índices não utilizados (e talvez tabelas) são "mortas". Isso significa que seu banco de dados já não usa-los e você potencialmente pode deixá-los, depois de fazer algumas outras investigações.
Nossos filtros de consulta db_index_usage_stats final pelo banco de dados atual (ver Figura 4). Isso inclui somente índices não-clusterizados. Ele pode ajudá-lo a decidir se o custo de manutenção de um determinado índice supera o benefício de tê-la no lugar.
Figura 4 encontrar raramente usados índices.
-- Possible Bad NC Indexes (writes > reads) SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] , i. name AS [Index Name] , i.index_id , user_updates AS [Total Writes] , user_seeks + user_scans + user_lookups AS [Total Reads] , user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference] FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK ) INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1 AND s.database_id = DB_ID() AND user_updates > ( user_seeks + user_scans + user_lookups ) AND i.index_id > 1 ORDER BY [Difference] DESC , [Total Writes] DESC , [Total Reads] ASC ;
Esta consulta procura todos os índices que têm grande número de gravações com zero leituras. Qualquer índice que pertence a esta categoria é um bom candidato para exclusão (após investigação completa). Você quer ter a certeza de que sua instância do SQL Server está em execução há tempo suficiente para que você tenha sua carga de trabalho completa, típica incluída.
Não se esqueça sobre periódicas cargas de trabalho de relatórios que podem não aparecer no seu dia-a-carga de trabalho. Muito embora os índices que facilitam essas cargas de trabalho não será que freqüentemente usado, sua presença será fundamental.
Você também deve olhar para linhas onde há grande número de gravações e um pequeno número de leituras. Soltar esses índices serão mais de uma chamada de julgamento, dependendo da tabela e como familiar você está com sua carga de trabalho.
.png)
.png)
.png)
Glenn Berry trabalha como arquiteto de banco de dados no NewsGator Technologies Inc. em Denver. Ele é um MVP do SQL Server e tem uma coleção inteira de certificações da Microsoft, incluindo o MCITP, MCDBA, MCSE, MCSD, MCAD e MCTS, que prova que ele gosta de fazer testes.
Louis Davidson foi no setor de TI há 16 anos como arquiteto e desenvolvedor de banco de dados corporativo. Ele tem sido um MVP do Microsoft SQL Server para seis anos e já escreveu quatro livros sobre design de banco de dados. Atualmente ele é o arquiteto de dados e, por vezes, DBA para o Christian Broadcasting Network, escritórios de apoio em Virginia Beach, Virgínia e em Nashville, Tennessee.
Timothy Ford Eus um MVP do SQL Server e tem vindo a trabalhar com o SQL Server para mais de 10 anos. Ele é o principal DBA e especialista no assunto para a plataforma do SQL Server para a saúde do espectro. Ele tem sido escrito sobre tecnologia desde 2007 para uma variedade de sites da Web e mantém seu próprio blog em thesqlagentman.com, abrangendo SQL como tópicos de desenvolvimento bem como teletrabalho e profissional.**
Saiba mais sobre o "SQL Server DMV Starter Pack" em red-gate.com/our-company/about/book-store.