noob question about tracing and sp_prepexec

• teutales

  Say Hey Kid

  Points: 671

  More actions

  December 8, 2010 at 6:04 am

  #244063

  Hello all,

  I'm tracing a customers db with sp_trace_create. Looking at the result there is a abnormality. when I look in RPC:Completed -> TextData, I frequently find a procedure (fired by an apache tomcat), which is prepared and unprepared every time with another handle.

  A simple example:

  declare @p1 int set @p1=49824 exec sp_prepexec @p1 output,N'@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int',N'EXEC dbo.sp_dosomething @P0, @P1, @P2 ',N'51EE94CA-B5B4-40FF-B742-F05B7850D70A',N'AC24D538-4539-44F2-A70F-9CA4AA7250DC',0 select @p1

  ...

  exec sp_unprepare 49824

  ...

  declare @p1 int set @p1=50321 exec sp_prepexec @p1 output,N'@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int',N'EXEC dbo.sp_dosomething @P0, @P1, @P2 ',N'51EE94CA-B5B4-40FF-B742-F05B7850D70A',N'AC24D538-4539-44F2-A70F-9CA4AA7250DC',0 select @p1

  ...

  exec sp_unprepare 50321

  All parameters are the same... every time it's prepared. So in my opinion that's a waste of time, isn't it?

  Greetz

  Stefan

• GSquared

  SSC Guru

  Points: 260824

  More actions

  December 8, 2010 at 6:59 am

  #1260067

  It just means there's an application that uses cursors, and probably uses some internal numbering scheme for the handles on them.

  It's probably not that big a deal.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon