2

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.

5

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.

2

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

enter image description here

For database audit details also, msdn db audit

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.