7

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.

8

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
  • Thanks, it looks like the UseCount will be very helpful. Still hoping for something similar on SQL Server 2000. – PatFromCanada Apr 9 '13 at 20:43
  • I added SQL Server 2000 advice to my answer... – Max Vernon Apr 9 '13 at 21:06
  • Perhaps its not Ideal but I am going to build a simple app to poll using this query, and store the results in a table. I notice that some objects will disappear from the cache during the day and that it is pretty much empty in the morning. All I need is one hit to know the view is active. – PatFromCanada Apr 10 '13 at 18:09
  • Thanks for this info! However, FYI, I got this error when I tried it: "Msg 6335, Level 16, State 101, Line 2 XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels. " Any thoughts on how to deal with that? – jrdevdba Apr 25 '18 at 15:23
  • 1
    @jrdevdba - sp_BlitzErik mentioned in chat this morning that "query plans with > 128 nodes are stored in sys.dm_exec_text_query_plan rather than sys.dm_exec_query_plan"... that may help you. – Max Vernon Apr 25 '18 at 17:25
15

Unfortunately if you want to be 100% accurate, the plan cache isn't going to cut it, because there are all types of scenarios in SQL Server where a plan may not get cached at all. For example, OPTION(RECOMPILE), zero cost plans, optimize for ad hoc workloads and single use plan stubs, etc.

Since you want a method that works across 2000-2012, your only real option to guarantee you catch the usage is to use a server-side trace with the Audit Database Object Access event:

http://msdn.microsoft.com/en-us/library/ms175013.aspx

You will want to filter on the ObjectType = 8278 so that you only catch Views being accessed:

http://msdn.microsoft.com/en-us/library/ms180953.aspx

Then setup a job that pulls the file data in and aggregates the counts up every couple of hours (or even days depending on the data generation rate), and you'll be able to accurately trace out the accesses that are occurring.

Yeah I hate Trace as much as the next guy, but this is one of those scenarios where it is the right tool for the task at hand.

FWIW, on 2012 you could use Server Audit or Extended Events for this to track the object access as well, but once you create the Trace definition for 2000, it should be portable for the most part to 2012, and you can use Profiler to generate the scripts to make it much easier to work with.

  • This looks super promising but i have not been able to get SQL Server 2000 to log anything for views. If I run a trace for all object types with the event of Objects-Object:Opened, I get no hits. (the help for this event says "Indicates when an object has been accessed, such as for a SELECT, INSERT, or DELETE statement.") There is no object Access Event that I can see. – PatFromCanada Apr 10 '13 at 15:26

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.