question

Ian Roke avatar image
Ian Roke asked

How do I find out the last time a view was used?

I would like to be able to produce a list of views in a database and the last time that the view was used so I can sort it by date and create a list of views that haven't been used in six months.

Is this possible? I was able to do this using sys.dm_db_index_usage_stats for tables but as yet not found anything similar for views.

sql-server-2005view
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

You can't do this 100% unless you're running a trace on your system 24/7 and keeping the data or using the auditing mechanisms of 2008 (+1 to Hakan). But, if you don't have these things implemented, you can still check to see if an object has been accessed recently. First, determine where the view is used within your procedures, views, and functions. Then, query the cache using sys.dm_exec_query_stats combined with sys.dm_exec_sql_text. You can look for the objects that use your view and you can look for ad hoc SQL that uses your view.

Just remember, this only shows the information that is currently in cache. If a query aged out of cache a couple of milliseconds before you check the data, it won't be there.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

Ian, I'm pretty certain SQL Server doesnt log the execution of a select statement - either to a view or to a table directly.

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It isn't so much the execution I am interested in more that the view was "touched" at all. If there has been no activity on a view then it would be safe to assume it isn't regularly used. I am just doing a bit of cleansing on the server. Each additional view/table/sproc takes a certain allocation of time to maintain and document.
0 Likes 0 ·
Thinking on from @DavidWinbush's answer - you could use Red-Gate's SQLSearch to find references to the objects you want. http://www.red-gate.com/products/SQL_Search/index.htm
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered

If the queries are all stored procs and functions you could search the [text] column in syscomments. It won't tell you when the view was last used but if the view doesn't show up in the results the answer is "not for a while". I wrapped this up in a stored proc and blogged about it here: http://sqlblogcasts.com/blogs/davidwimbush/archive/2009/07/13/better-dependency-checking.aspx

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered

I agree with Fatherjack, SQL server 2005 doesn't log that kind of information, but if you upgrade to SQL server 2008 you can acticate "Audit" to log when an object was last accessed. I implemented Audit in SQL server 2008 and collected the information (stored in files that are recycled) to a table. If an object was not "accessed" in 6 month it was considered "obsolete" and renamed in the next release (after a lot of testing of course).

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.