Updating answer
There are two parts to your question:
- 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.
- 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.
