Aperçu de l'outil gratuit SQLIndexManager

Comme nous le savons, les index jouent un rôle crucial dans les SGBD, offrant une recherche rapide des enregistrements nécessaires. Il est donc important de les entretenir régulièrement. Il existe de nombreux matériaux sur l'analyse et l'optimisation, notamment sur Internet. Par exemple, un examen récent de ce sujet a été réalisé dans cet article.

Il existe de nombreuses solutions, à la fois payantes et gratuites, pour cela. Par exemple, il existe un outil prêt à l'emploi la solution, basé sur une méthode d'optimisation adaptative des index.

Nous allons maintenant examiner l'outil gratuit SQLIndexManager, dont l'auteur est AlanDenton.

La principale différence technique entre SQLIndexManager et plusieurs autres analogues est également expliquée par l'auteur. ici et ici.

Dans cet article, nous examinerons le projet et les possibilités d'exploitation de cette solution logicielle.

Cet outil est discuté. ici.
Au fil du temps, la plupart des remarques et des bugs ont été corrigés.

Nous allons maintenant aborder l'outil SQLIndexManager lui-même.

L'application est développée en C# sur le .NET Framework 4.5 avec Visual Studio 2017 et utilise DevExpress pour les formulaires :

et se présente comme suit :

Toutes les requêtes sont générées dans les fichiers suivants :

1. Index
2. Query
3. QueryEngine
4. ServerInfo

Lors de la connexion à la base de données et de l'envoi de requêtes au SGBD, l'application se connecte comme suit :

ApplicationName="SQLIndexManager"

Lors du lancement de l'application, une fenêtre modale s'ouvrira pour ajouter une connexion :

Pour l'instant, le chargement complet de la liste de toutes les instances MS SQL Server disponibles sur les réseaux locaux ne fonctionne pas.

Il est également possible d'ajouter une connexion à l'aide du bouton le plus à gauche dans le menu principal :

Les requêtes suivantes vont alors être exécutées sur le SGBD :

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 la liste des bases de données disponibles avec leurs propriétés résumées

   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 l'exécution des scripts ci-dessus, une fenêtre apparaîtra contenant un résumé des bases de données de l'instance MS SQL Server sélectionnée :

Il convient de noter que les informations étendues sont affichées en fonction des droits. S'il y a sysadmin, vous pouvez choisir les données à partir de la vue sys.master_files. S'il n'y a pas de tels droits, moins de données sont simplement renvoyées pour ne pas ralentir la requête.

Ici, il est nécessaire de sélectionner les bases de données qui vous intéressent et de cliquer sur le bouton "OK".

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

Analyse de l'état des index

déclarer @Fragmentation flottant=15;
déclarer @MinIndexSize bigint=768;
déclarer @MaxIndexSize bigint=1048576;
déclarer @PreDescribeSize bigint=32768;

SET NOCOUNT ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF

SI OBJECT_ID('tempdb.dbo.#AllocationUnits') EST NON NULL
    SUPPRIMER TABLE #AllocationUnits

CREER TABLE #AllocationUnits (
      ContainerID   BIGINT PRIMARY KEY
    , ReservedPages BIGINT NON NULL
    , UsedPages     BIGINT NON NULL
)

INSERER DANS #AllocationUnits (ContainerID, ReservedPages, UsedPages)
SELECT [container_id]
     , SOMME([total_pages])
     , SOMME([used_pages])
FROM sys.allocation_units WITH(NOLOCK)
GROUPER PAR [container_id]
AYANT SOMME([total_pages]) ENTRE @MinIndexSize ET @MaxIndexSize

SI OBJECT_ID('tempdb.dbo.#ExcludeList') EST NON NULL
    SUPPRIMER TABLE #ExcludeList

CREER TABLE #ExcludeList (ID INT PRIMARY KEY)

INSERER DANS #ExcludeList
SELECT [object_id]
FROM sys.objects WITH(NOLOCK)
OÙ [type] IN ('V', 'U')
    ET ( [is_ms_shipped] = 1 )

SI OBJECT_ID('tempdb.dbo.#Partitions') EST NON NULL
    SUPPRIMER TABLE #Partitions

SELECT [object_id]
     , [index_id]
     , [partition_id]
     , [partition_number]
     , [rows]
     , [data_compression]
DANS #Partitions
FROM sys.partitions WITH(NOLOCK)
OÙ [object_id] > 255
    ET [rows] > 0
    ET [object_id] NON IN (SELECT * FROM #ExcludeList)

SI OBJECT_ID('tempdb.dbo.#Indexes') EST NON NULL
    SUPPRIMER TABLE #Indexes

CREER TABLE #Indexes (
      ObjectID         INT NON NULL
    , IndexID          INT NON NULL
    , IndexName        SYSNAME NULL
    , PagesCount       BIGINT NON NULL
    , UnusedPagesCount BIGINT NON NULL
    , PartitionNumber  INT NON NULL
    , RowsCount        BIGINT NON NULL
    , IndexType        TINYINT NON NULL
    , IsAllowPageLocks BIT NON NULL
    , DataSpaceID      INT NON NULL
    , DataCompression  TINYINT NON NULL
    , IsUnique         BIT NON NULL
    , IsPK             BIT NON NULL
    , FillFactorValue  INT NON NULL
    , IsFiltered       BIT NON NULL
    , PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)

INSERER DANS #Indexes
SELECT ObjectID         = i.[object_id]
     , IndexID          = i.index_id
     , IndexName        = i.[name]
     , PagesCount       = a.ReservedPages
     , UnusedPagesCount = CASE QUAND ABS(a.ReservedPages - a.UsedPages) > 32 ALORS a.ReservedPages - a.UsedPages SINON 0 FIN
     , 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] ET p.[index_id] = i.[index_id] 
OÙ i.[type] IN (0, 1, 2, 5, 6)
    ET i.[object_id] > 255

DÉCLARER @files TABLE (ID INT PRIMARY KEY)
INSERER DANS @files
SELECT DISTINCT [data_space_id]
FROM sys.database_files WITH(NOLOCK)
OÙ [state] != 0
    ET [type] = 0

SI @@ROWCOUNT > 0 DÉBUT

    SUPPRIMER DE i
    DE #Indexes i
    LEFT JOIN sys.destination_data_spaces dds WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] ET i.PartitionNumber = dds.[destination_id]
    OÙ ISNULL(dds.[data_space_id], i.DataSpaceID) IN (SELECT * FROM @files)

FIN


DÉCLARER @DBID   INT
      , @DBNAME SYSNAME

SET @DBNAME = DB_NAME()
SELECT @DBID = [database_id]
FROM sys.databases WITH(NOLOCK)
OÙ [name] = @DBNAME

SI OBJECT_ID('tempdb.dbo.#Fragmentation') EST NON NULL
    SUPPRIMER TABLE #Fragmentation

CREER TABLE #Fragmentation (
      ObjectID         INT NON NULL
    , IndexID          INT NON NULL
    , PartitionNumber  INT NON NULL
    , Fragmentation    FLOAT NON NULL
    , PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)

INSERER DANS #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, 'LIMITÉ') r
OÙ i.PagesCount = @Fragmentation
        OU
            i.PagesCount > @PreDescribeSize
        OU
            i.IndexType IN (5, 6)
    )

Comme on peut le voir dans les requêtes elles-mêmes, les tables temporaires sont souvent utilisées. Cela est fait pour éviter les recompilations, et dans le cas d'un schéma volumineux, un plan peut être généré en parallèle lors de l'insertion de données, car l'insertion avec des variables de table ne peut se faire que dans un seul thread.

Après l'exécution du script ci-dessus, une fenêtre s'ouvrira avec un tableau d'index :

Ici, il est également possible de fournir d'autres informations détaillées, telles que :

1. base de données
2. nombre de sections
3. date et heure de la dernière consultation
4. etc.) de 10% à 100%.
5. groupe de fichiers

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

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

Vous devez choisir les index nécessaires pour le traitement.

Avec le menu principal, vous pouvez non seulement enregistrer le script (ce même bouton lance également le processus d'optimisation des index) :

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

Vous pouvez également mettre à jour les informations en cliquant sur le troisième bouton à gauche dans le menu principal à côté de la loupe.

Le bouton avec la loupe permet de sélectionner les bases de données nécessaires à l'examen.

Il n'y a pas encore de système d'aide complet. Par conséquent, en cliquant sur le bouton '?', une fenêtre modale apparaîtra simplement avec des informations de base sur le produit :

En plus de tout ce qui a été décrit ci-dessus, il y a une barre de recherche dans le menu principal :

Lors du lancement du processus d'optimisation des index :

Vous pouvez également consulter le journal des actions effectuées en bas de la fenêtre :

Dans la fenêtre des paramètres détaillés pour l'analyse et l'optimisation des index, vous pouvez configurer des options plus fines :

Suggestions pour l'application :

1. rendre possible le rafraîchissement sélectif des statistiques non seulement pour les index mais aussi de différentes manières (rafraîchir complètement ou partiellement)
2. permettre non seulement le choix de la BDD, mais aussi de différents serveurs (c'est très pratique lorsqu'il y a de nombreuses instances de MS SQL Server)
3. pour plus de flexibilité dans l'utilisation, il est proposé d'encapsuler des commandes dans des bibliothèques et de les afficher en tant que commandes PowerShell, comme c'est fait, par exemple, ici :
4. dbatools.io/commands
5. rendre possible la sauvegarde et la modification des paramètres personnels tant pour l'ensemble de l'application que, si nécessaire, pour chaque instance de MS SQL Server et chaque base de données
6. des points 2 et 4 découle le souhait de créer des groupes par bases de données et des groupes par instances de MS SQL Server, pour lesquels les paramètres sont identiques
7. implémenter la recherche de doublons d'index (complets et incomplets, qui diffèrent légèrement ou seulement par des colonnes incluses)
8. comme SQLIndexManager est utilisé uniquement pour les systèmes de gestion de bases de données MS SQL Server, il est nécessaire de le refléter dans le nom, par exemple comme suit : SQLIndexManager pour MS SQL Server
9. extrait toutes les parties non GUI de l'application dans des modules séparés et réécrit en .NET Core 2.1

au moment de la rédaction de l'article, le point 6 des souhaits est en cours de développement actif et dispose déjà d'un support sous forme de recherche de doublons complets et similaires :

Sources

• méthode adaptative d'optimisation des index
• outil SQLIndexManager
• aperçu technique de l'outil SQLIndexManager
• discussion sur l'outil SQLIndexManager
• SQL Index Manager – Outil GUI gratuit pour la maintenance des index sur SQL Server et Azure
• Analyse et optimisation des index de SQL Server

Source : habr.com