Duration value in a Trace

Question

Post reply

Duration value in a Trace

rajankjohn

SSCertifiable

Points: 5061
More actions
March 26, 2010 at 3:21 pm

#143717

Hi,
I am having an issue where the same stored procedure with the same set of parameters is taking 1 second whne I run from SSMs while the .Net application takes more than 12 seconds. The same values are getting captured in the trace also. Can someone tell me exactly what is the meaning of the "Duration"? Does it include the time when the enmtire data is transferred over to the app layer, or is it just the data fetch time?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

OTF SSCertifiable Points: 6104 More actions · Answer 1

Have you checked the execution plans each call is using?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Didn't I just answer this question ... ?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Heh... I believe so.

Of course, there might be the "parameter sniffing" thing going on, as well. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vidya_pande SSCertifiable Points: 5275 More actions · Answer 4

if it is not parameter sniffing then it could be application which is taking more time to display or render the results and execution time at database is just 1 sec as captured by trace.

rajankjohn SSCertifiable Points: 5061 More actions · Answer 5

How can I check the execution plan of a query that is run from the application? Also, I know that ARITHABORT is ON in SQL Server and OFF in ADO.NET by default which can lead to this issue. Why should SQL Server generate two different execution plans when SET ARITHABORT is set to ON and OFF? Can you please provide me with some architectural link and MSDN links to this? I have a call with MS at 11 CST; I need to give explanations to the management :crying:

rajankjohn SSCertifiable Points: 5061 More actions · Answer 6

RBarry..I didnt see if you already replied to this in another post. Can you please provide me the post link? Thabnks in advance!

rajankjohn SSCertifiable Points: 5061 More actions · Answer 7

RBarryYoung (3/26/2010)
Didn't I just answer this question ... ?

Can you please sent me the link to the post you answered this? Thanks in advance!

rajankjohn SSCertifiable Points: 5061 More actions · Answer 8

Rajan John-243593 (3/29/2010)
How can I check the execution plan of a query that is run from the application? Also, I know that ARITHABORT is ON in SQL Server and OFF in ADO.NET by default which can lead to this issue. Why should SQL Server generate two different execution plans when SET ARITHABORT is set to ON and OFF? Can you please provide me with some architectural link and MSDN links to this? I have a call with MS at 11 CST; I need to give explanations to the management :crying:

Can someone please give me some hints on these?

harriga.rabie-1008938 Old Hand Points: 319 More actions · Answer 9

the duration is the time CPU end the time for read or write from buffer or from disk.

You can capture the excution plan for see the difference between plans.

If the parameters of procedures dont the same, you can do one alter procedure and excute your procedure with the parameter of application

OTF SSCertifiable Points: 6104 More actions · Answer 10

You can capture the Execution Plans with Profiler.

You can look up the "ShowPlan" Events in the Performance Category.

Try to avoid running Profiler against a Production Server, if you can but if you must

trace only the Events you need and save the trace results to a file.

A more efficient approach might be a Server side trace.