0

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.

  • 1
    Don't use profiler. You're going to cause more problems than you solve. And if you want to know the components that a process took, measure it using wait stats, not a trace. A trace just sums up how long it took - if you want to differentiate between time spent on the network vs. latching vs. anything else, you need a different tool than profiler. One question though - why is that query being executed through sp_execute? How is this query being sent to SQL Server? – Aaron Bertrand Mar 22 '16 at 19:03
2

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.