duplicate lines in SQL Server Profiler

sgray128
sgray128 used Ask the Experts™
on
Sirs:
I'm running a SQL 2000 'long running queries' trace, I have SP:Completed selected and I'm filtering for a duration of > 1000ms.

When this executes, frequently I see the same query in the output 3 or more time in succession, and I'm sure that the code is not doing that. Why would it appear like that?

I have the 'endTime' column included, and the matching lines have different 'endtimes'
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
If Profiler contained those entities several times, then its for sure that the query have been executed several times..
Different endtimes is a proof from my side to confirm it out..

Kindly check out the application name and the host name from where those requests origin to get it confirmed more..
G GodwinDatabase Administrator

Commented:
It is likely that you see it listed with several events.  Several events will list the same query, as the query has a start and a stop.  The query is a statement which will be in a batch and the batch may be in a Procedure...
e.g. Proc Start, Proc End, Stmt Start, and Stmt End...
Look at how many events you are tracing.
Best Regards,
-G

Author

Commented:
I only have one event monitored: SP COMPLETED, so I'm sure it's not the same thing showing up twice. The events have different 'end times'
I wrote the application, it is not possible that the Stored Proc is being kicked off multiple times... some of thes line create sales orders, I'd see that very easily.
Could this be an attempt to run the proc, but encountering locking and just retrying?

trace.jpg
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

G GodwinDatabase Administrator

Commented:
It would not be re-tries.
If it encounters locks, SQL will wait for locks to clear up, and then execute the work, but you should not see it "re-trying".  If it encountered locks that it could not resolve, it would deadlock and you would see a deadlock event, and one side of the deadlock would be the victim and would be rolled-back.
I can't see column headings, so I would just be guessing at this point.  However, I am not convinced that this is not simply multiple events being logged for the same work.
-G
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
In the snapshot attached, Event clause is not there at all.
Kindly include it which would tell what kind of events are those things..

And as mentioned earlier, no column headings which makes it non-readable..
Commented:
http://www.devnewsgroups.net/sqlservertools/t8354-profiler-logging-sp-starting-hundreds-times-one-sp.aspx
I think this answers it. They're saying that functions in the stored procs cause them to log multiple times.
G GodwinDatabase Administrator

Commented:
Yes.  
-G

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial