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

데이터베이스 테이블이 더 이상 액세스되고 있는지 어떻게 알 수 있습니까? "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;

/

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

---

이 솔루션은 위의 솔루션보다 더 잘 작동합니다. 그러나 여전히 서버가 다시 시작되지 않았다는 한계가 있지만 여전히 사용되지 않은 테이블에 대한 좋은 아이디어를 제공합니다.

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

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