Is there DMV for seeing view access count?

by Vladimir Oselsky   Last Updated May 08, 2017 14:06 PM

I saw it once during a presentation when someone showed a query to get a count of how many times views have queried in a SQL server. I don't remember if it was from DMV or a combination of some other statistics, but I clearly remember when they ran a query that selects from a view and after it would show that count going up by one. Another interesting fact that I recall about the presentation was selecting from a CTE increased the count by two because SQL Server has to create a "temporary view" and later select from it.

Does anyone know how this can be demonstrated?

Answers 1

No, but what the presenter was probably doing was using Grant Fritchey's technique to search the plan cache for a string. You can search the plan cache for your view name.

That technique has a few drawbacks. It's very slow on a busy/large production server - it doesn't hold folks back by blocking, but it can just take a really long time to search, say, 10-100GB of query plans looking for a string. Also, it only searches the literal plan - if you have a view calling another view, the nested view's name may not show up in the query or the plan.

Brent Ozar
Brent Ozar
May 08, 2017 13:47 PM

Related Questions

Replace a materialized view in Postgres

Updated December 15, 2017 11:06 AM

SQL Server table and view show different values

Updated April 15, 2015 23:02 PM

Create view with group by and extra fields

Updated May 06, 2016 08:02 AM