Geekly articles weekly

Visão geral da ferramenta SQLIndexManager gratuita

Como você sabe, os índices desempenham um papel importante no DBMS, fornecendo uma pesquisa rápida dos registros necessários. Portanto, é muito importante atendê-los em tempo hábil. Muito material foi escrito sobre análise e otimização, inclusive na Internet. Por exemplo, uma revisão recente deste tópico foi feita nesta publicação .

Existem muitas soluções gratuitas e pagas para isso. Por exemplo, existe uma solução pronta para uso com base em um método de otimização de índice adaptável.

Em seguida, considere o utilitário SQLIndexManager gratuito, de autoria de AlanDenton .

A principal diferença técnica entre o SQLIndexManager e vários outros análogos é feita pelo próprio autor aqui e aqui .

No mesmo artigo, examinamos o projeto e as possibilidades de uso desta solução de software.

Discuta este utilitário aqui .
Com o tempo, a maioria dos comentários e bugs foram corrigidos.

Então, agora vamos ao próprio utilitário SQLIndexManager.

O aplicativo é escrito em C # .NET Framework 4.5 no Visual Studio 2017 e usa o DevExpress para formulários:



e fica assim:



Todas as solicitações são geradas nos seguintes arquivos:

  1. Índice
  2. Consulta
  3. Queryengine
  4. ServerInfo



Ao conectar-se ao banco de dados e enviar solicitações ao DBMS, o aplicativo é assinado da seguinte maneira:

ApplicationName=”SQLIndexManager”

Quando o aplicativo é iniciado, uma janela modal é aberta para adicionar uma conexão:


Aqui, o carregamento da lista completa de todas as instâncias do MS SQL Server disponíveis em redes locais ainda não funciona.

Você também pode adicionar uma conexão usando o botão mais à esquerda no menu principal:



Em seguida, as seguintes consultas DBMS serão iniciadas:

  1. Obtendo informações do DBMS
     SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT)

  2. Obtendo uma lista de bancos de dados disponíveis com suas breves propriedades
     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 acima, aparece uma janela contendo informações breves sobre os bancos de dados da instância selecionada do MS SQL Server:



Vale ressaltar que as informações estendidas são exibidas com base nos direitos. Se houver sysadmin , você poderá selecionar dados na visualização sys.master_files . Se não houver tais direitos, menos dados serão retornados para não retardar a solicitação.

Aqui você precisa selecionar o banco de dados de interesse e clicar no botão "OK".

Em seguida, o seguinte script será executado para cada banco de dados selecionado para analisar o status dos índices:

Análise de Status do Índice
 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 <= @PreDescribeSize AND r.[index_level] = 0 AND r.[alloc_unit_type_desc] = 'IN_ROW_DATA' AND i.IndexType IN (0, 1, 2) IF OBJECT_ID('tempdb.dbo.#Columns') IS NOT NULL DROP TABLE #Columns CREATE TABLE #Columns ( ObjectID INT NOT NULL , ColumnID INT NOT NULL , ColumnName SYSNAME NULL , SystemTypeID TINYINT NULL , IsSparse BIT , IsColumnSet BIT , MaxLen INT , PRIMARY KEY (ObjectID, ColumnID) ) INSERT INTO #Columns SELECT ObjectID = [object_id] , ColumnID = [column_id] , ColumnName = [name] , SystemTypeID = [system_type_id] , IsSparse = [is_sparse] , IsColumnSet = [is_column_set] , MaxLen = [max_length] FROM sys.columns WITH(NOLOCK) WHERE [object_id] IN (SELECT DISTINCT i.ObjectID FROM #Indexes i) IF OBJECT_ID('tempdb.dbo.#IndexColumns') IS NOT NULL DROP TABLE #IndexColumns CREATE TABLE #IndexColumns ( ObjectID INT NOT NULL , IndexID INT NOT NULL , OrderID INT NOT NULL , ColumnID INT NOT NULL , IsIncluded BIT NOT NULL , PRIMARY KEY (ObjectID, IndexID, ColumnID) ) INSERT INTO #IndexColumns SELECT ObjectID = [object_id] , IndexID = [index_id] , OrderID = CASE WHEN [is_included_column] = 0 THEN [key_ordinal] ELSE [index_column_id] END , ColumnID = [column_id] , IsIncluded = ISNULL([is_included_column], 0) FROM sys.index_columns ic WITH(NOLOCK) WHERE EXISTS( SELECT * FROM #Indexes i WHERE i.ObjectID = ic.[object_id] AND i.IndexID = ic.[index_id] AND i.IndexType IN (1, 2) ) IF OBJECT_ID('tempdb.dbo.#Lob') IS NOT NULL DROP TABLE #Lob CREATE TABLE #Lob ( ObjectID INT NOT NULL , IndexID INT NOT NULL , IsLobLegacy BIT , IsLob BIT , PRIMARY KEY (ObjectID, IndexID) ) INSERT INTO #Lob (ObjectID, IndexID, IsLobLegacy, IsLob) SELECT c.ObjectID , IndexID = ISNULL(i.IndexID, 1) , IsLobLegacy = MAX(CASE WHEN c.SystemTypeID IN (34, 35, 99) THEN 1 END) , IsLob = 0 FROM #Columns c LEFT JOIN #IndexColumns i ON c.ObjectID = i.ObjectID AND c.ColumnID = i.ColumnID WHERE c.SystemTypeID IN (34, 35, 99) GROUP BY c.ObjectID , i.IndexID IF OBJECT_ID('tempdb.dbo.#Sparse') IS NOT NULL DROP TABLE #Sparse CREATE TABLE #Sparse (ObjectID INT PRIMARY KEY) INSERT INTO #Sparse SELECT DISTINCT ObjectID FROM #Columns WHERE IsSparse = 1 OR IsColumnSet = 1 IF OBJECT_ID('tempdb.dbo.#AggColumns') IS NOT NULL DROP TABLE #AggColumns CREATE TABLE #AggColumns ( ObjectID INT NOT NULL , IndexID INT NOT NULL , IndexColumns NVARCHAR(MAX) , IncludedColumns NVARCHAR(MAX) , PRIMARY KEY (ObjectID, IndexID) ) INSERT INTO #AggColumns SELECT t.ObjectID , t.IndexID , IndexColumns = STUFF(( SELECT ', [' + c.ColumnName + ']' FROM #IndexColumns i JOIN #Columns c ON i.ObjectID = c.ObjectID AND i.ColumnID = c.ColumnID WHERE i.ObjectID = t.ObjectID AND i.IndexID = t.IndexID AND i.IsIncluded = 0 ORDER BY i.OrderID FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '') , IncludedColumns = STUFF(( SELECT ', [' + c.ColumnName + ']' FROM #IndexColumns i JOIN #Columns c ON i.ObjectID = c.ObjectID AND i.ColumnID = c.ColumnID WHERE i.ObjectID = t.ObjectID AND i.IndexID = t.IndexID AND i.IsIncluded = 1 ORDER BY i.OrderID FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '') FROM ( SELECT DISTINCT ObjectID, IndexID FROM #Indexes WHERE IndexType IN (1, 2) ) t 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) )


Como você pode ver pelas próprias consultas, tabelas temporárias são frequentemente usadas. Isso é feito para que não haja recompilação e, no caso de um esquema grande, o plano pode ser gerado paralelamente ao inserir dados, pois a inserção com variáveis ​​de tabela é possível em apenas um fluxo.

Depois de executar o script acima, uma janela com a tabela de índice aparecerá:



Aqui você também pode exibir outras informações detalhadas, como:

  1. um banco de dados
  2. número de seções
  3. data e hora da última chamada
  4. compressão
  5. grupo de arquivos

etc.
As próprias colunas podem ser personalizadas:



Nas células da coluna Correção, você pode escolher qual ação será executada durante a otimização. Além disso, quando a verificação é concluída, a ação padrão é selecionada com base nas configurações selecionadas:



Você deve selecionar os índices desejados para processamento.

Usando o menu principal, você pode salvar o script (o mesmo botão inicia o processo de otimização de índice):



salve a tabela em diferentes formatos (o mesmo botão permite abrir configurações detalhadas para análise e otimização de índices):



Além disso, as informações podem ser atualizadas clicando no terceiro botão à esquerda no menu principal ao lado da lupa.

Um botão com uma lupa permite selecionar o banco de dados desejado para consideração.

Atualmente, não existe um sistema de ajuda completo. Portanto, pressionando o botão "?" simplesmente causará o aparecimento de uma janela modal contendo informações básicas sobre o produto de software:



Além de todas as opções acima, o menu principal possui uma barra de pesquisa:



Ao iniciar o processo de otimização de índice:



Também na parte inferior da janela, você pode ver o log das ações executadas:



Na janela para análise detalhada e otimização de índices, você pode configurar opções mais sutis:



Sugestões para a aplicação:

  1. possibilitar a atualização seletiva de estatísticas, não apenas para índices, mas também de maneiras diferentes (atualização completa ou parcial)
  2. possibilita não apenas selecionar o banco de dados, mas também servidores diferentes (isso é muito conveniente quando há muitas instâncias do MS SQL Server)
  3. para maior flexibilidade no uso, propõe-se agrupar comandos nas bibliotecas e enviá-los para os comandos do PowerShell, como é feito, por exemplo, aqui: dbatools.io/commands
  4. possibilitar salvar e alterar configurações pessoais para todo o aplicativo e, se necessário, para cada instância do MS SQL Server e cada banco de dados
  5. das cláusulas 2 e 4, segue-se o desejo de criar grupos em bancos de dados e grupos em instâncias do MS SQL Server, cujas configurações sejam as mesmas
  6. procure por índices duplicados (completos e incompletos, que diferem ligeiramente ou diferem apenas nas colunas incluídas)
  7. Como SQLIndexManager é usado apenas para o DBMS do MS SQL Server, é necessário refletir isso no nome, por exemplo, da seguinte maneira: SQLIndexManager para MS SQL Server
  8. Remova todas as partes do aplicativo da GUI em módulos separados e reescreva-os no .NET Core 2.1

No momento da redação deste artigo, o artigo 6 dos desejos está sendo desenvolvido ativamente e já existe suporte na forma de uma busca por duplicatas completas e similares:



Fontes


Source: https://habr.com/ru/post/pt461277/

More articles:


All Articles