Como Monitorar o Uso de Views no SQL Server com SQL Server Agent Job

O SQL Server fornece vários métodos para monitorar e analisar o uso de views em seu banco de dados. Neste tutorial, vamos mostrar como criar um SQL Server Agent Job que monitora e registra o uso de views em um banco de dados SQL Server.

Passo 1: Crie uma tabela para armazenar as estatísticas de uso

Primeiro, precisamos criar uma tabela para armazenar nossos dados de monitoramento.

Execute o seguinte comando no SQL Server:

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

Passo 2: Crie um procedimento armazenado para coletar as estatísticas

Em seguida, vamos criar um procedimento armazenado que coleta as estatísticas de uso e as insere na tabela ViewUsageStats:

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

Passo 3: Crie um SQL Server Agent Job para executar o procedimento regularmente

Agora vamos criar um SQL Server Agent Job que executará este procedimento a cada hora:

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:59aa
    @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)'

Lembre-se de substituir 'YourDatabaseName' pelo nome do seu banco de dados onde o procedimento UpdateViewUsageStats foi criado.

Conclusão

Agora você tem um SQL Server Agent Job configurado para rastrear e registrar o uso de views a cada hora. Este é um exemplo simples, mas útil, de como você pode usar os recursos internos do SQL Server para monitorar o desempenho do seu banco de dados e identificar possíveis problemas.