Trace queries and Procedures taking 30 Second duration in SQL Server with SQL Profiler

SQL Profiler to trace queries taking 30-second duration in SQL Server

Set the SQL Profiler to trace the SQL Queries and Procedure taking 30 Second duration in SQL Server as follows:

  1. Open the SQL Profiler of SQL Server and start the tracing process:

2. Go to the Event table:

3. Select the column filter which is right side corner. Select the duration in the column filter. I used 30000 for the 30 seconds duration, the value entered as shown below is in milliseconds

4. I created and executed the procedure which have to wait for 30 seconds. so that it will take time to execute and captured in our SQL Profiler:


CREATE PROCEDURE testwaitfor30
AS
    SET NOCOUNT ON;
    waitfor delay '00:00:40'
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
Go

--Execute the procedure
Exec testwaitfor30
Go

5. Show the output of trace as below that starts capturing the procedure which takes more than 30 seconds duration while executing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.