How to run SQL Profiler to monitor 1 specific stored procedure?

Question

I have an issue where a stored procedure will run properly when executed from SQL Management Studio, but when the stored procedure is called from an application, it doesn't return the results to the application. This application regularly utilizes stored procedures as part of it's operation, so why this specific SP isn't returning results is odd.

Since the application works fine otherwise and since the stored procedure works just fine when run in SQL, what I'd like to do is setup sql profiler to monitor just that stored procedure, while I try to execute/call it from the application.

I'm hoping to avoid both the extra load on the system and sifting through a ton of irrelevant data that a generic profiler run would provided on a busy database like this one.

Thank you in advance for any advice on this.

score 0 · Answer 1 · edited Apr 10 '17 at 19:02

Following code will do trace one stored procedure. Check for folder location, duration and like statement for the name of your stored procedure. I also excluded any replication related stored procedure in case you have replication. Let me know if you run into any issues.

-- Variables
DECLARE @rc       INT, 
        @TraceID  INT, 
        @filename NVARCHAR(256), 
        @maxsize  BIGINT, 
        @on       BIT 
DECLARE @Folder VARCHAR(1000) 

SET @Folder = 'D:\Proc_Trace' 

-- Check for other running traces 
SELECT @rc = Count(DISTINCT traceid) 
FROM   ::fn_trace_getinfo(DEFAULT) 

IF ( @rc > 1 ) 
  GOTO error 


-- Create the holding folder
IF RIGHT(@Folder, 1) <> '\'
    SET @Folder = @Folder + '\'

SELECT @Folder = @Folder + 
    'output_' +
    CONVERT(VARCHAR(8), GETDATE(), 112) + '_' +
    LEFT(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', ''), 4)

EXEC master.dbo.xp_create_subdir @Folder

SELECT @filename = @Folder + '\' + REPLACE(@@SERVERNAME, '\', '_') + '__sp_trace'
SELECT @maxsize=50, @on=1


-- Create a Queue
EXEC @rc = sp_trace_create @TraceID OUTPUT, 2, @filename, @maxsize, NULL 
IF (@rc != 0) GOTO error


-- Set the events
EXEC sp_trace_setevent @TraceID, 11, 2, @on  -- Needed for 2008 RML


EXEC sp_trace_setevent @TraceID, 11, 3, @on
EXEC sp_trace_setevent @TraceID, 11, 7, @on
EXEC sp_trace_setevent @TraceID, 11, 15, @on
EXEC sp_trace_setevent @TraceID, 11, 8, @on
EXEC sp_trace_setevent @TraceID, 11, 1, @on
EXEC sp_trace_setevent @TraceID, 11, 14, @on

EXEC sp_trace_setfilter @TraceID, 1, 0, 6, N'%p_FoldersApplicants_sel_All_Candidates_OBy_CreatedDate_DESC%'
EXEC sp_trace_setfilter @TraceID, 6, 0, 7, N'%repl%'


-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1


-- Wait 10 min
WAITFOR DELAY '08:00:00'

-- Stop/Delete the trace

EXEC sp_trace_setstatus @traceID, 0
EXEC sp_trace_setstatus @traceID, 2

GOTO finish

ERROR: 
SELECT ErrorCode=@rc

FINISH: 

--getting result

SELECT 
      StartTime, 
      HostName, 
      SUBSTRING(TextData, CHARINDEX('EXEC', TextData), 1000) AS ProcCall,
      SUBSTRING(TextData, 103, 1000) AS Parameters
FROM ::fn_trace_gettable('D:\Proc_Trace\output_20111010_1009\filename.trc', DEFAULT)
WHERE EventClass = 11
ORDER BY StartTime

How to run SQL Profiler to monitor 1 specific stored procedure?

1 Answers1