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?


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.

  • Wow, have you seen a server with 100GB of query plans? Were they genuine or unparameterized duplication? – usr May 8 '17 at 21:14
  • Yeah, 1+TB RAM boxes with high uptime and diverse workloads. – Brent Ozar May 8 '17 at 23:52

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.