CODEWITHSUNDEEP
sql-serverdatabaseperformancetrace
Gyanendra Singh
Gyanendra Singh

Reputation: 1005

Query shows greater value of duration in trace than when the query is run by itself

The duration value in the sql server profiler trace shows a value larger for a query, than when I run the same query and take a look at the duration of execution for the query. Why is it so, or any other thing that I am missing out on?

Upvotes: 5

Views: 1665

Answers (2)

Paul
Paul

Reputation: 1059

Is the version your profiling part of an SP that is generated dynamically?

e.g.

..
@sql = 'select * from ' + @tablename
exec (@sql)

Most SPs are pre-compiled after their first execution but SPs with non-static code such as the snippet above would never become pre-compiled. But if in the query window you were running

select * from the_table_name

This might show the difference you're seeing. Bit of a guess though I'm afraid. Maybe you can tell us more about how the query is run, if it is part of an SP etc.

Upvotes: 1

Diego
Diego

Reputation: 36176

how larger? you know that in profiler the duration is stored in microseconds, right? So 1000000 means 1 second.

Besides that, you need to take into considerations other things like reuse of execution plan or even the fact that SSMS is an UI tool and may be doing something else with your query before sending it to the database, and profiler gets the statistics directly from the execution on the DB, which can explain a few seconds more on the execution

Upvotes: 1

Related Questions