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.

USE [WideWorldImporters]
GO

---Step1----Create a store procedure 

CREATE PROCEDURE Sel_From_View
AS
SELECT top 100 WC.[CustomerID]
      ,WC.[CustomerName]
      ,WC.[CustomerCategoryName]
      ,WC.[WebsiteURL]
FROM [WideWorldImporters].[Website].[Customers] WC
GO

---Step2----Execute the Created Stored Procedure 50 times now

EXEC Sel_From_View
GO 50

---Step3----Lets use the View in the Adhoc Query and it will be executed 5 times.

SELECT WC.[CustomerID]
      ,WC.[CustomerName]
      ,WC.[CustomerCategoryName]
      ,WC.[WebsiteURL]
	  ,C.AccountOpenedDate 
FROM [WideWorldImporters].[Website].[Customers] WC
inner join Sales.Customers C on C.CustomerID = WC.CustomerID
GO 5

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

SELECT  
		a.execution_count ,
		OBJECT_NAME(objectid, b.dbid) as object_name,
		query_text = SUBSTRING( 
								b.text, a.statement_start_offset/2, 
										(   CASE WHEN a.statement_end_offset = -1 
											THEN LEN(CONVERT(nvarchar(MAX), b.text)) * 2 
											ELSE a.statement_end_offset 
											END - a.statement_start_offset
										 )/2
								) ,
		dbname = DB_NAME(b.dbid)
--		a.creation_time,
--		a.last_execution_time
FROM            sys.dm_exec_query_stats a 
CROSS APPLY     sys.dm_exec_sql_text(a.sql_handle) AS b 
WHERE SUBSTRING( b.text,  a.statement_start_offset/2,   
					( CASE  WHEN a.statement_end_offset = -1  THEN LEN(CONVERT(NVARCHAR(MAX), b.text)) * 2 
							ELSE a.statement_end_offset END - a.statement_start_offset
					 )/2
				)  LIKE '%Customers%' ---Pass the view name

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.