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

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

저는 수백 개의 테이블이 있는 매우 큰 데이터베이스를 가지고 있습니다. 그리고 많은 제품 업그레이드 후에, 그 중 절반은 더 이상 사용되지 않을 것입니다.테이블이 현재 선택 중인지 확인하는 방법은 무엇입니까?프로파일러를 그냥 사용할 수는 없습니다. 며칠 이상 지켜보고 싶을 뿐만 아니라 수천 개의 저장 프로시저가 SP 호출을 테이블 액세스 호출로 변환하지 않습니다.

관심 테이블에 클러스터된 인덱스를 생성한 다음 모니터링할 수 있는 유일한 방법은 다음과 같습니다.sys.dm_db_index_usage_stats클러스터된 인덱스에 검색 또는 검색이 있는지 확인합니다. 즉, 테이블의 데이터가 로드되었음을 의미합니다.그러나 모든 테이블에 클러스터된 인덱스를 추가하는 것은 (여러 가지 이유로) 실제로 실현 가능하지 않기 때문에 좋지 않습니다.

제가 선택할 수 있는 다른 방법이 있습니까?저는 항상 "SELECT 트리거"와 같은 기능을 원했지만 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)

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

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

이벤트 수가 많을 것이기 때문에 트레이스의 크기 때문에 오랜 시간에 걸쳐 추적하는 것은 여전히 실용적이지 않을 수 있습니다.그러나 필터(예: TextData에 확인할 테이블 이름이 포함되어 있는 경우)를 적용할 수 있습니다.한 번에 필터링할 테이블 이름 목록을 지정하고 단계적으로 작업할 수 있습니다.따라서 이러한 테이블에 액세스하지 않은 경우 추적 이벤트를 받지 않아야 합니다.

당신에게 적합하고 실행 가능한 접근법이 아니라고 생각하더라도, 저는 그것이 확장할 가치가 있다고 생각했습니다.

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

SQL Server 2008의 경우 SQL Auditing을 살펴봐야 합니다.이렇게 하면 테이블의 선택 항목과 파일 또는 이벤트 로그에 대한 보고서를 포함하여 여러 가지 사항을 감사할 수 있습니다.

다음 쿼리는 쿼리 계획 캐시를 사용하여 캐시에 있는 기존 계획에 테이블에 대한 참조가 있는지 확인합니다.이는 (메모리 제약이 있는 경우 쿼리 계획이 플러시되므로) 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 + '%'

다른 테이블에 대한 사용자 권한을 사용하려고 했지만 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