Comment savoir si une table de base de données est en train d'être utilisée? Vous voulez quelque chose comme un "déclencheur SELECT"

Question

J'ai une très grande base de données avec des centaines de tables, et après beaucoup, beaucoup de mises à niveau de produits, je suis sûr que la moitié d'entre eux ne sont pas plus utilisées. Comment puis-je savoir si un tableau est activement sélectionnées à partir de? Je ne peut pas utiliser de générateur de profils - non seulement je veux regarder pour plus de quelques jours, mais il y a des milliers de procédures stockées en tant que bien, et le générateur de profils de ne pas traduire le PS appelle dans l'accès à la table des appels.

La seule chose que je peux penser à est de créer un index cluster sur les tables de l'intérêt, et puis surveiller l' sys.dm_db_index_usage_stats pour voir s'il y a des recherches ou des analyses sur l'index cluster, ce qui signifie que les données de la table a été chargé. Cependant, l'ajout d'un index cluster sur chaque tableau est une mauvaise idée (pour diverses raisons), que n'est pas vraiment possible.

Existe-il d'autres options? J'ai toujours voulu une fonction comme un "déclencheur", mais il y a probablement d'autres raisons pour que SQL Server n'a pas cette fonctionnalité soit.

SOLUTION:

Merci, Remus, pour me pointer dans la bonne direction. L'utilisation de ces colonnes, j'ai créé la sélection suivante, qui fait exactement ce que je veux.

  WITH LastActivity (ObjectID, LastAction) AS 
  (
       SELECT object_id AS TableName,
              last_user_seek as LastAction
         FROM sys.dm_db_index_usage_stats u
        WHERE database_id = db_id(db_name())
        UNION 
       SELECT object_id AS TableName,
              last_user_scan as LastAction
         FROM sys.dm_db_index_usage_stats u
        WHERE database_id = db_id(db_name())
        UNION
       SELECT object_id AS TableName,
              last_user_lookup as LastAction
         FROM sys.dm_db_index_usage_stats u
        WHERE database_id = db_id(db_name())
  )
  SELECT OBJECT_NAME(so.object_id) AS TableName,
         MAX(la.LastAction) as LastSelect
    FROM sys.objects so
    LEFT
    JOIN LastActivity la
      on so.object_id = la.ObjectID
   WHERE so.type = 'U'
     AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)

Answer 1

Regardez dans sys.dm_db_index_usage_stats . Les colonnes last_user_xxx contiendront la dernière fois que la table a été accédée à partir des demandes de l'utilisateur. Cette table réinitialise son suivi après le redémarrage d'un serveur. Vous devez donc le laisser fonctionner pendant un certain temps avant de vous fier à ses données.

Answer 2

Re: générateur de profils, si vous faites le suivi de SP:StmtCompleted, qui permettra de saisir toutes les instructions qui s'exécutent à l'intérieur d'une procédure stockée, donc, ça va attraper table accède à l'intérieur d'une procédure stockée. Si tout ne se passe via des procédures stockées, vous aurez également besoin du SQL:StmtCompleted événement.

Il y aura un grand nombre d'événements donc ce n'est probablement pas encore pratique pour tracer sur une longue période en raison de la taille de la trace. Cependant, vous pouvez appliquer un filtre par exemple, où TextData contient le nom de votre table que vous souhaitez vérifier. Vous pouvez donner une liste de noms de table pour filtre à tout moment et de travailler à travers eux petit à petit. Donc, vous ne devriez pas obtenir aucune trace des événements si aucun de ces tables ont été consultés.

Même si vous sentez que c'est pas un bon/approche viable pour vous, j'ai pensé qu'il valait la peine de l'expansion sur.

Une autre solution serait de faire une recherche globale de votre code source pour trouver des références aux tables. Vous pouvez faire une requête de la procédure stockée définitions pour les matchs, pour un tableau, ou tout simplement de générer une base de données complète de script et faites une recherche sur que pour les noms de table.

Answer 3

Pour SQL Server 2008, vous devriez jeter un coup d'œil à l' audit SQL . Cela vous permet d’auditer de nombreuses choses, y compris les sélections sur une table et les rapports dans un fichier ou un journal des événements.

Answer 4

Remarque: si votre intention est de supprimer ces tables, vous devrez peut-être tenir compte des obligations légales qui vous imposent de conserver les données concernées de toute façon pendant x ans.

Answer 5

J'avais en tête de jouer avec les permissions des utilisateurs pour différentes tables, mais je me suis alors rappelé que vous pouvez activer la trace avec un déclencheur ON LOGON, cela pourrait vous être avantageux:

 CREATE OR REPLACE TRIGGER SYS.ON_LOGON_ALL

AFTER LOGON ON DATABASE
WHEN (

USER 'MAX'

)
BEGIN

EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';

--EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever level 12''';

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

/
 

Ensuite, vous pouvez vérifier vos fichiers de trace.