How to create a specific SQL server trace?

donovan Stoltz
donovan Stoltz used Ask the Experts™
on
I know how to create a certain trace on the SQL Profiler but I have to....set up a SQL Server Profile trace on our prod server (SQL Server 2008) to capture execute times for a specific stored procedure, and to capture events where a query been delayed more than 2 seconds due to locks which I haven t done before.


Database: testdb
Stored proc: (sp_ex)
Elapsed: more than 300ms
Login: login\test

How would I go about this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> I know how to create a certain trace on the SQL Profiler
>> to capture execute times for a specific stored procedure

Since you know how to create a Profiler trace, then to capture the Execution time of a particular stored procedure, you can follow the below steps:
1. Create a New Profiler trace.
2. Give path to save your trace file and then Click on Events Selection.
3. Choose only RPC:Completed under Stored Procedures event. Remove all other events.
4. Ensure TextData column is Selected
5. Click on Show All columns and Choose Objectname column.
6. Now click on Column Filters and add a filter on either TextData column or ObjectName column.
a. If on TextData column, then use the value %ur_stored_procedure_name% in LIKE clause
b. If on Objectname column, then use the value ur_stored_procedure_name in LIKE clause.
7. Click OK and then start the profiler trace.

>> and to capture events where a query been delayed more than 2 seconds due to locks which I haven t done before.

Just follow the above steps with minor modifications.
1. Create a New Profiler trace.
2. Give path to save your trace file and then Click on Events Selection.
3. Choose only RPC:Completed under Stored Procedures event and SQL:BatchCompleted under SQL event. Remove all other events.
4. Ensure TextData column is Selected
5. Now click on Column Filters and add a filter on duration column and choose Greater than or equal to 2000 milliseconds.(2 Seconds)
7. Click OK and then start the profiler trace.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today