Wie kann ich feststellen, ob auf eine Datenbanktabelle noch zugegriffen wird? Ich möchte so etwas wie einen "SELECT-Trigger".

Question

Ich habe eine sehr große Datenbank mit Hunderten von Tabellen, und nach vielen, vielen Produkt-Upgrades wird sicher die Hälfte davon nicht mehr verwendet. Wie kann ich feststellen, ob aus einer Tabelle aktiv ausgewählt wird? Ich kann nicht einfach Profiler verwenden - ich möchte nicht nur mehr als ein paar Tage beobachten, sondern es gibt auch Tausende von gespeicherten Prozeduren, und Profiler wird die SP-Aufrufe nicht in Tabellenzugriffsaufrufe übersetzen.

Das Einzige, was mir einfällt, ist, einen geclusterten Index für die betreffenden Tabellen zu erstellen und dann die sys.dm_db_index_usage_stats um zu sehen, ob es irgendwelche Suchvorgänge oder Scans für den geclusterten Index gibt, was bedeutet, dass Daten aus der Tabelle geladen wurden. Das Hinzufügen eines geclusterten Indexes für jede Tabelle ist jedoch eine schlechte Idee (aus einer Vielzahl von Gründen), da es nicht wirklich machbar ist.

Gibt es noch andere Möglichkeiten für mich? Ich habe mir immer eine Funktion wie einen "SELECT-Trigger" gewünscht, aber es gibt wahrscheinlich andere Gründe, warum SQL Server diese Funktion nicht hat.

LÖSUNG:

Danke, Remus, dass du mich auf den richtigen Weg gebracht hast. Unter Verwendung dieser Spalten habe ich das folgende SELECT erstellt, das genau das tut, was ich will.

  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

Blick in sys.dm_db_index_usage_stats . Die Spalten last_user_xxx enthalten das letzte Mal, dass auf die Tabelle durch Benutzeranfragen zugegriffen wurde. Diese Tabelle setzt ihre Verfolgung nach einem Server-Neustart zurück, so dass Sie sie eine Weile laufen lassen müssen, bevor Sie sich auf ihre Daten verlassen.

Answer 2

Re: Profiler, wenn Sie überwachen für SP:StmtCompleted die alle Anweisungen erfasst, die innerhalb einer gespeicherten Prozedur ausgeführt werden, so dass Tabellenzugriffe innerhalb einer Sproc abgefangen werden. Wenn nicht alles über gespeicherte Prozeduren abläuft, benötigen Sie möglicherweise auch die SQL:StmtCompleted Veranstaltung.

Es wird eine große Anzahl von Ereignissen geben, so dass es aufgrund der Größe der Aufzeichnung wahrscheinlich immer noch nicht praktikabel ist, eine Aufzeichnung über einen langen Zeitraum durchzuführen. Sie könnten jedoch einen Filter anwenden - z. B. wenn TextData den Namen der Tabelle enthält, auf die Sie prüfen wollen. Sie könnten eine Liste von Tabellennamen angeben, nach denen gefiltert werden soll, und diese nach und nach abarbeiten. So sollten Sie keine Trace-Ereignisse erhalten, wenn auf keine dieser Tabellen zugegriffen wurde.

Selbst wenn Sie der Meinung sind, dass dieser Ansatz für Sie nicht geeignet/umsetzbar ist, dachte ich, dass es sich lohnt, ihn zu vertiefen.

Eine andere Lösung wäre, eine globale Suche in Ihrem Quellcode durchzuführen, um Verweise auf die Tabellen zu finden. Sie können die gespeicherten Prozedurdefinitionen abfragen, um nach Übereinstimmungen für eine bestimmte Tabelle zu suchen, oder einfach ein komplettes Datenbankskript erstellen und dieses nach Tabellennamen durchsuchen.

Answer 3

Für SQL Server 2008 sollten Sie einen Blick auf SQL-Überprüfung . Damit können Sie viele Dinge prüfen, einschließlich Auswahlen in einer Tabelle und Berichte in einer Datei oder im Ereignisprotokoll.

Answer 4

Ich hatte im Sinn, mit den Benutzerrechten für verschiedene Tabellen zu spielen, aber dann erinnerte ich mich, dass man die Verfolgung mit einem ON LOGON-Trigger einschalten kann, und dass dies von Vorteil sein könnte:

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;

/

Dann können Sie Ihre Trace-Dateien überprüfen.

Answer 5

Diese Lösung funktioniert bei mir besser als die obige Lösung. Aber, ist immer noch begrenzt, dass der Server nicht neu gestartet wurde, als auch, aber immer noch gibt Ihnen eine gute Idee von Tabellen nicht verwendet.

SELECT [name]
      ,[object_id]
      ,[principal_id]
      ,[schema_id]
      ,[parent_object_id]
      ,[type]
      ,[type_desc]
      ,[create_date]
      ,[modify_date]
      ,[is_ms_shipped]
      ,[is_published]
      ,[is_schema_published]
  FROM [COMTrans].[sys].[all_objects]
  where object_id not in (
select object_id from sys.dm_db_index_usage_stats

)
and type='U'
order by name