데이터베이스 테이블이 더 이상 액세스되고 있는지 어떻게 알 수 있습니까?

데이터베이스 테이블이 더 이상 액세스되고 있는지 어떻게 알 수 있습니까? "SELECT 트리거"와 같은 것을 원함

---

저는 수백 개의 테이블이있는 매우 큰 데이터베이스를 가지고 있으며, 많은 제품 업그레이드 후에 그 중 절반이 더 이상 사용되지 않을 것이라고 확신합니다. 테이블이 활발하게 선택되고 있는지 어떻게 알 수 있습니까? 프로파일 러 만 사용할 수는 없습니다. 며칠 이상 시청하고 싶을뿐만 아니라 수천 개의 저장 프로시 저도 있으며 프로파일 러는 SP 호출을 테이블 액세스 호출로 변환하지 않습니다.

내가 생각할 수있는 유일한 방법은 관심있는 테이블에 클러스터형 인덱스를 만든 다음를 모니터링 sys.dm_db_index_usage_stats하여 클러스터형 인덱스에 검색 또는 스캔이 있는지 확인하는 것입니다. 즉, 테이블의 데이터가로드되었음을 의미합니다. 그러나 모든 테이블에 클러스터형 인덱스를 추가하는 것은 실제로 가능하지 않기 때문에 여러 가지 이유로 나쁜 생각입니다.

다른 옵션이 있습니까? 저는 항상 "SELECT 트리거"와 같은 기능을 원했지만 SQL Server에 해당 기능이없는 다른 이유가있을 수 있습니다.

해결책:

저를 올바른 방향으로 안내 해준 Remus에게 감사합니다. 이러한 열을 사용하여 원하는대로 정확히 수행하는 다음 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)

---

sys.dm_db_index_usage_stats를 찾습니다 . last_user_xxx 열에는 사용자 요청에서 테이블에 마지막으로 액세스 한 시간이 포함됩니다. 이 테이블은 서버가 다시 시작된 후 추적을 재설정하므로 데이터에 의존하기 전에 잠시 실행 상태로 두어야합니다.

---

Re : Profiler, SP : StmtCompleted 를 모니터링하는 경우 저장 프로 시저 내에서 실행되는 모든 문을 캡처하여 sproc 내에서 테이블 액세스를 포착합니다. 모든 것이 저장 프로 시저를 거치지 않는 경우 SQL : StmtCompleted 이벤트 가 필요할 수도 있습니다 .

많은 수의 이벤트가 있으므로 추적 크기로 인해 오랫동안 추적하는 것은 여전히 ​​실용적이지 않습니다. 그러나 필터를 적용 할 수 있습니다. 예를 들어 TextData에 확인하려는 테이블 이름이 포함되어 있습니다. 언제든지 필터링 할 테이블 이름 목록을 제공하고 점차적으로 작업 할 수 있습니다. 따라서 해당 테이블에 액세스하지 않은 경우 추적 이벤트를 가져 오지 않아야합니다.

자신에게 적합하고 실행 가능한 접근 방식이 아니라고 느끼더라도 계속 확장 할 가치가 있다고 생각했습니다.

또 다른 해결책은 테이블에 대한 참조를 찾기 위해 소스 코드의 전역 검색을 수행하는 것입니다. 저장된 프로 시저 정의를 쿼리하여 주어진 테이블과 일치하는지 확인하거나 전체 데이터베이스 스크립트를 생성하고 테이블 이름에 대한 찾기를 수행 할 수 있습니다.

---

SQL Server 2008의 경우 SQL 감사를 살펴보아야 합니다. 이를 통해 테이블의 선택 및 파일 또는 이벤트 로그에 대한 보고서를 포함하여 많은 것을 감사 할 수 있습니다.

---

다음 쿼리는 쿼리 계획 캐시를 사용하여 캐시의 기존 계획에 테이블에 대한 참조가 있는지 확인합니다. 이는 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 + '%'

---

참고 사항 : 이러한 테이블을 삭제하려는 의도가 있다면 관련 데이터를 x 년 동안 유지해야하는 법적 의무를 고려해야 할 수도 있습니다.

---

다른 테이블에 대한 사용자 권한으로 플레이 할 것을 염두에 두었지만 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;

/

그런 다음 추적 파일을 확인할 수 있습니다.

---

This solution works better for me then the solution above. But, is still limted that the server was not re-started as well, but still gives you a good idea of tables not used.

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

ReferenceURL : https://stackoverflow.com/questions/2155594/how-can-i-tell-if-a-database-table-is-being-accessed-anymore-want-something-lik