sp_prepexec procedure problems

• bhaktaram

  SSC Enthusiast

  Points: 169

  More actions

  August 7, 2003 at 12:57 pm

  #58813

  Hi All:

  We have a booking engine for resorts reservations(cold fusion MX and

  sql server), our sql server is performing real slow, cpu utilization

  is abt 70 -95 % all the time.

  Analysis from sql profiler points to the procedure(below) taking a lot of processing time :

  declare @P1 int set @P1=NULL declare @P2 varchar(8000) set @P2=NULL

  exec sp_prepexec @P1 output, 210, 145, '02/14/04', '02/22/04', 600, 1,

  @P2 output select @P1, @P2 151.34 usr001 NULL server_name NULL 65

  2003-08-06 11:35:47.733 2003-08-06 11:38:19.077

  Please let me know what could be the problem.

  Regards

  Bhakta Ram

• Rajesh Patavardhan

  SSCrazy

  Points: 2801

  More actions

  August 7, 2003 at 10:58 pm

  #469117

  sp_prepexec does two things

  1. Prepares the Stored procedure execution plan

  2. Executes the stored procedure

  Now the first step usually takes some time depending on the complexity of stored procedure and WITH RECOMPILE option set/not set etc..

  Are you saying that sp_prepexec is appearing repeately and frequently for the same stored procedure?

  Then this will certainly be issue.

  If you are using ADO then create a ado command and prepare it only once and then use this command object to execute with different parameters.

  (similar approach should be possible in cold fusion also)

  In this case sp_prepexec will be called only once then sql server will use sp_execute

  Hope this helps.