SQL Server Profiler 2005: How to measure execution time of insert statement with trigger?

I want to measure the execution time (using I guess duration from SQL开发者_StackOverflow中文版 Server Profiler) of an insert statement that has an instead-of insert trigger on it. How do I measure the complete time of this statement including the trigger time?

The execution time (duration) that you see in SQL server profiler for a query is the time it took to execute that query including evaluating any triggers or other constraints.

Because triggers are intended to be used as an alternative way to check data integrity, an SQL statement is not considered to have completed until any triggers have also finished.

Update: An overview of some commonly used SQL Server profiler events:

• SQL:BatchCompleted Occurs when a SQL server batch (a group of statements) has completed execution - the duration is the total time to execute the batch.

• SQL:StmtCompleted Occurs when a SQL statement executed as part of a batch completes execution - again the duration is the time to execute that single statement.

• SP:Completed Occurs when a stored procedure has completed execution - the duration shown is the time to complete execution of the stored procedure.

• SP:StmtCompleted Occurs when an SQL statement executed as part of a stored procedure completes.

A batch is a set of SQL statements separated by a GO statement, however to understand the above you should also know that all SQL server commands are executed in the context of a batch*.

Also, each of the above events also has a corresponding Starting event - SP:Starting, SQL:BatchStarting, SQL:StmtStarting and SP:StmtCompleted. These don't list durations (as we don't know the duration yet because its not completed, however do help show when the duration recording starts from).

To better understand the relationship between these events, I recommend that you experiment with capturing some traces of some simple examples (from within SQL Server Management Studio), for example:

SELECT * FROM SomeTable
GO

SELECT * FROM SomeTable
SELECT * FROM OtherTable
GO

SELECT * FROM SomeTable
exec SomeProc
GO

As you should see, for each of the 3 examples above you always get a SQL:BatchStarting and SQL:BatchCompleted, the other event types however provide more detail on the individual commands run.

For this reason I generally tend to use the SQL:BatchCompleted event the most, however if the statement you are attempting to measure is executed as part of a larger batch (or in a stored procedure) then you may find one of the other event classes helpful.

See TSQL Event Category (MSDN) for more information on the various SQL Server Profiling events - there are lots!

Finally, if you are executing this command from within SQL Server Management studio, be aware that the simplest way to record the execution time is to use the client side statistics feature:

(*) I'm pretty sure that everything is executed as part of a batch, although I've not managed to find any evidence on the internet to confirm this.