Comment puis-je surveiller et de trouver des index inutilisés dans la base de données sql

Je tiens à surveiller l'utilisation des index pour une base de données sql, afin de trouver des index inutilisés et de les en exclure. Comment puis-je surveiller l'utilisation des index le plus efficacement? Et les scripts qui pourraient être utiles?

(Je suis au courant de cette question porte sur l'identification des objets inutilisés, mais cela s'applique uniquement à l'exécution de sql server. Je tiens à surveiller l'utilisation des index sur une période de temps...)

OriginalL'auteur Ole Lynge | 2009-09-21

7

Actuellement (SQL Server 2005 - 2008) l'index SQL stats informations ne sont conservées en mémoire et donc, vous avez à faire le travail vous-même si vous voulez avoir de la qui a persisté à travers redémarre et se détache de la base de données.

Ce que je fais habituellement, c'est que je créer une tâche qui s'exécute tous les jours et prend un instantané de l'information trouvée dans le sys.dm_db_index_usage_stats table, dans un tableau personnalisé que j'ai créer pour la base de données en question.

Cela semble fonctionner assez bien jusqu'à ce qu'une future version de SQL qui sera le soutien persistant de l'indice de statistiques d'utilisation.

OriginalL'auteur Mike Dinescu

C'est une question intéressante. J'ai travaillé sur cette même question au cours de la dernière semaine. Il y a un système de table appelée dm_db_index_usage_stats qui contient l'utilisation de statistiques sur les index.

Indices Qui n'Apparaissent Jamais dans les Statistiques d'Utilisation de la Table de

Cependant, de nombreux indices n'apparaissent pas dans ce tableau. La requête de David Andres posté listes de tous les indices de cette affaire. J'ai mis à jour un petit peu d'ignorer les clés primaires, qui ne devrait probablement pas être supprimé, même si elles ne sont pas toujours utilisés. J'ai aussi rejoint sur le dm_db_index_physical_stats table pour obtenir d'autres informations, y compris le nombre de pages, le Total de la Taille de l'Index, et le Pourcentage de Fragmentation. Une note intéressante est que les index qui sont retournés par cette requête ne semblent pas apparaître dans le Rapport SQL pour l'Indice des Statistiques d'Utilisation.

DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())

SELECT  Databases.Name AS [Database],
        Objects.NAME AS [Table],
        Indexes.NAME AS [Index],
        Indexes.INDEX_ID,
        PhysicalStats.page_count as [Page Count],
        CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Index Size (MB)],
        CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragmentation (%)]
FROM SYS.INDEXES Indexes
    INNER JOIN SYS.OBJECTS Objects ON Indexes.OBJECT_ID = Objects.OBJECT_ID
    LEFT JOIN sys.dm_db_index_physical_stats(@dbid, null, null, null, null) PhysicalStats
        on PhysicalStats.object_id = Indexes.object_id and PhysicalStats.index_id = indexes.index_id
    INNER JOIN sys.databases Databases
        ON Databases.database_id = PhysicalStats.database_id
WHERE OBJECTPROPERTY(Objects.OBJECT_ID,'IsUserTable') = 1
    AND Indexes.type = 2    -- Nonclustered indexes
    AND   Indexes.INDEX_ID NOT IN (
            SELECT UsageStats.INDEX_ID
            FROM SYS.DM_DB_INDEX_USAGE_STATS UsageStats
            WHERE UsageStats.OBJECT_ID = Indexes.OBJECT_ID
                AND   Indexes.INDEX_ID = UsageStats.INDEX_ID
                AND   DATABASE_ID = @dbid)
ORDER BY PhysicalStats.page_count DESC,
         Objects.NAME,
         Indexes.INDEX_ID,
         Indexes.NAME ASC

Indices Qui Apparaissent dans les Statistiques d'Utilisation de la Table, Mais ne Sont Jamais Utilisés

Il y a d'autres indices qui apparaissent dans le dm_db_index_usage_stats table, mais qui n'ont jamais été utilisés pour l'utilisateur cherche, des analyses, ou des recherches. Cette requête permettra d'identifier les indices qui tombent dans cette catégorie. D'ailleurs, contrairement à l'index retourné à partir d'une autre requête, l'index de retour dans cette requête peut être vérifié sur le Rapport SQL par l'Indice des Statistiques d'Utilisation.

J'ai ajouté un nombre de pages Minimum, ce qui me permet d'abord de se concentrer sur et supprimer les indices qui prennent beaucoup d'espace de rangement.

DECLARE @MinimumPageCount int
SET @MinimumPageCount = 500
SELECT  Databases.name AS [Database], 
Indexes.name AS [Index],
Objects.Name AS [Table],                    
PhysicalStats.page_count as [Page Count],
CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Index Size (MB)],
CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragmentation (%)],
ParititionStats.row_count AS [Row Count],
CONVERT(decimal(18,2), (PhysicalStats.page_count * 8.0 * 1024) / ParititionStats.row_count) AS [Index Size/Row (Bytes)]
FROM sys.dm_db_index_usage_stats UsageStats
INNER JOIN sys.indexes Indexes
ON Indexes.index_id = UsageStats.index_id
AND Indexes.object_id = UsageStats.object_id
INNER JOIN sys.objects Objects
ON Objects.object_id = UsageStats.object_id
INNER JOIN SYS.databases Databases
ON Databases.database_id = UsageStats.database_id       
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS PhysicalStats
ON PhysicalStats.index_id = UsageStats.Index_id 
and PhysicalStats.object_id = UsageStats.object_id
INNER JOIN SYS.dm_db_partition_stats ParititionStats
ON ParititionStats.index_id = UsageStats.index_id
and ParititionStats.object_id = UsageStats.object_id        
WHERE UsageStats.user_scans = 0
AND UsageStats.user_seeks = 0
AND UsageStats.user_lookups = 0
AND PhysicalStats.page_count > @MinimumPageCount    -- ignore indexes with less than 500 pages of memory
AND Indexes.type_desc != 'CLUSTERED'                -- Exclude primary keys, which should not be removed    
ORDER BY [Page Count] DESC

J'espère que cette aide.

Dernière Pensée

Bien sûr, une fois que les indices sont identifiés comme candidats pour l'enlèvement, un examen attentif doit toujours être employé pour s'assurer que c'est une bonne décision de le faire.

Pour plus d'informations, voir L'identification des Index Inutilisés dans une Base de données SQL Server

OriginalL'auteur Narnian

Tiré ce chiot hors de http://blog.sqlauthority.com/2008/02/11/sql-server-2005-find-unused-indexes-of-current-database/. Notez que cela fonctionne pour 2005 et au-dessus. La clé est la JOIN à la SYS.DM_DB_INDEX_USAGE_STATS système de de table.

USE AdventureWorks
GO
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND   I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND   I.INDEX_ID = S.INDEX_ID
AND   DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO

OriginalL'auteur David Andres

J'ai modifié Jean Pasquet interrogations ici: L'identification des Index Inutilisés dans une Base de données SQL Server de retour indices de 10 ou moins de temps, réunies les résultats qui ne sont pas dans les statistiques d'utilisation des tables, exclure des tas d'indices et les contraintes unique ou de clé primaire index, et enfin d'exclure les index avec zéro pages.

Être prudent avec les résultats de cette requête, il est préférable d'utiliser dans la production où les indices sont réellement utilisées de la façon dont vous vous attendez. Si vous effectuez une requête sur une base de données avec reconstruit ou abandonné/recréé indices ou sur une récente sauvegarde de base de données, vous pourriez obtenir de faux positifs (indices qui, normalement, devait s'habituer, mais ne sont pas en raison de circonstances spéciales). Pas sûr à utiliser lors du test ou de dev environnements de décider de supprimer les index. Comme Narnian dit, cette requête permettent seulement d'identifier les candidats pour l'enlèvement de votre examen attentif.

USE [DatabaseName]
DECLARE @MinimumPageCount int
SET @MinimumPageCount = 500
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
-- GET UNUSED INDEXES THAT APPEAR IN THE INDEX USAGE STATS TABLE
SELECT  
Databases.name AS [Database]
,object_name(Indexes.object_id) AS [Table]
,Indexes.name AS [Index]
,PhysicalStats.page_count as [Page Count]
,CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Index Size (MB)]
,CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragmentation (%)]
,ParititionStats.row_count AS [Row Count]
,CONVERT(decimal(18,2), (PhysicalStats.page_count * 8.0 * 1024) / ParititionStats.row_count) AS [Index Size Per Row (Bytes)]
,1 AS [Appears In Usage Stats Table]
FROM sys.dm_db_index_usage_stats UsageStats
INNER JOIN sys.indexes Indexes
ON Indexes.index_id = UsageStats.index_id AND Indexes.object_id = UsageStats.object_id
INNER JOIN SYS.databases Databases
ON Databases.database_id = UsageStats.database_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(),NULL,NULL,NULL,NULL) AS PhysicalStats
ON PhysicalStats.index_id = UsageStats.Index_id AND PhysicalStats.object_id = UsageStats.object_id
INNER JOIN SYS.dm_db_partition_stats ParititionStats
ON ParititionStats.index_id = UsageStats.index_id AND ParititionStats.object_id = UsageStats.object_id
WHERE 
UsageStats.user_scans <= 10
AND UsageStats.user_seeks <= 10
AND UsageStats.user_lookups <= 10
-- exclude heap indexes
AND Indexes.name IS NOT NULL
-- ignore indexes with less than a certain number of pages of memory
AND PhysicalStats.page_count > @MinimumPageCount
-- Exclude primary keys, which should not be removed
AND Indexes.is_primary_key = 0
-- ignore unique constraints - those shouldn't be removed 
AND Indexes.is_unique_constraint = 0 
AND Indexes.is_unique = 0
UNION ALL 
(
-- GET UNUSED INDEXES THAT DO **NOT** APPEAR IN THE INDEX USAGE STATS TABLE
SELECT  
Databases.Name AS [Database]
,Objects.NAME AS [Table]
,Indexes.NAME AS [Index]
,PhysicalStats.page_count as [Page Count]
,CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Index Size (MB)]
,CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragmentation (%)]
,-1 AS [Row Count]
,-1 AS [Index Size Per Row (Bytes)]
,0 AS [Appears In Usage Stats Table]
FROM SYS.INDEXES Indexes
INNER JOIN SYS.OBJECTS Objects 
ON Indexes.OBJECT_ID = Objects.OBJECT_ID
LEFT JOIN sys.dm_db_index_physical_stats(@dbid, null, null, null, null) PhysicalStats
ON PhysicalStats.object_id = Indexes.object_id AND PhysicalStats.index_id = indexes.index_id
INNER JOIN sys.databases Databases
ON Databases.database_id = PhysicalStats.database_id
WHERE 
Objects.type = 'U' -- Is User Table
-- exclude heap indexes
AND Indexes.name IS NOT NULL
-- exclude empty tables
AND PhysicalStats.page_count <> 0
-- Exclude primary keys, which should not be removed
AND Indexes.is_primary_key = 0
-- ignore unique constraints - those shouldn't be removed 
AND Indexes.is_unique_constraint = 0 
AND Indexes.is_unique = 0
AND Indexes.INDEX_ID NOT IN 
(
SELECT UsageStats.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS UsageStats
WHERE 
UsageStats.OBJECT_ID = Indexes.OBJECT_ID
AND Indexes.INDEX_ID = UsageStats.INDEX_ID
AND DATABASE_ID = @dbid
)
)
ORDER BY [Table] ASC, [Total Index Size (MB)] DESC

OriginalL'auteur HAL9000

0

Vous devriez jeter un oeil à Brent Ozars sp_BlitzIndex. Cette procédure stockée listes entre autres unsused index. Il répertorie les troubles dans un rapport. Pour chaque entrée, une URL est donnée qui explique ce qu'il faut chercher et comment gérer le problème.

OriginalL'auteur TaXXoR

Vous devez vous connecter pour publier un commentaire.