How to Monitor SQL Server View Usage with SQL Server Agent Job

SQL Server provides several methods to monitor and analyze the usage of views in your database. In this tutorial, we will show how to create a SQL Server Agent Job that monitors and logs the usage of views in a SQL Server database.

Step 1: Create a table to store the usage statistics

First, we need to create a table to store our monitoring data. Run the following command in SQL Server:

CREATE TABLE ViewUsageStats
(
    ViewName nvarchar(128),
    ExecutionCount bigint,
    LastExecutionTime datetime,
    RecordTime datetime default GETDATE()
)

Step 2: Create a stored procedure to collect the statistics

Next, let's create a stored procedure that collects the usage statistics and inserts them into the ViewUsageStats table:

CREATE PROCEDURE UpdateViewUsageStats AS
BEGIN
    INSERT INTO ViewUsageStats (ViewName, ExecutionCount, LastExecutionTime)
    SELECT 
        OBJECT_NAME(q.objectid),
        qs.execution_count,
        qs.last_execution_time
    FROM 
        sys.dm_exec_query_stats qs
    CROSS APPLY 
        sys.dm_exec_sql_text(qs.sql_handle) q
    WHERE 
        q.objectid IS NOT NULL 
        AND OBJECT_NAME(q.objectid) IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS)
END

Step 3: Create a SQL Server Agent Job to run the procedure regularly

Now let's create a SQL Server Agent Job that will run this procedure every hour:

USE msdb
GO

DECLARE @jobId BINARY(16)
EXEC sp_add_job
    @job_name = N'UpdateViewUsageStatsJob',
    @enabled = 1,
    @notify_level_eventlog = 0,
    @notify_level_email = 0,
    @notify_level_netsend = 0,
    @notify_level_page = 0,
    @delete_level = 0,
    @description = N'Job to update view usage statistics every hour.',
    @category_name = N'[Uncategorized (Local)]',
    @owner_login_name = N'sa', 
    @job_id = @jobId OUTPUT

-- Add a job step named 'UpdateViewUsageStatsStep'
EXEC sp_add_jobstep
    @job_id = @jobId,
    @step_name = N'UpdateViewUsageStatsStep',
    @step_id = 1,
    @cmdexec_success_code = 0,
    @on_success_action = 1,
    @on_success_step_id = 0,
    @on_fail_action = 2,
    @on_fail_step_id = 0,
    @retry_attempts = 0,
    @retry_interval = 0,
    @os_run_priority = 0, 
    @subsystem = N'TSQL',
    @command = N'EXEC dbo.UpdateViewUsageStats',
    @database_name = N'YourDatabaseName',
    @flags = 0

-- Create a schedule
DECLARE @scheduleId INT
EXEC sp_add_jobschedule
    @job_id = @jobId,
    @name = N'UpdateViewUsageStatsSchedule',
    @enabled = 1,
    @freq_type = 4, -- 4 = Daily
    @freq_interval = 1, -- Every day
    @freq_subday_type = 4, -- 4 = Hour
    @freq_subday_interval = 1, -- Every hour
    @freq_relative_interval = 0,
    @freq_recurrence_factor = 0,
    @active_start_date = 20230726, -- Today's date in YYYYMMDD format
    @active_end_date = 99991231, -- Run indefinitely
    @active_start_time = 0, -- Start at 00:00:00
    @active_end_time = 235959, -- End at 23:59:59
    @schedule_id = @scheduleId OUTPUT

-- Attach the job to the schedule
EXEC sp_attach_schedule
    @job_id = @jobId,
    @schedule_id = @scheduleId

-- Add the job to the SQL Server Server
EXEC sp_add_jobserver
    @job_id = @jobId,
    @server_name = N'(local)'

Remember to replace 'YourDatabaseName' with the name of your database where the UpdateViewUsageStats procedure was created.

Conclusion

Now you have a SQL Server Agent Job set up to track and log view usage every hour. This is a simple but useful example of how you can use SQL Server's built-in features to monitor your database's performance and identify potential issues.