If I capture an "exec sp_execute" command in SQL Profiler (2012), what exactly does the duration measure? I'm trying to understand whether or not factors outside of SQL Server (network or the client) can effect the duration. So if the duration of one call is higher than that of a similar call, is it correct to say "SQL Server took more time to process that call". Conversely if the duration includes the amount of time it takes the client to read the data, then I can not assume that the problem is on SQL Server (it could have been network or a problem with the client program).
I'm attempting to track down some performance problems, and the answer to this question will hopefully help me determine whether or not the problem is in SQL Server or some other component (network/client/etc.)
In case it makes a difference, I'm talking about executing a very simple select statement like:
SELECT * FROM Customer WHERE Id=? and Product='ABC'
Thanks in advance.
Best Answer
The short answer is that the unit description is mentioned in the answer to this question:
https://stackoverflow.com/questions/2590304/unit-of-measurement-for-duration-column-in-sql-profiler
Also, just to add on to Aaron Bertrand's comment, here is some information on why SQL Profiler should be avoided, and what to use instead (at least through SQL Server 2012):
Performance Impact: Profiler Tracing vs. Server Side SQL Tracing, by Linchi Shea
http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx
Basically, SQL Profiler can have a large impact on the remote server whereas SQL Trace on the server side has much less impact on the remove server. Google the following phrases for more info:
Server-Side Tracing and Collection
SQL Server Performance Statistics Using a Server Side Trace
Automating Server Side Tracing in SQL Server
How to identify SQL Server performance issues, by analyzing Profiler output?
Good luck.