sql-server - nonclustered - sql server index types

¿Cómo puedo saber si ya se está accediendo a una tabla de base de datos? Quieres algo así como un "gatillo SELECT"

Question

Tengo una base de datos muy grande con cientos de tablas, y después de muchas, muchas actualizaciones de productos, estoy seguro de que la mitad de ellas ya no se usan. ¿Cómo puedo saber si se está seleccionando activamente una tabla? No puedo usar Profiler, no solo quiero verlo durante más de unos pocos días, sino que también hay miles de procedimientos almacenados, y Profiler no trasladará las llamadas de SP a las llamadas de acceso a la tabla.

Lo único que se me ocurre es crear un índice agrupado en las tablas de interés y luego supervisar sys.dm_db_index_usage_stats para ver si hay búsquedas o escaneos en el índice agrupado, lo que significa que se cargaron los datos de la tabla. Sin embargo, agregar un índice agrupado en cada tabla es una mala idea (por varios motivos), ya que no es realmente factible.

¿Hay otras opciones que tengo? Siempre quise una característica como "SELECT trigger", pero probablemente haya otras razones por las cuales SQL Server no tiene esa característica tampoco.

SOLUCIÓN:

Gracias, Remus, por apuntarme en la dirección correcta. Usando esas columnas, he creado el siguiente SELECT, que hace exactamente lo que quiero.

  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

Esta solución funciona mejor para mí que la solución anterior. Pero, todavía está limitado que el servidor no se reinició también, pero todavía le da una buena idea de las tablas no utilizadas.

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

Answer 2

Mire en sys.dm_db_index_usage_stats . Las columnas last_user_xxx contendrán la última vez que se accedió a la tabla desde las solicitudes de los usuarios. Esta tabla restablece su seguimiento después de reiniciar el servidor, por lo que debe dejarlo en ejecución por un tiempo antes de confiar en sus datos.

Answer 3

Para SQL Server 2008, debería echar un vistazo a la auditoría SQL . Esto le permite auditar muchas cosas, incluyendo selecciones en una tabla e informes a un archivo o registro de eventos.

Answer 4

Re: Profiler, si monitoreas para SP:StmtCompleted , que capturará todas las instrucciones que se ejecutan dentro de un procedimiento almacenado, de modo que atrapará los accesos a la tabla dentro de un sproc. Si no todo pasa por procedimientos almacenados, también puede necesitar el evento SQL:StmtCompleted .

Habrá una gran cantidad de eventos, por lo que probablemente aún no sea práctico rastrear durante un período prolongado debido al tamaño de la traza. Sin embargo, puede aplicar un filtro, por ejemplo, donde TextData contiene el nombre de la tabla que desea verificar. Puede dar una lista de nombres de tabla para filtrar en cualquier momento y trabajar en ellos gradualmente. Por lo tanto, no debería obtener ningún evento de rastreo si no se ha accedido a ninguna de esas tablas.

Incluso si sientes que no es un enfoque adecuado / viable para ti, pensé que valía la pena expandirlo.

Otra solución sería hacer una búsqueda global de su código fuente para encontrar referencias a las tablas. Puede consultar las definiciones de procedimientos almacenados para buscar coincidencias para una tabla determinada, o simplemente generar un script de base de datos completo y hacer una búsqueda sobre eso para nombres de tabla.

Answer 5

Tenía en mente jugar con los permisos de usuario para diferentes tablas, pero luego recordé que puede activar el rastreo con un activador ON LOGON que podría beneficiarse de esto:

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;

/

Luego puede verificar sus archivos de rastreo.

Answer 6

Una nota al margen: si su intención es abandonar esas tablas, es posible que deba considerar las obligaciones legales que se le imponen para mantener los datos involucrados de todos modos por x años.