Technical Article

Track Long Running Queries

,

SQL2000 only.

We use the attached script to schedule a SQL Agent job that sets up a Profile trace looking for long queries, and hourly imports them into a table.  This way we can historically (but with some degree of real time) look to see what queries put a load on the system.  We then allow people to query this directly or through a simple ASP web page.

There's a lot of code inside that should be customized - how often you want it to run (you can also run it any time from SQL Agent or directly), how long to keep data, what you consider long-running, where to keep the files, etc.  Think of it as a framework.

You may want to skip the scripted job creation and do it manually with your own criteria (e.g. this one reports errors to an operator DW ERRORS you will likely not have).

Note that it puts the procedure and table in MASTER.  Some won't like that, so don't just run this until you decide where you want it.

Feedback welcomed to Ferguson@chaslevy.com

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

Rate

5 (1)

Share

Share

Rate

5 (1)