request/response style profiling

don't laugh,
is there some utility that enables a request/response style profiling on SQL
server.
this is really needed.
I mean not only seeing the SQL request like the SQL server profiler does,
but seeing also the response in some way, lets say first 10 rows etc.
it can really ease debugging
if there is no such way but you know how to do this using some profiling API
to SQL server it can also help.
TIA.
z f wrote:
> don't laugh,
> is there some utility that enables a request/response style profiling
> on SQL server.
> this is really needed.
> I mean not only seeing the SQL request like the SQL server profiler
> does, but seeing also the response in some way, lets say first 10
> rows etc.
> it can really ease debugging
> if there is no such way but you know how to do this using some
> profiling API to SQL server it can also help.
>
> TIA.
Profiler shows this is as the Duration, which is the time SQL Server takes
to execute and the client to fetch all results. You can see this easily with
a large enough table from SQL EM (do not do this in production). Start a
Profiler trace (RPC:Starting/Completed and SQL:BatchStarting/Completed
events). Open a table from SQL EM (All Rows). You'll see the starting event.
Hit CTR+END to fetch all rows. You'll then see the Completed event. Is this
what you are looking for? If not, it may be something you'll have to program
from the data access layer in your source code.
David Gugick
Quest Software
|||There's no way to get at the responses with SQL Profiler.
The only way I'm aware of to get this information without changing your app
is to use a protocol analyser such as Ethereal. Good news is that Ethereal
is free & conveniently exports its captures to xml files which are then
fairly easy to work with.
It wouldn't be too hard to write an xml parser to extract the first 10 rows
from the output..
Regards,
Greg Linwood
SQL Server MVP
"z f" <dont@.send.mails> wrote in message
news:eg0Uage6FHA.4076@.tk2msftngp13.phx.gbl...
> don't laugh,
> is there some utility that enables a request/response style profiling on
> SQL server.
> this is really needed.
> I mean not only seeing the SQL request like the SQL server profiler does,
> but seeing also the response in some way, lets say first 10 rows etc.
> it can really ease debugging
> if there is no such way but you know how to do this using some profiling
> API to SQL server it can also help.
>
> TIA.
>
>
|||does the protocol SQL server used is being parsed by ethereal (like HTTP )
to not show only the binary data?
is it public like HTTP?
because if not there is no way to parse the data.
thanks
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%23fysOpi6FHA.2608@.tk2msftngp13.phx.gbl...
> There's no way to get at the responses with SQL Profiler.
> The only way I'm aware of to get this information without changing your
> app is to use a protocol analyser such as Ethereal. Good news is that
> Ethereal is free & conveniently exports its captures to xml files which
> are then fairly easy to work with.
> It wouldn't be too hard to write an xml parser to extract the first 10
> rows from the output..
> Regards,
> Greg Linwood
> SQL Server MVP
> "z f" <dont@.send.mails> wrote in message
> news:eg0Uage6FHA.4076@.tk2msftngp13.phx.gbl...
>
|||Not having a public protocol certainly does NOT mean that there's no way to
parse the binary data. For example, some client access libraries such as ADO
have documented & even supported de-serialization methods which you can use
to reverse a byte stream to an instance of an object. This was a very common
technique amongst MSMQ developers years ago ( & even today ) as they would
often serialise an ADO recordset to a byte stream, pack it into a MSMQ
message, transmit it somewhere, then de-serialize it at the other end of the
wire. I don't have that code handy right now but can definitely dig it up if
you're looking to unpack older ADO stuff.
SQL Server's TDS protocol is documented to a reasonable extend here:
http://www.freetds.org. How useful this is to you will depend on which
version of SQL Server you're using (which you haven't stated) & also what
you're actually intending to do (eg script something for your own ad-hoc use
or build a toolset that you're planning to sell which requires significantly
more efffort).
The real trick here is to know what protocols you're intending to work with
& target them individually. Trying to build an all-encompassing solution
that covers all client access protocols probably isn't feasible, given the
number & versions of apis available.
I didn't mean to trivialise this when I suggested the idea. Whether this is
worth your while reallyy depends on how badly you need a solution to this.
If you're dealing with your own application, it may well be easier to simply
insert some extra code in your data layer & pluck the results from within
whatever library you're using.
Regards,
Greg Linwood
SQL Server MVP
"z f" <dont@.send.mails> wrote in message
news:eI50ahn6FHA.1416@.TK2MSFTNGP09.phx.gbl...
> does the protocol SQL server used is being parsed by ethereal (like HTTP )
> to not show only the binary data?
> is it public like HTTP?
> because if not there is no way to parse the data.
> thanks
>
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:%23fysOpi6FHA.2608@.tk2msftngp13.phx.gbl...
>