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.
| Author |
Topic |
|
HWCsql
Starting Member
2 Posts |
 Posted - 2008-11-27 : 07:25:33
|
| Hi,I am new to this forum but not new to sql server. I was hoping you guys would be able to help me with the following problem:A certain stored procedure that normally executes in < 1 second is slowing down to about 1 minute about twice a day for around 30 minute periods, then suddenly it will execute fast again.This same SP always runs very quickly in query analyzer it is only when it is being called by the application.All other SPs in the database are fine.When i run sp_updatestats it fixes the issue and makes it perform fine but then a few hours later the problem happens again.The data in this table has not changed for days.Has anybody come across this sort of thing before? Many Thanks |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-27 : 08:37:18
|
| Is Auto update statistics turned on for that database? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-27 : 08:38:24
|
| check if some blocking is happening while its slowing down. Use sp_who2 to check process status when its running slow and check for BlkBy column to see if blocking occurs. |
|
|
|
HWCsql
Starting Member
2 Posts |
Posted - 2008-11-27 : 08:51:20
|
| Hi,Thanks for the replies, auto update statistics is turned on.I have used the sp_who2 method previously and there was nothing worth noting in there.A suggestion I have had from an expert is that Parameter Sniffing is happening so I am currently looking into that.Thanks |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-27 : 09:07:32
|
| see this for Parameter sniffing:http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!541.entry?wa=wsignin1.0 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-27 : 09:09:22
|
| Also worth analysing if there are some jobs executing on those times which makes use of some of tables sp uses |
|
|
|
|
|
|
SP slowing down for 30 minute periods