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 .
Il existe de nombreuses solutions, à la fois payantes et gratuites, pour cela. Par exemple, il existe un outil prêt à l'emploi , basé sur une méthode d'optimisation adaptative des index.
Nous allons maintenant examiner l'outil gratuit , dont l'auteur est .
La principale différence technique entre SQLIndexManager et plusieurs autres analogues est également expliquée par l'auteur. et .
Dans cet article, nous examinerons le projet et les possibilités d'exploitation de cette solution logicielle.
Cet outil est discuté. .
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 :
- Index
- Query
- QueryEngine
- 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 :
- Obtention d'informations sur le SGBD
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - 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 , vous pouvez choisir les données à partir de la vue . 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 :
- base de données
- nombre de sections
- date et heure de la dernière consultation
- etc.) de 10% à 100%.
- 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 :
- 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)
- 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)
- 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 :
- 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
- 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
- implémenter la recherche de doublons d'index (complets et incomplets, qui diffèrent légèrement ou seulement par des colonnes incluses)
- 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
- 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
Source : habr.com
