Por muitos anos trabalhando como DBA do SQL Server, administrando servidores e otimizando o desempenho. Em geral, eu queria fazer algo útil no meu tempo livre para o Universo e nossos colegas. Portanto, no final, temos uma pequena
ferramenta de manutenção de índice de
código aberto para o SQL Server e o Azure.
Idéia
Às vezes, ao trabalhar em suas prioridades, as pessoas podem se parecer com uma bateria do tipo dedo - há carga de motivação suficiente para apenas um flash, e é isso. E até recentemente, eu não era exceção a essa observação da vida. Muitas vezes, fui visitado por idéias para criar algo próprio, mas as prioridades mudaram e nada foi levado ao fim.
Uma influência bastante forte sobre minha motivação e desenvolvimento profissional foi fornecida pelo trabalho na empresa Devart, de Kharkov, que se dedicou à criação de software para o desenvolvimento e administração de bancos de dados SQL Server, MySQL e Oracle.
Antes de chegar a eles, eu tinha pouca idéia das especificidades de criar meu próprio produto, mas já no processo, adquiri muito conhecimento sobre a estrutura interna do SQL Server. Tendo otimizado as solicitações de metadados em suas linhas de produtos há mais de um ano, gradualmente comecei a entender qual funcionalidade é mais requisitada no mercado do que qualquer outra.
Em um certo estágio, surgiu a idéia de criar um novo produto de nicho, mas, devido às circunstâncias, ela não decolou. Naquela época, para o novo projeto, não havia recursos livres suficientes suficientes na empresa sem comprometer o negócio principal.
Já quando ele trabalhava em um novo local e tentava fazer o projeto por conta própria, tinha que fazer alguns compromissos constantemente. A idéia inicial de tornar um produto grande e cheio de recursos rapidamente se transformou em uma direção diferente - para dividir a funcionalidade planejada em mini-ferramentas separadas e implementá-las independentemente uma da outra.
Como resultado, o
SQL Index Manager nasceu - uma ferramenta gratuita de manutenção de índice para o SQL Server e o Azure. A idéia principal era tomar como base alternativas comerciais da RedGate e Devart e tentar melhorar sua funcionalidade. Fornecer, para usuários iniciantes e experientes, a capacidade de analisar e manter convenientemente índices.
Implementação
Em palavras, tudo sempre parece simples ... Peguei um e olhei para alguns vidosiks motivadores, fiquei na prateleira e comecei a fazer um produto legal. Mas, na prática, nem tudo é tão fácil, pois existem muitas armadilhas ao trabalhar com a função de tabela do sistema sys.dm_db_index_physical_stats e, em combinação, o único local de onde você pode obter informações relevantes sobre a fragmentação do índice.
Desde os primeiros dias de desenvolvimento, houve uma grande oportunidade de criar um caminho sombrio entre os esquemas padrão e copiar a lógica já depurada do trabalho dos aplicativos concorrentes, acrescentando um pouco de piada. Mas, depois de analisar as solicitações de metadados, eu queria fazer algo mais otimizado, que, devido à burocracia das grandes empresas, nunca teria aparecido em seus produtos.
Ao analisar o RedGate SQL Index Manager (1.1.9.1378 - $ 155), você pode ver que o aplicativo usa uma abordagem muito simples: com uma consulta, obtemos uma lista de tabelas e visualizações do usuário e, após a segunda consulta, uma lista de todos os índices no banco de dados selecionado é retornada.
SELECT objects.name AS tableOrViewName , objects.object_id AS tableOrViewId , schemas.name AS schemaName , CAST(ISNULL(lobs.NumLobs, 0) AS BIT) AS ContainsLobs , o.is_memory_optimized FROM sys.objects AS objects JOIN sys.schemas AS schemas ON schemas.schema_id = objects.schema_id LEFT JOIN ( SELECT object_id , COUNT(*) AS NumLobs FROM sys.columns WITH (NOLOCK) WHERE system_type_id IN (34, 35, 99) OR max_length = -1 GROUP BY object_id ) AS lobs ON objects.object_id = lobs.object_id LEFT JOIN sys.tables AS o ON o.object_id = objects.object_id WHERE objects.type = 'U' OR objects.type = 'V' SELECT i.object_id AS tableOrViewId , i.name AS indexName , i.index_id AS indexId , i.allow_page_locks AS allowPageLocks , p.partition_number AS partitionNumber , CAST((c.numPartitions - 1) AS BIT) AS belongsToPartitionedIndex FROM sys.indexes AS i JOIN sys.partitions AS p ON p.index_id = i.index_id AND p.object_id = i.object_id JOIN ( SELECT COUNT(*) AS numPartitions , object_id , index_id FROM sys.partitions GROUP BY object_id , index_id ) AS c ON c.index_id = i.index_id AND c.object_id = i.object_id WHERE i.index_id > 0 -- ignore heaps AND i.is_disabled = 0 AND i.is_hypothetical = 0
Em um ciclo, para cada seção do índice, uma solicitação é enviada para determinar seu tamanho e nível de fragmentação. No final da varredura, os índices que pesam menos que o limite de entrada são descartados no cliente.
EXEC sp_executesql N' SELECT index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)' , N'@databaseId int,@objectId int,@indexId int,@partitionNr int' , @databaseId = 7, @objectId = 2133582639, @indexId = 1, @partitionNr = 1 EXEC sp_executesql N' SELECT index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)' , N'@databaseId int,@objectId int,@indexId int,@partitionNr int' , @databaseId = 7, @objectId = 2133582639, @indexId = 2, @partitionNr = 1 EXEC sp_executesql N' SELECT index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)' , N'@databaseId int,@objectId int,@indexId int,@partitionNr int' , @databaseId = 7, @objectId = 2133582639, @indexId = 3, @partitionNr = 1
Ao analisar a lógica desta aplicação, você pode encontrar muitas deficiências. Por exemplo, se você encontrar falhas nas ninharias, antes de enviar uma solicitação, nenhuma verificação será feita sobre se a seção atual contém seqüências de caracteres para excluir seções vazias da varredura.
Mas o problema é mais grave em outro aspecto: o número de solicitações do servidor será aproximadamente igual ao número total de linhas de sys.partitions. Dado o fato de que bancos de dados reais podem conter dezenas de milhares de seções, essa nuance pode levar a um grande número de solicitações semelhantes ao servidor. Em uma situação em que o banco de dados é remoto, o tempo de varredura se tornará ainda maior devido ao aumento de atrasos na rede para cada solicitação, mesmo a mais simples.
Diferentemente do RedGate, um produto similar desenvolvido no Devart - o dbForge Index Manager para SQL Server (1.10.38 - $ 99) recebe informações em uma consulta grande e exibe tudo no cliente:
SELECT SCHEMA_NAME(o.[schema_id]) AS [schema_name] , o.name AS parent_name , o.[type] AS parent_type , i.name , i.type_desc , s.avg_fragmentation_in_percent , s.page_count , p.partition_number , p.[rows] , ISNULL(lob.is_lob_legacy, 0) AS is_lob_legacy , ISNULL(lob.is_lob, 0) AS is_lob , CASE WHEN ds.[type] = 'PS' THEN 1 ELSE 0 END AS is_partitioned FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s JOIN sys.partitions p ON s.[object_id] = p.[object_id] AND s.index_id = p.index_id AND s.partition_number = p.partition_number JOIN sys.indexes i ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id LEFT JOIN ( SELECT c.[object_id] , index_id = ISNULL(i.index_id, 1) , is_lob_legacy = MAX(CASE WHEN c.system_type_id IN (34, 35, 99) THEN 1 END) , is_lob = MAX(CASE WHEN c.max_length = -1 THEN 1 END) FROM sys.columns c LEFT JOIN sys.index_columns i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id AND i.index_id > 0 WHERE c.system_type_id IN (34, 35, 99) OR c.max_length = -1 GROUP BY c.[object_id], i.index_id ) lob ON lob.[object_id] = i.[object_id] AND lob.index_id = i.index_id JOIN sys.objects o ON o.[object_id] = i.[object_id] JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id WHERE i.[type] IN (1, 2) AND i.is_disabled = 0 AND i.is_hypothetical = 0 AND s.index_level = 0 AND s.alloc_unit_type_desc = 'IN_ROW_DATA' AND o.[type] IN ('U', 'V')
Conseguimos nos livrar do problema principal com o véu do mesmo tipo de consultas em um produto concorrente, mas as desvantagens dessa implementação são que nenhum parâmetro adicional é passado para a função sys.dm_db_index_physical_stats que pode limitar a varredura de índices obviamente desnecessários. De fato, isso leva à obtenção de informações sobre todos os índices no sistema e cargas extras de disco no estágio de varredura.
É importante observar que os dados obtidos de sys.dm_db_index_physical_stats não são armazenados em cache permanentemente no buffer pool, portanto, minimizar as leituras físicas ao obter informações sobre a fragmentação do índice foi uma das tarefas prioritárias durante o desenvolvimento.
Após várias experiências, acabou combinando as duas abordagens, dividindo a varredura em duas partes. Primeiro, uma consulta grande determina o tamanho das seções, pré-filtrando as que não estão no intervalo de filtragem:
INSERT INTO #AllocationUnits (ContainerID, ReservedPages, UsedPages) SELECT [container_id] , SUM([total_pages]) , SUM([used_pages]) FROM sys.allocation_units WITH(NOLOCK) GROUP BY [container_id] HAVING SUM([total_pages]) BETWEEN @MinIndexSize AND @MaxIndexSize
Em seguida, obtemos apenas as seções que contêm dados para evitar operações de leitura desnecessárias de índices vazios.
SELECT [object_id] , [index_id] , [partition_id] , [partition_number] , [rows] , [data_compression] INTO #Partitions FROM sys.partitions WITH(NOLOCK) WHERE [object_id] > 255 AND [rows] > 0 AND [object_id] NOT IN (SELECT * FROM #ExcludeList)
Dependendo das configurações, somente os tipos de índices são obtidos que o usuário deseja analisar (o trabalho com heaps, índices de cluster / não cluster e indicadores de coluna é suportado).
INSERT INTO #Indexes SELECT ObjectID = i.[object_id] , IndexID = i.index_id , IndexName = i.[name] , PagesCount = a.ReservedPages , UnusedPagesCount = a.ReservedPages - a.UsedPages , PartitionNumber = p.[partition_number] , RowsCount = ISNULL(p.[rows], 0) , IndexType = i.[type] , IsAllowPageLocks = i.[allow_page_locks] , DataSpaceID = i.[data_space_id] , DataCompression = p.[data_compression] , IsUnique = i.[is_unique] , IsPK = i.[is_primary_key] , FillFactorValue = i.[fill_factor] , IsFiltered = i.[has_filter] FROM #AllocationUnits a JOIN #Partitions p ON a.ContainerID = p.[partition_id] JOIN sys.indexes i WITH(NOLOCK) ON i.[object_id] = p.[object_id] AND p.[index_id] = i.[index_id] WHERE i.[type] IN (0, 1, 2, 5, 6) AND i.[object_id] > 255
Depois disso, um pouco de mágica começa: para todos os pequenos índices, determinamos o nível de fragmentação chamando repetidamente a função sys.dm_db_index_physical_stats com uma indicação completa de todos os parâmetros.
INSERT INTO #Fragmentation (ObjectID, IndexID, PartitionNumber, Fragmentation) SELECT i.ObjectID , i.IndexID , i.PartitionNumber , r.[avg_fragmentation_in_percent] FROM #Indexes i CROSS APPLY sys.dm_db_index_physical_stats(@DBID, i.ObjectID, i.IndexID, i.PartitionNumber, 'LIMITED') r WHERE i.PagesCount <= @PreDescribeSize AND r.[index_level] = 0 AND r.[alloc_unit_type_desc] = 'IN_ROW_DATA' AND i.IndexType IN (0, 1, 2)
Em seguida, retornamos todas as informações possíveis ao cliente, filtrando o excesso de dados:
SELECT i.ObjectID , i.IndexID , i.IndexName , ObjectName = o.[name] , SchemaName = s.[name] , i.PagesCount , i.UnusedPagesCount , i.PartitionNumber , i.RowsCount , i.IndexType , i.IsAllowPageLocks , u.TotalWrites , u.TotalReads , u.TotalSeeks , u.TotalScans , u.TotalLookups , u.LastUsage , i.DataCompression , f.Fragmentation , IndexStats = STATS_DATE(i.ObjectID, i.IndexID) , IsLobLegacy = ISNULL(lob.IsLobLegacy, 0) , IsLob = ISNULL(lob.IsLob, 0) , IsSparse = CAST(CASE WHEN p.ObjectID IS NULL THEN 0 ELSE 1 END AS BIT) , IsPartitioned = CAST(CASE WHEN dds.[data_space_id] IS NOT NULL THEN 1 ELSE 0 END AS BIT) , FileGroupName = fg.[name] , i.IsUnique , i.IsPK , i.FillFactorValue , i.IsFiltered , a.IndexColumns , a.IncludedColumns FROM #Indexes i JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = i.ObjectID JOIN sys.schemas s WITH(NOLOCK) ON s.[schema_id] = o.[schema_id] LEFT JOIN #AggColumns a ON a.ObjectID = i.ObjectID AND a.IndexID = i.IndexID LEFT JOIN #Sparse p ON p.ObjectID = i.ObjectID LEFT JOIN #Fragmentation f ON f.ObjectID = i.ObjectID AND f.IndexID = i.IndexID AND f.PartitionNumber = i.PartitionNumber LEFT JOIN ( SELECT ObjectID = [object_id] , IndexID = [index_id] , TotalWrites = NULLIF([user_updates], 0) , TotalReads = NULLIF([user_seeks] + [user_scans] + [user_lookups], 0) , TotalSeeks = NULLIF([user_seeks], 0) , TotalScans = NULLIF([user_scans], 0) , TotalLookups = NULLIF([user_lookups], 0) , LastUsage = ( SELECT MAX(dt) FROM ( VALUES ([last_user_seek]) , ([last_user_scan]) , ([last_user_lookup]) , ([last_user_update]) ) t(dt) ) FROM sys.dm_db_index_usage_stats WITH(NOLOCK) WHERE [database_id] = @DBID ) u ON i.ObjectID = u.ObjectID AND i.IndexID = u.IndexID LEFT JOIN #Lob lob ON lob.ObjectID = i.ObjectID AND lob.IndexID = i.IndexID LEFT JOIN sys.destination_data_spaces dds WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] AND i.PartitionNumber = dds.[destination_id] JOIN sys.filegroups fg WITH(NOLOCK) ON ISNULL(dds.[data_space_id], i.DataSpaceID) = fg.[data_space_id] WHERE o.[type] IN ('V', 'U') AND ( f.Fragmentation >= @Fragmentation OR i.PagesCount > @PreDescribeSize OR i.IndexType IN (5, 6) )
Depois disso, as consultas pontuais determinam o nível de fragmentação para índices grandes.
EXEC sp_executesql N' DECLARE @DBID INT = DB_ID() SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats(@DBID, @ObjectID, @IndexID, @PartitionNumber, ''LIMITED'') WHERE [index_level] = 0 AND [alloc_unit_type_desc] = ''IN_ROW_DATA''' , N'@ObjectID int,@IndexID int,@PartitionNumber int' , @ObjectId = 1044198770, @IndexId = 1, @PartitionNumber = 1 EXEC sp_executesql N' DECLARE @DBID INT = DB_ID() SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats(@DBID, @ObjectID, @IndexID, @PartitionNumber, ''LIMITED'') WHERE [index_level] = 0 AND [alloc_unit_type_desc] = ''IN_ROW_DATA''' , N'@ObjectID int,@IndexID int,@PartitionNumber int' , @ObjectId = 1552724584, @IndexId = 0, @PartitionNumber = 1
Devido a essa abordagem, ao gerar consultas, foi possível resolver os problemas com o desempenho da verificação encontrados nos aplicativos concorrentes. Isso pode ser concluído, mas, no processo de desenvolvimento, surgiram novas idéias que permitiram expandir o escopo de aplicação do seu produto.
Inicialmente, o suporte ao trabalho com WAIT_AT_LOW_PRIORITY foi implementado e, em seguida, foi possível usar DATA_COMPRESSION e FILL_FACTOR para reconstruir índices.
O aplicativo estava um pouco cheio de funcionalidades não planejadas anteriormente, como a manutenção de colunas de serviço:
SELECT * FROM ( SELECT IndexID = [index_id] , PartitionNumber = [partition_number] , PagesCount = SUM([size_in_bytes]) / 8192 , UnusedPagesCount = ISNULL(SUM(CASE WHEN [state] = 1 THEN [size_in_bytes] END), 0) / 8192 , Fragmentation = CAST(ISNULL(SUM(CASE WHEN [state] = 1 THEN [size_in_bytes] END), 0) * 100. / SUM([size_in_bytes]) AS FLOAT) FROM sys.fn_column_store_row_groups(@ObjectID) GROUP BY [index_id] , [partition_number] ) t WHERE Fragmentation >= @Fragmentation AND PagesCount BETWEEN @MinIndexSize AND @MaxIndexSize
Ou a capacidade de criar índices não agrupados em cluster com base nas informações de dm_db_missing_index:
SELECT ObjectID = d.[object_id] , UserImpact = gs.[avg_user_impact] , TotalReads = gs.[user_seeks] + gs.[user_scans] , TotalSeeks = gs.[user_seeks] , TotalScans = gs.[user_scans] , LastUsage = ISNULL(gs.[last_user_scan], gs.[last_user_seek]) , IndexColumns = CASE WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NOT NULL THEN d.[equality_columns] + ', ' + d.[inequality_columns] WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NULL THEN d.[equality_columns] ELSE d.[inequality_columns] END , IncludedColumns = d.[included_columns] FROM sys.dm_db_missing_index_groups g WITH(NOLOCK) JOIN sys.dm_db_missing_index_group_stats gs WITH(NOLOCK) ON gs.[group_handle] = g.[index_group_handle] JOIN sys.dm_db_missing_index_details d WITH(NOLOCK) ON g.[index_handle] = d.[index_handle] WHERE d.[database_id] = DB_ID()
Sumário
Após seis meses da fase de desenvolvimento ativo, fico feliz que os planos não terminem aí, porque quero continuar desenvolvendo este produto. A próxima etapa é adicionar funcionalidade para procurar índices duplicados ou não utilizados, além de implementar o suporte completo para veicular estatísticas no SQL Server.
Com base no fato de haver muitas soluções pagas no mercado agora, quero acreditar que, devido ao posicionamento livre, uma descrição mais otimizada dos metadados e a presença de várias pequenas coisas úteis para alguém, este produto definitivamente se tornará útil nas tarefas diárias.
A versão atual do aplicativo pode ser baixada no
GitHub . As fontes estão no mesmo lugar.