Como é sabido, os índices desempenham um papel importante nos SGBDs, proporcionando uma busca rápida por registros específicos. Portanto, é crucial mantê-los de forma adequada. Há muito material disponível sobre análise e otimização, inclusive na Internet. Recentemente, foi feita uma revisão sobre esse tema em .
Existem muitas soluções, tanto pagas quanto gratuitas, para isso. Por exemplo, há uma pronta , baseada no método adaptativo de otimização de índices.
A seguir, vamos analisar a ferramenta gratuita , cujo autor é .
A principal diferença técnica entre o SQLIndexManager e outros semelhantes é apresentada pelo próprio autor. e .
Neste artigo, veremos o projeto e as possibilidades de utilização dessa solução de software.
Esta ferramenta é discutida .
Com o tempo, a maioria das observações e bugs foram corrigidos.
Agora, vamos à própria ferramenta SQLIndexManager.
O aplicativo foi desenvolvido na linguagem C# .NET Framework 4.5 no Visual Studio 2017 e utiliza o DevExpress para os formulários:
e apresenta a seguinte aparência:
Todas as consultas são formadas nos seguintes arquivos:
- Index
- Query
- QueryEngine
- ServerInfo
Ao conectar-se ao banco de dados e enviar consultas ao SGBD, o aplicativo se identifica da seguinte forma:
ApplicationName="SQLIndexManager" Ao iniciar o aplicativo, uma janela modal abrirá para adicionar a conexão:
Até agora, o carregamento completo de todas as instâncias do MS SQL Server disponíveis nas redes locais não está funcionando.
A conexão também pode ser adicionada usando o botão mais à esquerda no menu principal:
Seguirão as seguintes consultas ao SGBD:
- Obtendo informações sobre o SGBD
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - Obtendo a lista de bancos de dados disponíveis com suas propriedades resumidas
SELECT DatabaseName = t.[name] , d.DataSize , DataUsedSize = CAST(NULL AS BIGINT) , d.LogSize , LogUsedSize = CAST(NULL AS BIGINT) , RecoveryModel = t.recovery_model_desc , LogReuseWait = t.log_reuse_wait_desc FROM sys.databases t WITH(NOLOCK) LEFT JOIN ( SELECT [database_id] , DataSize = SUM(CASE WHEN [type] = 0 THEN CAST(size AS BIGINT) END) , LogSize = SUM(CASE WHEN [type] = 1 THEN CAST(size AS BIGINT) END) FROM sys.master_files WITH(NOLOCK) GROUP BY [database_id] ) d ON d.[database_id] = t.[database_id] WHERE t.[state] = 0 AND t.[database_id] != 2 AND ISNULL(HAS_DBACCESS(t.[name]), 1) = 1
Após a execução dos scripts mencionados acima, uma janela aparecerá contendo informações resumidas sobre os bancos de dados da instância do MS SQL Server selecionada:
Vale ressaltar que as informações detalhadas são exibidas com base nas permissões. Se houver , é possível escolher dados da visualização . Se não houver tais permissões, serão retornados apenas menos dados para não atrasar a consulta.
Aqui, é necessário selecionar os bancos de dados desejados e clicar no botão “OK”.
Em seguida, o seguinte script será executado para cada banco de dados selecionado para analisar o estado dos índices:
Análise do estado dos índices
declare @Fragmentation float=15;
declare @MinIndexSize bigint=768;
declare @MaxIndexSize bigint=1048576;
declare @PreDescribeSize bigint=32768;
SET NOCOUNT ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
IF OBJECT_ID('tempdb.dbo.#AllocationUnits') IS NOT NULL
DROP TABLE #AllocationUnits
CREATE TABLE #AllocationUnits (
ContainerID BIGINT PRIMARY KEY
, ReservedPages BIGINT NOT NULL
, UsedPages BIGINT NOT NULL
)
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
IF OBJECT_ID('tempdb.dbo.#ExcludeList') IS NOT NULL
DROP TABLE #ExcludeList
CREATE TABLE #ExcludeList (ID INT PRIMARY KEY)
INSERT INTO #ExcludeList
SELECT [object_id]
FROM sys.objects WITH(NOLOCK)
WHERE [type] IN ('V', 'U')
AND ( [is_ms_shipped] = 1 )
IF OBJECT_ID('tempdb.dbo.#Partitions') IS NOT NULL
DROP TABLE #Partitions
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)
IF OBJECT_ID('tempdb.dbo.#Indexes') IS NOT NULL
DROP TABLE #Indexes
CREATE TABLE #Indexes (
ObjectID INT NOT NULL
, IndexID INT NOT NULL
, IndexName SYSNAME NULL
, PagesCount BIGINT NOT NULL
, UnusedPagesCount BIGINT NOT NULL
, PartitionNumber INT NOT NULL
, RowsCount BIGINT NOT NULL
, IndexType TINYINT NOT NULL
, IsAllowPageLocks BIT NOT NULL
, DataSpaceID INT NOT NULL
, DataCompression TINYINT NOT NULL
, IsUnique BIT NOT NULL
, IsPK BIT NOT NULL
, FillFactorValue INT NOT NULL
, IsFiltered BIT NOT NULL
, PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)
INSERT INTO #Indexes
SELECT ObjectID = i.[object_id]
, IndexID = i.index_id
, IndexName = i.[name]
, PagesCount = a.ReservedPages
, UnusedPagesCount = CASE WHEN ABS(a.ReservedPages - a.UsedPages) > 32 THEN a.ReservedPages - a.UsedPages ELSE 0 END
, 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
DECLARE @files TABLE (ID INT PRIMARY KEY)
INSERT INTO @files
SELECT DISTINCT [data_space_id]
FROM sys.database_files WITH(NOLOCK)
WHERE [state] != 0
AND [type] = 0
IF @@ROWCOUNT > 0 BEGIN
DELETE FROM i
FROM #Indexes i
LEFT JOIN sys.destination_data_spaces dds WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] AND i.PartitionNumber = dds.[destination_id]
WHERE ISNULL(dds.[data_space_id], i.DataSpaceID) IN (SELECT * FROM @files)
END
DECLARE @DBID INT
, @DBNAME SYSNAME
SET @DBNAME = DB_NAME()
SELECT @DBID = [database_id]
FROM sys.databases WITH(NOLOCK)
WHERE [name] = @DBNAME
IF OBJECT_ID('tempdb.dbo.#Fragmentation') IS NOT NULL
DROP TABLE #Fragmentation
CREATE TABLE #Fragmentation (
ObjectID INT NOT NULL
, IndexID INT NOT NULL
, PartitionNumber INT NOT NULL
, Fragmentation FLOAT NOT NULL
, PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)
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 = @Fragmentation
OR
i.PagesCount > @PreDescribeSize
OR
i.IndexType IN (5, 6)
)
Como pode ser visto pelas próprias consultas, tabelas temporárias são frequentemente utilizadas. Isso é feito para evitar recompilações e, em casos de grandes esquemas, o plano pode ser gerado em paralelo durante a inserção de dados, já que a inserção com variáveis de tabela só é possível em um único fluxo.
Após a execução do script acima, uma janela com a tabela de índices aparecerá:
Aqui também é possível exibir outras informações detalhadas, como:
- banco de dados
- número de seções
- data e hora do último acesso
- compressão
- grupo de arquivos
etc.
As próprias colunas podem ser configuradas:
Nas células da coluna Fix, é possível escolher qual ação será realizada durante a otimização. Além disso, ao finalizar a varredura, a ação padrão é escolhida com base nas configurações selecionadas:
É necessário selecionar os índices que precisam ser processados.
Com o menu principal, é possível tanto salvar o script (esse mesmo botão inicia o processo de otimização dos índices):
quanto salvar a tabela em diferentes formatos (esse mesmo botão permite abrir configurações detalhadas para análise e otimização dos índices):
As informações também podem ser atualizadas clicando no terceiro botão à esquerda no menu principal, ao lado da lupa.
O botão com a lupa permite selecionar os bancos de dados desejados para análise.
Atualmente, não há um sistema de ajuda completo. Portanto, clicar no botão "?" simplesmente abrirá uma janela modal contendo as informações principais sobre o produto:
Além de tudo o que foi descrito acima, há uma barra de pesquisa no menu principal:
Ao iniciar o processo de otimização dos índices:
Também na parte inferior da janela, é possível visualizar o log das ações realizadas:
Na janela de configurações detalhadas para análise e otimização dos índices, é possível ajustar opções mais finas:
Sugestões para o aplicativo:
- tornar possível atualizar estatísticas de forma seletiva não apenas para índices e também de maneiras diferentes (atualizar completamente ou parcialmente)
- tornar possível não apenas escolher bancos de dados, mas também diferentes servidores (isso é muito conveniente quando há muitas instâncias do MS SQL Server)
- para maior flexibilidade no uso, é sugerido encapsular os comandos em bibliotecas e exportá-los para comandos PowerShell, como feito, por exemplo, aqui:
- possibilitar o armazenamento e a modificação das configurações pessoais tanto para toda a aplicação quanto, se necessário, para cada instância do MS SQL Server e cada banco de dados
- do item 2 e 4 surge o desejo de criar grupos por bancos de dados e grupos por instâncias do MS SQL Server, para os quais as configurações são iguais
- realizar a busca de duplicatas de índices (completos e incompletos, que sejam semelhantes ou se diferenciem apenas pelas colunas incluídas)
- como o SQLIndexManager é usado apenas para bancos de dados MS SQL Server, isso deve ser refletido no nome, por exemplo, da seguinte forma: SQLIndexManager for MS SQL Server
- extrair todas as partes da aplicação que não são GUI para módulos separados e reescrevê-los em .NET Core 2.1
No momento da redação deste artigo, o item 6 das solicitações está em desenvolvimento ativo e já há suporte na forma de pesquisa de duplicatas completas e semelhantes:
Fontes
Fonte: habr.com
