SQL Server Profiler Automatization

freepik

Hey guys! We all know how boring repetitive tasks can be, especially those that you feel could be automated, right?

Time is money these days (but remember to rest), so I’m here to share an automation for those of you who spend a few minutes a day watching SQL Server Profiler just to make sure everything is running smoothly. Instead of doing this work manually, how about getting a warning if everything is working fine? Or, if everything falls apart… :(

Below is the stages and topics:

1- Scenario;

2- The Queries;

3- Procedure Creation and Execution in SQL Server Agent;

4- Check and Delete Profiler

Scenario

In our scenario we are going to work with command tracking, focused on the events: RPC:Completed, SQL:BatchCompleted and SQL:BatchStarting.

The idea is that: SQL Agent sends me an email whenever any query exceeds 30 seconds, a system requirement that end users use, focused on database performance.

The Queries

Below, I will add all the queries used in this process, it has some comments that may help in understanding. So you can change it however you want, targeting your scenario.

-- Create Procedure
-- WHAT IT MEANS? This is the procedure used for make the automatization
USE [your_database]
GO

CREATE PROCEDURE [dbo].[AutomaticallyProfiler]

     @RunTime int = 1 -- Duration of the trace in minutes

AS

-- Create a Queue
DECLARE @rc int
DECLARE @TraceID int
DECLARE @maxfilesize bigint

-- Generate File Names based on prefix and time stamp
-- Specify Max File Size and Location
DECLARE @Now datetime
DECLARE @StopTime DateTime
DECLARE @FQFileName Nvarchar(100)
DECLARE @FileStamp Nvarchar(25)

SET @Now = GETDATE()
SET @StopTime = DATEADD(MI, @RunTime, @Now)
SET @FQFileName = 'C:\Profiler\TraceProfiler_' -- Your path to store the files
SET @FileStamp =
    CAST(DATEPART(YEAR, GETDATE()) AS NVARCHAR) +
    RIGHT('0' + CAST(DATEPART(MONTH, GETDATE()) AS NVARCHAR), 2) +
    RIGHT('0' + CAST(DATEPART(DAY, GETDATE()) AS NVARCHAR), 2)

SET @FQFileName = @FQFileName + @FileStamp
SET @maxfilesize = 500

EXEC @rc = sp_trace_create @TraceID OUTPUT, 0, @FQFileName, @maxfilesize, @StopTime
IF (@rc != 0) GOTO error

-- Set the events
DECLARE @on BIT
SET @on = 1

-- Configure tracking events (Here you can change depending on what you want to view)
EXEC sp_trace_setevent @TraceID, 10, 1, @on
EXEC sp_trace_setevent @TraceID, 10, 12, @on
EXEC sp_trace_setevent @TraceID, 10, 13, @on
EXEC sp_trace_setevent @TraceID, 12, 1, @on
EXEC sp_trace_setevent @TraceID, 12, 12, @on
EXEC sp_trace_setevent @TraceID, 12, 13, @on

-- Set the Filters
DECLARE @intfilter int
DECLARE @bigintfilter bigint

SET @intfilter = 50
EXEC sp_trace_setfilter @TraceID, 12, 0, 4, @intfilter
EXEC sp_trace_setfilter @TraceID, 35, 0, 7, N'Master'

-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1
-- display trace id for future references
      SELECT TraceID=@TraceID
      goto finish
      error:

      SELECT ErrorCode=@rc
      finish:
Go


-----------------------------------------------------------------------------------

-- Exec Procedure
-- WHAT IT MEANS? This is just a script used to execute the procedure
USE [your_database]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[AutomaticallyProfiler]

SELECT 'Return Value' = @return_value

GO


-----------------------------------------------------------------------------------

-- Check Profiler
-- WHAT IT MEANS? This is a query used to check if any query takes more than 30 seconds
DECLARE @FQFileName NVARCHAR(100)
DECLARE @FileStamp NVARCHAR(25)

SET @FQFileName = 'C:\Profiler\TraceProfiler_'

SET @FileStamp =
    CAST(DATEPART(YEAR, GETDATE()) AS NVARCHAR) +
    RIGHT('0' + CAST(DATEPART(MONTH, GETDATE()) AS NVARCHAR), 2) +
    RIGHT('0' + CAST(DATEPART(DAY, GETDATE()) AS NVARCHAR), 2) 

SET @FQFileName = @FQFileName + @FileStamp + '.trc'

DECLARE @DurationCount INT

SELECT @DurationCount = ISNULL(COUNT(Duration), 0)
FROM fn_trace_gettable(@FQFileName, 1)
WHERE [Duration] > 30000000

IF @DurationCount > 0 OR @DurationCount <> 0
BEGIN
    -- Throw a custom error
    -- THROW 50001, 'The WHERE value is greater or different from 0.', 1

    -- Throw an error using RAISERROR
    RAISERROR('Existem consultas que levaram mais de 30 segundos', 16, 1)
END


-----------------------------------------------------------------------------------

-- Deletes the file
-- WHAT IT MEANS? This is a query used to delete the Profiler file that was created, after it has been verified
DECLARE @FilePath NVARCHAR(100)
DECLARE @FileName NVARCHAR(100)

SET @FilePath = 'C:\Profiler\'
SET @FileName = 'TraceProfiler_'

SET @FileName =
    @FileName +
    CAST(DATEPART(YEAR, GETDATE()) AS NVARCHAR) +
    RIGHT('0' + CAST(DATEPART(MONTH, GETDATE()) AS NVARCHAR), 2) +
    RIGHT('0' + CAST(DATEPART(DAY, GETDATE()) AS NVARCHAR), 2) +
    '.trc'

DECLARE @Cmd NVARCHAR(200)
SET @Cmd = 'DEL "' + @FilePath + @FileName + '"'

EXEC xp_cmdshell @Cmd

Ok, don´t forgot to create the path on C:\

C:\Profiler

Procedure Creation and Execution in SQL Server Agent

First, we create the Procedure, just change the Database and execute the Procedure code.

Example:

USE [your_database]
GO

CREATE PROCEDURE [dbo].[AutomaticallyProfiler]

-- ...
-- All Procedure Code
-- ...

      finish:
Go

After that, we navigate to the SQL Server Agent, if necessary, configure.

1. With the Agent started, expand by clicking on the + button >> right button on “Jobs” >> “New Job”
2. Add a name in the “General” tab
3. In the “Steps” tab, go to “New” >> Add a name to the “Step Name” >> In the “Type” leave it with the Transact-SQL Script (T-SQL) >> Change it to the database used >> copy and paste the command that executes the Procedure.

Example of “General” tab:

Source: Author

Example of Advanced” tab:

Source: Author

4. Navigate to the “Schedule” tab and configure it according to your scenario. But pay attention to the next steps, they are connected.

Check and Delete Profiler

1. Remake the previous steps 01 and 02 to create a New Job in SQL Agent, this one I call Check Trace

Example of “General” tab:

Source: Author

Example of “Advanced” tab:

Source: Author

Notice that in the “Advanced” tab we added the action that when it runs successfully it will go to the next step. this means that when the Job runs successfully it will remove the file. If it doesn’t run, it will give an error.

2. The logic is: If there is no query longer than 30 seconds, delete the file.

Note: You need to release cmd_shell commands to perform the query, you only need to do this once. Example:

-- Liberar comandos cmd_shell
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO

-- Verifica se foi habilitado
sp_configure 'xp_cmdshell';
GO

Example of “General” tab:

Source: Author

Example of “Advanced” tab:

Source: Author

Now, what happens if it generates an error? I need to know, right? hmm, what about…

3. Navigate to the “Notifications” tab, where we will configure the notice.

The logic is: If there is a query longer than 30 seconds in the Trace, then the Agent will generate an error. If the Agent throws an error, then send me an email.

Example of “Notifications” tab:

Source: Author

To add operators who receive notifications, click here!

If you have any questions, get in touch!

Thanks and see ya!

Doing What Needs to Be Done.