In SQL Server 2008, is there some simple logging of how often a particular table is selected (in an hour, a day, a week, etc.)? I have a large table that is accessed by dozens of applications and scores of web pages. The data provider is threatening to make major changes to what is available and I need to start working on a demonstration of how much the data is accessed.
Assuming that there is an index on the table in question, you could try querying sys.dm_db_index_usage_stats. It's a view so you will need to specify the database and object_ID of the table you are interested in. You can query at intervals and store the results to get delta over time. Look at the MSDN page https://msdn.microsoft.com/en-us/library/ms188755(v=sql.100).aspx for how to interpret the information.
SQL Server Audits is a very lightweight solution that with get all the SELECTS on a table. Things to know beforehand: you need a server audit created and enabled, you need a database audit created and enabled, you will not be able to query the audit file until its all shut down, upon shut down the memory is flushed to disk(audit file), you need to specify who your auditing and what table explicitly
USE [master] GO CREATE SERVER AUDIT [test_server_audit] TO FILE ( FILEPATH = N'<your location for results>' ,MAXSIZE = 2 MB ,MAX_FILES = 7 ,RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 10000 ,ON_FAILURE = CONTINUE ) GO -- Enable the server audit. ALTER SERVER AUDIT test_server_audit WITH (STATE = ON) ; GO -- Create the datbase audit. USE <your db> GO CREATE DATABASE AUDIT SPECIFICATION [test_db_audit] FOR SERVER AUDIT [test_server_audit] ADD (SELECT ON OBJECT::[dbo].[<table name here>] BY [dbo]) WITH (STATE = ON) -- enable the audit GO -- Disable the datbase audit. USE <your db> GO ALTER DATABASE AUDIT SPECIFICATION test_db_audit WITH (STATE = OFF) GO -- disable the server audit. USE master GO ALTER SERVER AUDIT test_server_audit WITH (STATE = OFF); GO -- must disable the db audit first SELECT COUNT(*) , statement FROM sys.fn_get_audit_file('<your location for results>\test_server_audit_<whatever name it has in this location>.sqlaudit', DEFAULT, DEFAULT) GROUP BY statement; GO
This needs a few adjustments.
- your database name needs inserted where appropriate
- your table name needs inserted in the one location
- this is auditing all dbo users, that can be adjusted as needed
- this audits one table
Also this can all be done through the GUI, I would advise you follow the msdn server audit for server audit details but your looking into
For database audit details also, msdn db audit