SQL Server Trace oddity

Question

Post reply

SQL Server Trace oddity

Jaysen Courmac

SSC Eights!

Points: 920
More actions
October 31, 2005 at 2:56 pm

#112270

Has anyone run into this situation before?
I am running a trace on a single stored procedure call, for the events: SQL:BatchStarting,SQL:BatchCompleted,SP:Starting,SP:Completed.
When I run the proc in query analyzer, I get one of each of those lines, as I would expect.
When the developer I'm working with runs it in the application, using a .net procedure call (.Net SqlClient Data Provider), the trace shows over 1900 pairs of SP:Starting and SP:Completed, all but 4 of which have a duration of 0. We ran it in debug mode so are sure that it SHOULD have only run once.
Not being an application developer myself, I'm at a loss as to how to assist the developer in proceeding. From my viewpoint, the issue isn't with SQL Server, but it has an impact on my ability to properly trace the performance of this procedure.
Any ideas?
Jaysen

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 1

Try SP:StmtCompleted and SQL:StmtCompleted in addition to RPC:Completed (stored procedures) and SQL:BatchCompleted

Jaysen Courmac SSC Eights! Points: 920 More actions · Answer 2

Unfortunately, that doesn't help. I'm already getting more rows of data in the trace than I need -- I'm getting multiple SP:Completed rows and multiple SP:Starting rows for each call from the application.

Adding in SP:StmtCompleted and SQL:StmtCompleted just gives me additional rows of data for the statements within the procs.

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 3

I assume you have set the filtering properly to monitor only that statement on that very specific machine? (to exclude other users)

If you keep getting them double it must be the application sending out the command more than once.

Jaysen Courmac SSC Eights! Points: 920 More actions · Answer 4

I am monitoring only on the services machine (and it's only run by the services machine once every 5 minutes).

What's odd is that the additional rows (and there are thousands -- a single call becomes about 3600 rows in a trace from the app, while it's 4 if I run from query analyzer) have mostly durations of 0 when it's run from the application. There's no way this procedure call can complete in less than a second.