Track Long Running Queries

use master 
go

if exists(select name from sysobjects where name='LongQueryCreate' and type='P') drop procedure LongQueryCreate
go

create procedure LongQueryCreate as 

-- Recurring job to produce table of long-running queries
-- This job should run on a frequent basis (e.g. every 30 minutes or so) to
-- and will capture what has accumulated in the interim.  Queries are saved to 
-- a file which are imported at the beginning of each run.

-- There is a small window while it switches files where a query could finish
-- and not be recorded.  That's the tradeoff for being able to get it refreshed regularly.

-- Important customizations flagged with <<<
set nocount on 

declare @filespec_wo_type sysname         -- passed to create trace(name of trace output file)
declare @filespec sysname                 -- Same as above with .TRC added
declare @MoveFilespec sysname             -- File space after move for load
declare @TraceID int                      -- Trace number created/used
declare @maxfilesize bigint               -- Size in megabytes of max file created
Declare @Result int                       -- Temp variable for results
declare @Parameter varchar(255)           -- Temp variable for calling parameters 
declare @on bit                           -- Bit setting for setting events on 
declare @intfilter int                    -- Temp variable for setting filters
declare @bigintfilter bigint              -- Temp variable for setting filters
declare @msg varchar(255)                 -- Output message if we have to log errors
declare @procid varchar(255)              -- Procedure name
declare @FileFound int                    -- Return from checking for file, 1 = found 
declare @MaxDays int                      -- How many days of history to retain 

set @maxfilesize = 1000                   -- Max file size to produce, really a santiy check as it should never get close <<< 
set @MaxDays = 120                        -- How long to retain history <<<

-- The idiotic program to create a trace ALWAYS appends the .trc even if present, so we need two differnet strings

select @Filespec=Convert(varchar(255),serverproperty('MachineName'))
set @Filespec_wo_type = '\\' + @Filespec + '\dw_input\LongQuery'            -- <<< Where should trace file go
set @Filespec         = @Filespec_wo_type + '.trc'
set @MoveFilespec     = @Filespec_wo_type + '_Moved.trc'

-- Creates a table to record the long running queries, it's in master and named below, and grant access as desired <<<<< 

if not exists (select * from master.dbo.sysobjects where id = object_id(N'[master].[dbo].[LongQueries]') and Type='U')
begin

        -- The table below should contain fields for each field captured in the trace <<<<< 
        CREATE TABLE [master].[dbo].[LongQueries] (
        [RowNumber] [int] IDENTITY (1, 1) NOT NULL ,
            [MachineName] VarChar(20) null, 
        [EventClass] [decimal](18, 0) NULL ,
        [TextData] [varchar](7000) NULL ,          -- <<< Note SQL is stored as long string, not TEXT, could be changed if needed
        [NTUserName] [varchar] (128) NULL ,
        [HostName] [varchar] (128) NULL ,
        [ClientProcessID] [decimal](18, 0) NULL ,
        [ApplicationName] [varchar] (128) NULL ,
        [SQLUserName] [varchar] (128) NULL ,
        [Duration] [decimal](18, 0) NULL ,
        [StartTime] [datetime] NULL ,
        [Reads] [decimal](18, 0) NULL ,
        [Writes] [decimal](18, 0) NULL ,
        [CPU] [decimal](18, 0) NULL ,
        ) ON [PRIMARY]
        create clustered index LongQueriesIndx on master.dbo.LongQueries (StartTime) 
        grant select on LongQueries to Public
end 

-- First have to close the trace to release the file (recreate it later)

SELECT @Traceid = Traceid FROM :: fn_trace_getinfo(default) where convert(varchar(255), value) like '%LongQuery%'
if @Traceid is not null
begin
        exec @result=sp_trace_setstatus @Traceid,0
        if @Result<> 0 
        begin 
        set @msg = 'Procedure=LongQuery Create Job' +  
        ', SPID=' + convert(varchar(10),@@SPID) + 
        ', User=' + (Select suser_sname()) + 
                        ' Unable to stop trace, @result= ' + Convert(varchar(20), @Result)
        raiserror(@msg,1,1) with log        
            Return @Result 
        end 
        -- This deletes the trace.  Theoretically we do not need to, but in case we want to change 
        -- the filters, etc., this way you don't have to do anything special, just redefine the SP and it takes it next time
        exec @result=sp_trace_setstatus @Traceid,2
        if (@Result != 0 )
        begin 
        set @msg = 'Procedure=LongQuery Create Job' +  
        ', SPID=' + convert(varchar(10),@@SPID) + 
        ', User=' + (Select suser_sname()) + 
                        ' Unable to close/delete trace, @result= ' + Convert(varchar(20), @Result)
        raiserror(@msg,1,1) with log        
            Return @Result 
        end 
end

-- Find the file (if present) 

EXEC master..xp_fileexist @FileSpec, @FileFound OUTPUT

-- Load data in the file

if @FileFound=1 
begin
    
    -- Most errors from the above abort this batch, and the error recovery below never runs.
    -- The most common one is a corrupted or empty file, which won't load, and we would never delete 
    -- As a workaround, we MOVE the file first, and load from the moved file and delete it 
    -- then if we abort after the move, the next iteration will restart the trace as no file is present.

    Set @Parameter = 'DEL ' + @MoveFilespec
    exec @Result = [master].[dbo].[xp_cmdshell] @Parameter, no_output  -- No error check here
    Set @Parameter = 'MOVE  ' + @Filespec + ' ' + @MoveFileSpec 
    exec @Result = [master].[dbo].[xp_cmdshell] @Parameter, no_output

if @@Error<> 0 
begin 
        set @msg = 'Procedure=LongQuery Create Job' +  
        ', SPID=' + convert(varchar(10),@@SPID) + 
        ', User=' + (Select suser_sname()) + 
                ' Unable to move trace file prior to load, @@error= ' + Convert(varchar(20), @@Error)
        raiserror(@msg,1,1) with log        

    end 

insert into master.dbo.LongQueries (EventClass, MachineName, TextData, NTUserName, HostName, ClientProcessID, ApplicationName, SQLUserName, Duration, StartTime, Reads, Writes, CPU) 
SELECT EventClass, Convert(VarChar(20),serverproperty('MachineName')), convert(varchar(7000),TextData), NTUserName, HostName, ClientProcessID, ApplicationName, LoginName as SQLUserName, Duration, StartTime, Reads, Writes, CPU 
FROM ::fn_trace_gettable(@MoveFilespec, default) 

if @@Error<> 0 
begin 
        set @msg = 'Procedure=LongQuery Create Job' +  
        ', SPID=' + convert(varchar(10),@@SPID) + 
        ', User=' + (Select suser_sname()) + 
                ' Unable to load data from trace file, @@error= ' + Convert(varchar(20), @@Error)
        raiserror(@msg,1,1) with log        -- Not no return, we have cleared out the file, continue and try to set up the trace
end
    -- And delete the file 
    Set @Parameter = 'DEL ' + @MoveFilespec
    exec @Result = [master].[dbo].[xp_cmdshell] @Parameter, no_output
end 

-- Create a new trace and set it running

exec @result = sp_trace_create @TraceID output, 0, @FileSpec_wo_type, @maxfilesize, NULL 
if (@result != 0)
begin 
        set @msg = 'Procedure=LongQuery Create Job' +  
        ', SPID=' + convert(varchar(10),@@SPID) + 
        ', User=' + (Select suser_sname()) + 
                ' Unable to run the sp_trace_create, result='  + Convert(varchar(20), @Result)
        raiserror(@msg,1,1) with log        
        Return @Result 
end

-- Set the events  <<< If these are changed, adjust table contents.
-- These are all event 12 = Batch Completed 

set @on = 1
exec sp_trace_setevent @TraceID, 12,  1, @on -- Text data
exec sp_trace_setevent @TraceID, 12,  6, @on -- NT User name 
exec sp_trace_setevent @TraceID, 12,  8, @on -- Client host name 
exec sp_trace_setevent @TraceID, 12, 10, @on -- Application name 
exec sp_trace_setevent @TraceID, 12, 11, @on -- SQLSecurityLoginName
exec sp_trace_setevent @TraceID, 12, 12, @on -- SPID
exec sp_trace_setevent @TraceID, 12, 13, @on -- Duration (clock)
exec sp_trace_setevent @TraceID, 12, 14, @on -- Start time
exec sp_trace_setevent @TraceID, 12, 16, @on -- Reads 
exec sp_trace_setevent @TraceID, 12, 17, @on -- Writes 
exec sp_trace_setevent @TraceID, 12, 18, @on -- CPU Time
exec sp_trace_setevent @TraceID, 12, 35, @on -- Database Name 
exec sp_trace_setevent @TraceID, 12, 40, @on -- Database User Name 

-- Set the Filters  <<<

set @intfilter = 3000     -- This traps any CPU run time over 3 seconds.

exec sp_trace_setfilter @TraceID, 18, 0, 4, @intfilter

-- Set the trace status to start
exec @result = sp_trace_setstatus @TraceID, 1
if (@result != 0)
begin 
        set @msg = 'Procedure=LongQuery Create Job' +  
        ', SPID=' + convert(varchar(10),@@SPID) + 
        ', User=' + (Select suser_sname()) + 
                ' Unable to set status to started='  + Convert(varchar(20), @Result)
        raiserror(@msg,1,1) with log        
        Return @Result 
end

-- Purge old data beyond the limit.

delete from LongQueries where StartTime < DateAdd(d,-@MaxDays,GetDate())

go

--------------------------------------------------------------------------------------------------------------------------------
-- Job to run the LongQueryCreate procedure on a recurring basis
--------------------------------------------------------------------------------------------------------------------------------

BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
  IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
     EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'LongQueryRefresh')       
  IF (@JobID IS NOT NULL)    
      BEGIN  
      -- Check if the job is a multi-server job  
          IF (EXISTS (SELECT  * 
                      FROM    msdb.dbo.sysjobservers 
                      WHERE   (job_id = @JobID) AND (server_id <> 0))) 
          BEGIN 
            -- There is, so abort the script 
            RAISERROR (N'Unable to import job ''LongQueryRefresh'' since there is already a multi-server job with this name.', 16, 1) 
            GOTO QuitWithRollback  
          END 
      ELSE 
        -- Delete the [local] job 
        EXECUTE msdb.dbo.sp_delete_job @job_name = N'LongQueryRefresh' 
        SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'LongQueryRefresh', 
          @owner_login_name = N'sa', @description = N'Track long running queries on server', @category_name = N'[Uncategorized (Local)]', 
          @enabled = 1, @notify_level_email = 2, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, 
          @delete_level= 0, @notify_email_operator_name = N'DW Errors'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Refresh data', 
          @command = N'exec LongQueryCreate ', @database_name = N'master', @server = N'', 
          @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, 
          @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, 
          @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'FrequentRun', 
        @enabled = 1, @freq_type = 4, @active_start_date = 20020821, @active_start_time = 0, @freq_interval = 1, 
        @freq_subday_type = 8, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, 
        @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 

go