How can I identify the number of times a View is called in SQL Server?

Posted on

Question :

How can I identify the number of times a View was called in SQL Server?

I am looking for an answer that is similar to this below which shows how it can be done for stored procedures.

How can I monitor the call count per stored procedure per timespan?

Answer :

Here is one not-overly-scientific way to do it, it is quite possible that it will miss references due to, say, indexed view expansion, and get you false positives if the batch text contains your view name in comments or as part of another entity name or string. Also won’t work on earlier versions of SQL Server (which is why it is important to tag your question with the minimum version of SQL Server you need to support).

SELECT t.text,s.execution_count,s.last_execution_time
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
WHERE t.text LIKE '%your-view-name%';

Also note this only contains metrics going back to the last service restart, RECONFIGURE, etc.

Leave a Reply

Your email address will not be published. Required fields are marked *