Geekly articles weekly

Présentation de l'outil gratuit SQLIndexManager

Comme vous le savez, les index jouent un rôle important dans le SGBD, fournissant une recherche rapide des enregistrements nécessaires. Par conséquent, il est si important de les entretenir en temps opportun. De nombreux documents ont été écrits sur l'analyse et l'optimisation, y compris sur Internet. Par exemple, une revue récente de ce sujet a été faite dans cette publication .

Il existe de nombreuses solutions payantes et gratuites pour cela. Par exemple, il existe une solution clé en main basée sur une méthode d'optimisation adaptative d'index.

Ensuite, considérez l'utilitaire gratuit SQLIndexManager , créé par AlanDenton .

La principale différence technique entre SQLIndexManager et un certain nombre d'autres analogues est faite par l'auteur lui-même ici et ici .

Dans le même article, nous examinons le projet et les possibilités d'utilisation de cette solution logicielle.

Discutez de cet utilitaire ici .
Au fil du temps, la plupart des commentaires et bogues ont été corrigés.

Passons maintenant à l'utilitaire SQLIndexManager lui-même.

L'application est écrite en C # .NET Framework 4.5 dans Visual Studio 2017 et utilise DevExpress pour les formulaires:



et ressemble à ceci:



Toutes les demandes sont générées dans les fichiers suivants:

  1. Index
  2. Requête
  3. Queryengine
  4. ServerInfo



Lors de la connexion à la base de données et de l'envoi de demandes au SGBD, l'application est signée comme suit:

ApplicationName=”SQLIndexManager”

Lorsque l'application démarre, une fenêtre modale s'ouvre pour ajouter une connexion:


Ici, le chargement de la liste complète de toutes les instances de MS SQL Server disponibles sur les réseaux locaux ne fonctionne pas encore.

Vous pouvez également ajouter une connexion en utilisant le bouton le plus à gauche du menu principal:



Ensuite, les requêtes SGBD suivantes seront lancées:

  1. Obtention d'informations sur le SGBD
     SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT)

  2. Obtenir une liste des bases de données disponibles avec leurs brèves propriétés
     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


Après avoir exécuté les scripts ci-dessus, une fenêtre apparaît contenant de brèves informations sur les bases de données de l'instance sélectionnée de MS SQL Server:



Il convient de noter que des informations étendues sont affichées en fonction des droits. S'il existe un administrateur système , vous pouvez sélectionner des données dans la vue sys.master_files . S'il n'y a pas de tels droits, moins de données sont renvoyées afin de ne pas ralentir la demande.

Ici, vous devez sélectionner la base de données d'intérêt et cliquer sur le bouton "OK".

Ensuite, le script suivant sera exécuté pour chaque base de données sélectionnée pour analyser l'état des index:

Analyse de l'état de l'index
 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) )


Comme vous pouvez le voir dans les requêtes elles-mêmes, des tables temporaires sont souvent utilisées. Ceci est fait de manière à ce qu'il n'y ait pas de recompilation, et dans le cas d'un grand schéma, le plan pourrait être généré en parallèle lors de l'insertion de données, car l'insertion avec des variables de table n'est possible que dans un seul flux.

Après avoir exécuté le script ci-dessus, une fenêtre avec la table d'index apparaîtra:



Ici, vous pouvez également afficher d'autres informations détaillées, telles que:

  1. une base de données
  2. nombre de sections
  3. date et heure du dernier appel
  4. compression
  5. groupe de fichiers

etc.
Les colonnes elles-mêmes peuvent être personnalisées:



Dans les cellules de la colonne Fix, vous pouvez choisir quelle action sera effectuée lors de l'optimisation. De plus, une fois la numérisation terminée, l'action par défaut est sélectionnée en fonction des paramètres sélectionnés:



Vous devez sélectionner les index souhaités pour le traitement.

En utilisant le menu principal, vous pouvez enregistrer le script (le même bouton démarre le processus d'optimisation d'index lui-même):



enregistrer le tableau dans différents formats (le même bouton vous permet d'ouvrir des paramètres détaillés pour l'analyse et l'optimisation des indices):



De plus, les informations peuvent être mises à jour en cliquant sur le troisième bouton à gauche dans le menu principal à côté de la loupe.

Un bouton avec une loupe vous permet de sélectionner la base de données souhaitée pour examen.

Il n'existe actuellement aucun système d'aide complet. Par conséquent, en appuyant sur le "?" cela provoquera simplement l'apparition d'une fenêtre modale contenant des informations de base sur le produit logiciel:



En plus de tout ce qui précède, le menu principal dispose d'une barre de recherche:



Lors du démarrage du processus d'optimisation d'index:



Toujours en bas de la fenêtre, vous pouvez voir le journal des actions effectuées:



Dans la fenêtre d'analyse détaillée et d'optimisation des index, vous pouvez configurer des options plus subtiles:



Suggestions pour l'application:

  1. permettre la mise à jour sélective des statistiques non seulement pour les index mais également de différentes manières (mise à jour complète ou partielle)
  2. permettent non seulement de sélectionner la base de données, mais aussi différents serveurs (c'est très pratique quand il y a beaucoup d'instances de MS SQL Server)
  3. pour une plus grande flexibilité d'utilisation, il est proposé d'envelopper les commandes dans les bibliothèques et de les exporter vers les commandes PowerShell, comme cela se fait, par exemple, ici: dbatools.io/commands
  4. permettent d'enregistrer et de modifier les paramètres personnels à la fois pour l'ensemble de l'application et, si nécessaire, pour chaque instance de MS SQL Server et chaque base de données
  5. des clauses 2 et 4, il découle de la volonté de faire des groupes sur les bases de données et des groupes sur les instances de MS SQL Server, pour lesquels les paramètres sont les mêmes
  6. rechercher des indices en double (complets et incomplets, qui diffèrent légèrement ou ne diffèrent que dans les colonnes incluses)
  7. Étant donné que SQLIndexManager est utilisé uniquement pour le SGBD MS SQL Server, vous devez refléter cela dans le nom, par exemple, comme suit: SQLIndexManager pour MS SQL Server
  8. Supprimez toutes les parties de l'application de l'interface graphique dans des modules séparés et réécrivez-les dans .NET Core 2.1

Au moment de la rédaction de cet article, l'article 6 des souhaits est en cours d'élaboration et il existe déjà un soutien sous la forme d'une recherche de doublons complets et similaires:



Les sources


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

More articles:


All Articles