How to Check SQL Server View Utilization

If you are working on SQL Server optimization delicately, you may get often queries that how to find if any index, table or view is being used in database or not.

In recent past, we went live for one of the critical application. The developers decided to check through all the indexes utilization including View indexes so that they can align or remove unwanted indexes from the tables and views. At a point, they also wanted to know a rough utilization of Views. So, they asked how to check a rough utilization of each view.

The question motivates to blog this tip. A special thank goes to the developer. 

How to check SQL Server View Utilization

Let’s follow a step-by-step approach to figure out the utilization of SQL Server View. To demonstrate it, I am going to use WideWorldImporters database.

  • Let’s execute the below code under the WideWorldImporters database. It is going to do following things;
    • Step1: Create a sp called “Sel_From_View”. The SP is going to do a SELECT using the View “[Website].[Customers]”.
    • Step2:  Execute the SP 50 times.
    • Step3: Use the View “[Website].[Customers]” in the ad-hoc query and execute it 5 times.

  • Once the above scripts got executed successfully, check the plan cache to figure out the utilization of the View by running the below query.

Here is the output of the above query;

The result shows that the View “[Website].[Customers]” is being used by the stored procedure and the ad-hoc query. So, you use the plan analysis script to figure out the utilization of required view.

Note: This process will only show the current utilization of the view form the plan cache not past utilization.

I hope you find this blog useful when you are asked the same question.

Leave a comment

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