How to Check SQL Server View Utilization

, 2018-04-09

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.

The post How to Check SQL Server View Utilization appeared first on .

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads