SQL Server – How to Log All Accessed Objects During a Certain Time Period

auditsql server

We are running quite a few tests for our software that require a certain set of databases. These databases are usually huge, while most of the data in there will not be used by any testcase.

In order to reduce stress on our database server due to many CI tests, we would like to remove the unused data and it would be great if that would be possible in an automated way.

Here I'm thinking of something like "Run the tests and log which objects (e.g. Table and PK) are accessed (SELECT, UPDATE, DELETE)". Then afterwards we could remove all which haven't been accessed. Is there a way to collect this information in SQL Server 2016?

Thanks, Alex

Best Answer

Updating answer

There are two parts to your question:

  1. In order to reduce stress on our database server due to many CI tests, we would like to remove the unused data and it would be great if that would be possible in an automated way.

This is to do with data inside tables, meaning we have to get to the row level in order to capture what has been updated/deleted(DML). This can be achieved by enabling tracking. Copied from Microsoft site:

SQL Server 2017 provides two features that track changes to data in a database: change data capture and change tracking. These features enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database. Change data capture and change tracking can be enabled on the same database; no special considerations are required. For the editions of SQL Server that support change data capture and change tracking, see Features Supported by the Editions of SQL Server 2016. Change tracking is supported by SQL Database. Change data capture is only supported in SQL Server and Azure SQL Database Managed Instance.

You may refer this link from Microsoft to understand details. Alternatively you can enable auditing on tables for any DML operations and you may also club these two as mentioned here.

For more details on Change tracking feature in SQL server database, you may refer another link from Mr. Arshad Ali.

Above will help you for tracking DML operation only however for tracking select, its really difficult and can be traced only by enabling auditing as mentioned here.

  1. Here I'm thinking of something like "Run the tests and log which objects (e.g. Table and PK) are accessed (SELECT, UPDATE, DELETE)". Then afterwards we could remove all which haven't been accessed. Is there a way to collect this information in SQL Server 2016?

As data level is tracked, we can go a level higher i.e. object level. In order to handle the usage/non-usage of any object, you may refer below.

There is a wonderful article from Mr. Ahmad Yaseen on finding unused objects in any given databases. You may Below is the query which can be used:

-- Create CTE for the unused tables, which are the tables from the sys.all_objects and 
-- not in the sys.dm_db_index_usage_stats table

; with UnUsedTables (TableName , TotalRowCount, CreatedDate , LastModifiedDate ) 
AS ( 
  SELECT DBTable.name AS TableName
     ,PS.row_count AS TotalRowCount
     ,DBTable.create_date AS CreatedDate
     ,DBTable.modify_date AS LastModifiedDate
  FROM sys.all_objects  DBTable 
     JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name
  WHERE DBTable.type ='U' 
     AND NOT EXISTS (SELECT OBJECT_ID  
                     FROM sys.dm_db_index_usage_stats
                     WHERE OBJECT_ID = DBTable.object_id )
)
-- Select data from the CTE
SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate 
FROM UnUsedTables
ORDER BY TotalRowCount ASC

Kindly note that, this will work from the last restart time of SQL service/server as mentioned:

We will use the sys.dm_db_index_usage_stats system DMV that stores the number of scans, seeks and updates performed on the database tables since the last time the SQL Service was restarted.

You may read more details here.

There are other articles also, which could be referred for this purpose. For instance, you may use sp_blitzindex for finding similar info from Mr. Brent Ozar.

For checking access date/time for views/function/SPs, you may refer below question:

Query last time a view, funcion or sp was last used/read

There is another article from Mr. Bart Gawrych on the same topic:

  select [schema_name],
           table_name,
           max(last_access) as last_access
    from(
        select schema_name(schema_id) as schema_name,
               name as table_name,
               (select max(last_access) 
                from (values(last_user_seek),
                            (last_user_scan),
                            (last_user_lookup), 
                            (last_user_update)) as tmp(last_access))
                    as last_access
    from sys.dm_db_index_usage_stats sta
    join sys.objects obj
         on obj.object_id = sta.object_id
         and obj.type = 'U'
         and sta.database_id = DB_ID()
    ) usage
    where last_access < dateadd(month, -3, current_timestamp)
    group by schema_name,
             table_name
    order by last_access desc;

This again depends on system DMV, so be wary of last reboot of service/server.

Note: Your question is on how to find list of objects which are getting used however there are tonnes of article on finding unused objects, you may tweak them a bit to get list of used objects or else if you do manage to find unused objects, that should also serve your purpose.

Please let us know if this helped you in achieving your objective.

Related Question