Wait time on server replies - How to improve?

• Rainmaker097

  SSC Eights!

  Points: 866

  More actions

  September 13, 2012 at 1:09 pm

  #264076

  Hi,

  The following query used to take 200 seconds (Client statistics - Wait time on server replies). Now it takes 400 to 500 ms.

  The server is decent. This started only yesterday morning.

  I restored 3 non-related databases on the same server. The difference was not big. Not sure how that could have any impact on this. But I just wanted to let you know what happened.

  Can you please let me know what can be my next step in fixing this or investigating more. Thanks for your time..

  declare @p12 int

  set @p12=101

  declare @p13 numeric(29,4)

  set @p13=0.0063

  declare @p14 numeric(29,4)

  set @p14=324.3428

  declare @p15 numeric(29,4)

  set @p15=480.1358

  declare @p16 numeric(29,4)

  set @p16=766.0878

  declare @p17 int

  set @p17=100

  declare @p18 numeric(29,4)

  set @p18=10.0000

  declare @p19 int

  set @p19=2500

  declare @p20 int

  set @p20=10

  declare @p21 numeric(29,4)

  set @p21=225.0000

  declare @p22 numeric(29,4)

  set @p22=550.0000

  declare @p23 int

  set @p23=708516

  declare @p24 varchar(max)

  set @p24=NULL

  declare @p25 numeric(29,4)

  set @p25=32.9500

  declare @p26 varchar(max)

  set @p26='US'

  declare @p27 varchar(max)

  set @p27='Fid'

  declare @p28 varchar(max)

  set @p28='Comm.gif'

  declare @p29 varchar(max)

  set @p29='PPM_eng.gif'

  declare @p30 varchar(max)

  set @p30=NULL

  declare @p31 int

  set @p31=1

  declare @p32 int

  set @p32=1

  declare @p33 numeric(29,4)

  set @p33=325.0000

  declare @p34 numeric(29,4)

  set @p34=0.0000

  declare @p35 numeric(29,4)

  set @p35=125.0000

  declare @p36 numeric(29,4)

  set @p36=349.6084

  declare @p37 numeric(29,4)

  set @p37=0.0000

  exec CommCheck

  @Ad_Id='G6253',

  @Units=350,

  @Price=83.59,

  @F_Id=23,

  @HHTier=0,

  @CrossClient=0,

  @CrossHHTier=1,

  @SchedCommInput=497.72,

  @ProposedCommission=500,

  @LanguageId=1,

  @WithMessages=0,

  @Count=@p12 output,@AvgDistance=@p13 output,@AvgCommission=@p14 output,@TopQuartileLow=@p15 output,@TopQuartileHigh=@p16 output,@RepresentativeSampleSize=@p17 output,@PriceOut=@p18 output,@UnitsOut=@p19 output,@UniverseFirms=@p20 output,@ProposedCommissionOut=@p21 output,@SchedCommOutput=@p22 output,@UniverseTradesCount=@p23 output,@XMLMessages=@p24 output,@DiscountBrokerageRate=@p25 output,@Country=@p26 output,@DiscountBrokerageFirm=@p27 output,@CCheckLogo=@p28 output,@PMLogo=@p29 output,@Warning=@p30 output,@LanguageIdOut=@p31 output,@CrossClientOut=@p32 output,@DiscountSharingBegins=@p33 output,@DiscountSharingChargeBack=@p34 output,@DiscountSharingNoPayout=@p35 output,@MedianCommission=@p36 output,@FirmMinimum=@p37 output

  select

  @p12 [count], @p13 [AvgDistance], @p14 [AvgCommission], @p15 [TopQuartileLow], @p16 [TopQuartileHigh], @p17 [RepresentativeSampleSize], @p18 [PriceOut], @p19 [UnitsOut], @p20 [UniverseFirms], @p21 [ProposedCommissionOut], @p22 [SchedCommOutput], @p23 [UniverseTradesCount], @p24 [XMLMessages], @p25 [DiscountBrokerageRate], @p26 [Country], @p27 [DiscountBrokerageFirm], @p28 [CCheckLogo], @p29 [PMLogo], @p30 [Warning], @p31 [LanguageIdOut], @p32 [CrossClientOut], @p33 [DiscountSharingBegins], @p34 [DiscountSharingChargeBack], @p35 [iscountSharingNoPayout], @p36 [MedianCommission], @p37 [FirmMinimum]

• pooyan_pdm

  Hall of Fame

  Points: 3007

  More actions

  September 13, 2012 at 6:00 pm

  #1537586

  I don't know what's inside the proc can you send the actual execution plan ?

  try updating the statistics of the tables involved in the query.and also try executing the query with recompile option .

  Pooyan