Wait time on server replies - Trying to decrease

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Wait time on server replies - Trying to decrease

Author

Topic

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2012-09-13 : 14:58:32

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]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-09-13 : 15:00:35

Are the statistics out of date on the tables? Run update stats, maybe even with fullscan.

We would need to know what the execution plan and/or the stats io/time were for before and after in order to know what happened. My guess is stats though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2012-09-13 : 15:46:37

Thanks a lot Tara.

I ran the following on all tables on the database in question.
But the server response time did not change:


USE <DBNAME>;
GO
UPDATE STATISTICS <tablename>
WITH FULLSCAN

Should I be doing something after updating the stats on the table?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-09-13 : 15:59:07

The first time you run it, it's going to be slower due to the compile time and having to get the data into cache. Does it speed up at all the second time you run it?

If it's the same, we'll need to see the execution plan and the output from stats IO/time when you execute your script.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2012-09-13 : 16:07:07

Thanks again Tara.
I got the following script from a friend of mine and I ran this and ran the script again and the response time went down to 200 ms. Thanks for pointing me in the right direction.

Is the reason this one worked that it had the "DBREINDEX" on it?


USE <DBNAME>
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_MSforeachtable @command1="print '?' UPDATE STATISTICS ? WITH FULLSCAN"
GO
DBCC FREEPROCCACHE
GO
DBCC UPDATEUSAGE (0);
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-09-13 : 16:46:04

It is doubtful that the reindex did it, however freeing the procedure cache might have as you might have encountered bad parameter sniffing. I would have thought updating stats with fullscan would have fixed it, however maybe you needed a lower sampling to get a good plan. Been there, done that.

You don't need the updateusage bit.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources