Track usage of columns or tables in Microsoft SQL server

Question

We have a collection of very old, horribly designed databases and I have been given the green light to tear them out and restructure them. However, these databases have no normalization, numerous fields have been empty for years, or re-purposed without documentation, and we have a slew of legacy applications and public websites that use various pieces of this data, but no one in house has any idea what does what to what.

How could I determine which fields in these tables are being used? Is there a way in SQL server, or using a third party tool if need be, to see a history of usage? or to set up some additional logging to determine this usage? "Usage" ideally meaning when it was last updated or inserted, and when or how often it is included in a select statement.

In addition to this, we are migrating from SQL 2005 to 2008. So a solution using either server type would work as I should be able to bring the 05 dbs up to 08 compliance.

score 2 · Accepted Answer · answered Jul 02 '09 at 03:59

Personally I would use the DMVs as my initial investigation to look for major activity. This can help target your investigation so you can create the right profiler traces. (As Sam mentions, you want to be careful with these so you don't create a performance problem.)

Since you mention you have the option of using sql 2008, the new auditing features may be useful for you.

Here is what I recommend looking at:

Look for object access with index DMVs

Two DMVs will be the most useful: sys.dm_db_index_usage_stats (BOL here) and sys.dm_db_missing_index_details (BOL here).

Look for the most frequent and highest impact queries in the cache

Use sys.dm_exec_query_stats queries to find the most frequently run queries. BOL here.

Good luck!

score 0 · Answer 2 · answered Jul 01 '09 at 16:14

Select statements can be audited by sql server traces from now forward. They are not stored in a log. Your logs hold data modification which can be inspected with some 3rd party tools from popular SQL utility vendors. You could also use triggers on all your tables to audit modifications - but they won't catch selects.

Running 'wide open' traces for all select statements on production are not recommended. Move it to development if possible and set up some testing.

c2 auditing might also help you out, but I don't know many details on what it actually collects.

Also, walking through the application code would be a good place to investigate as well.

score 0 · Answer 3 · answered Jul 01 '09 at 16:15

You can run a trace with SQL profiler, let it go for a while, then do a Find in the output to look for the field names you are concerned about.

The other option is you could create a trigger in SQL server and log that information to another table.

i.e.,

    CREATE TRIGGER Audit 
    ON dbo.Employee 
    FOR INSERT, UPDATE, DELETE 
AS 
    IF (SELECT COUNT(*) FROM inserted) > 0 
    BEGIN 
        IF (SELECT COUNT(*) FROM deleted) > 0 
        BEGIN 
            -- update! 
            INSERT AuditEmployee 
            (EmployeeID, UserName, Operation) 
            SELECT EmployeeID, SUSER_SNAME(), 'U' 
            FROM inserted 
        END 
        ELSE 
        BEGIN 
            -- insert! 
            INSERT AuditEmployee 
            (EmployeeID, UserName, Operation) 
            SELECT EmployeeID, SUSER_SNAME(), 'I' 
            FROM inserted 
        END 
    END 
    ELSE 
    BEGIN 
        -- delete! 
        INSERT AuditEmployee 
        (EmployeeID, UserName, Operation) 
        SELECT EmployeeID, SUSER_SNAME(), 'D' 
        FROM deleted 
    END 
GO

That example was pulled from http://sqlserver2000.databases.aspfaq.com/how-do-i-audit-changes-to-sql-server-data.html. To store the column name in you can add that variable to your insert statement. See http://www.devx.com/dbzone/Article/7939/1954 for another example.

Track usage of columns or tables in Microsoft SQL server

3 Answers3