Как узнать, больше ли доступ к таблице базы данных? Хотите что-то вроде "SELECT trigger"

У меня очень большая база данных с сотнями таблиц, и после многих, многих обновлений продукта, я уверен, что половина из них больше не используется. Как определить, активно ли выбран таблица? Я не могу просто использовать Profiler - не только я хочу наблюдать более нескольких дней, но также есть тысячи хранимых процедур, а профилировщик не будет переводить вызовы SP в вызовы доступа к таблице.

Единственное, о чем я могу думать, это создать кластерный индекс в интересующих таблицах, а затем отслеживать sys.dm_db_index_usage_stats, чтобы увидеть, есть ли какие-либо поиски или сканирование в кластерном индексе, что означает, что данные из таблицы были загружен. Однако добавление кластеризованного индекса в каждую таблицу является плохим решением (по каким-либо причинам), поскольку это не реально.

Существуют ли другие варианты? Я всегда хотел использовать функцию типа "SELECT trigger", но, вероятно, есть и другие причины, по которым SQL Server не имеет этой функции.

РЕШЕНИЕ:

Спасибо, Ремус, за то, что указал мне в правильном направлении. Используя эти столбцы, я создал следующий SELECT, который делает именно то, что я хочу.

  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)

Ответ 1

Посмотрите sys.dm_db_index_usage_stats. Столбцы last_user_xxx будут содержать последний раз, когда таблица была получена из пользовательских запросов. Эта таблица сбрасывает отслеживание после перезапуска сервера, поэтому вы должны оставить его работающим некоторое время, прежде чем полагаться на его данные.

Ответ 2

Re: Profiler, если вы отслеживаете SP: StmtCompleted, который будет захватывать все утверждения, выполняемые в хранимой процедуре, так что будет ловить таблицу доступ в sproc. Если не все проходит через хранимые процедуры, вам может понадобиться событие SQL: StmtCompleted.

Будет большое количество событий, поэтому, вероятно, еще не так долго можно отслеживать из-за размера трассировки. Однако вы можете применить фильтр - например, где TextData содержит имя вашей таблицы, которую вы хотите проверить. Вы можете указать список имен таблиц, которые будут фильтроваться в любой момент, и работать с ними постепенно. Таким образом, вы не должны получать никаких событий трассировки, если ни одна из этих таблиц не была доступна.

Даже если вы считаете, что это не подходящий/жизнеспособный подход для вас, я думал, что это стоит расширять.

Другим решением было бы сделать глобальный поиск исходного кода, чтобы найти ссылки на таблицы. Вы можете запросить определения хранимой процедуры для проверки совпадений для данной таблицы или просто сгенерировать полную базу данных script и выполнить поиск по имени для таблиц.

Ответ 3

Для SQL Server 2008 вы должны взглянуть на SQL Auditing. Это позволяет вам проверять многие вещи, включая выборки в таблице и отчеты в файл или журнал событий.

Ответ 4

Примечание: если вы намерены отказаться от этих таблиц, вам, возможно, придется рассмотреть юридические обязательства, которые налагают на вас обязательство хранить данные в любом случае в течение x лет.

Ответ 5

Я имел в виду играть с разрешениями пользователей для разных таблиц, но потом я вспомнил, что вы можете включить трассировку с помощью триггера ON LOGON, который вам может пригодиться:

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;

/

Затем вы можете проверить файлы трассировки.

Ответ 6

В следующем запросе используется кэш плана запроса, чтобы узнать, есть ли ссылка на таблицу в каком-либо из существующих планов в кеше. Это не гарантирует точности на 100% (поскольку планы запросов сбрасываются при наличии ограничений памяти), но может быть использовано для получения некоторых сведений об использовании таблиц.

SELECT schema_name(schema_id) as schemaName, t.name as tableName,
    databases.name,
dm_exec_sql_text.text AS TSQL_Text,
dm_exec_query_stats.creation_time, 
dm_exec_query_stats.execution_count,
dm_exec_query_stats.total_worker_time AS total_cpu_time,
dm_exec_query_stats.total_elapsed_time, 
dm_exec_query_stats.total_logical_reads, 
dm_exec_query_stats.total_physical_reads, 
dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases ON dm_exec_sql_text.dbid = databases.database_id
RIGHT JOIN sys.tables t (NOLOCK) ON cast(dm_exec_query_plan.query_plan as varchar(max)) like '%' + t.name + '%'

Ответ 7

Это решение работает лучше для меня, чем решение выше. Но, все еще ограничено, что сервер не был снова запущен, но все же дает вам представление о не используемых таблицах.

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