Get list of views and its time for execution

Question

How can we get list of all views and its time for execution in a database in SQL Server

Does this answer your question? [How to see query history in SQL Server Management Studio](https://stackoverflow.com/questions/5299669/how-to-see-query-history-in-sql-server-management-studio) — Amira Bedhiafi, Nov 18 '19 at 08:25
A view does not have a "time for execution". A query that uses a view has execution information. And many different queries might use the same view in different ways. This sounds like a big [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) — SMor, Nov 18 '19 at 12:45

score 0 · Answer 1 · edited Nov 18 '19 at 07:33

0

For example:

Use database
select * from sys.views

edited Nov 18 '19 at 07:33

Dale K

25,246
15
42
71

answered Nov 18 '19 at 07:00

Leon Yue

15,693
1
11
23

i need time taking for an each view to get executed – steve Nov 18 '19 at 07:06
According my experience, I'm afraid we can not get the time of view execution. – Leon Yue Nov 18 '19 at 07:35

Amira Bedhiafi · Answer 2 · 2019-11-18T08:50:54.880

0

You can check the last execution time of a specific view :

SELECT t.[text], s.last_execution_time
FROM sys.dm_exec_cached_plans AS p
INNER JOIN sys.dm_exec_query_stats AS s
   ON p.plan_handle = s.plan_handle
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.[text] LIKE N'select * from yourview'
ORDER BY s.last_execution_time DESC;

Update :

SET STATISTICS TIME  ON
SET STATISTICS TIME ON;    
SELECT * FROM .... //your query
SET STATISTICS TIME OFF;

Go to the 'Message' tab to see a message like this:

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 165 ms.

edited Nov 18 '19 at 08:50

answered Nov 18 '19 at 08:25

Amira Bedhiafi

8,088
6
24
60

I need a query which gives output some thing like this view1 taking 11 seconds.. view2 taking 5 seconds to execute.. – steve Nov 18 '19 at 08:40

score 0 · Answer 3 · answered Nov 18 '19 at 10:51

The script below will execute each of the views that it finds in sys.views and then based on the start time and end time give an output right at the bottom showing how long it took to execute each one

DECLARE @views TABLE (
    RowCounter int IDENTITY(1,1),
    ViewName nvarchar(100),
    StartDate datetime,
    EndDate datetime,
    Milliseconds float
)
--get the views
INSERT @views (ViewName)
SELECT name
FROM sys.views

DECLARE @counter int, @startDate datetime, @endDate datetime, @nextView nvarchar(100), @nextSql nvarchar(255)
SET @counter = 1

WHILE @counter <= (SELECT COUNT(*) FROM @views)
BEGIN
    --record start date
    SELECT TOP 1 @startDate = GETDATE(), @nextView = ViewName, @nextSql = 'SELECT * FROM ' + ViewName
    FROM @views
    WHERE RowCounter = @counter
    --execute the view
    exec sp_executesql @nextSql
    --record the end date
    SELECT @endDate = GETDATE() 

    --update the results
    UPDATE @views 
    SET StartDate = @startDate, EndDate  = @endDate,
        Milliseconds = DATEDIFF(MILLISECOND, @startDate, @endDate)
    WHERE RowCounter = @counter

    SET @counter = @counter + 1
END

SELECT * FROM @views

Get list of views and its time for execution

3 Answers3