SQL Server – How to Detect Unused Views and Get Usage Stats

sql serversql-server-2000sql-server-2012

Is there a way to determine if a view is no longer being used (without removing them)?

Ideally I would like to know the views' usage for SQL Server 2000 and 2012.

I am upgrading some databases and suspect that many of the views are no longer being used. Also, some of the views will be difficult to compile on the new server as they access multiple databases, some of which are not being moved to the new server.

Best Answer

Even though this answer has been accepted, please see Jonathan Kehayias' answer below for a much better way to do this.

For SQL Server 2012, you could inspect the plan cache for the name of the view. 

DECLARE     @FindSql nvarchar(max) = 'name_of_view';
SELECT 
    /* cp.*, ct.* */
    cp.objtype AS [Type],
    cp.refcounts AS ReferenceCount,
    cp.usecounts AS UseCount,
    cp.size_in_bytes / 1024 AS SizeInKB,
    db_name(ct.dbid) AS [Database],
    CAST(pt.query_plan as xml) as QueryPlan
FROM sys.dm_exec_cached_plans cp
OUTER APPLY sys.dm_exec_text_query_plan(plan_handle, 0, -1) pt
OUTER APPLY sys.dm_exec_sql_text(plan_handle) AS ct
WHERE (ct.text LIKE '%' + @FindSql + '%') OR (pt.query_plan LIKE '%' + @FindSql + '%')
ORDER BY cp.usecounts DESC;

You may want to use DBCC FREEPROCCACHE <sql_plan_handle> with the plan handle of any plans that use the view, then watch the results of the above query to see if it pops up again.

MSSQLTips has a great article on doing this in SQL Server 2000 +

USE Master
GO
SELECT 
    UseCounts, RefCounts,CacheObjtype, ObjType, DB_NAME(dbid) as DatabaseName, SQL
FROM syscacheobjects
WHERE SQL LIKE '%view_name_here%'
ORDER BY dbid,usecounts DESC,objtype
Related Question