| Author |
Query suddenly becomes very slow
|
|
| McGeeky 2006-03-16, 8:23 pm |
| Hi. This is a bit of an open ended question which I don't think there is a
single answer to but I woud appreciate suggestions on though. For the last
few months a stored procedure has been taking between 2-12 seconds to
complete. It runs several times a day. Suddenly today in the space of a
minute the stored procedure went from 2 seconds to complete to 35 minutes!
It was inexplicable. There was nothing else running on the machine (that I
know of) at the time. Further invocations continued to take at least 10
minutes to run. Only after bouncing the SQL Server instance did the stored
procedure go back to its normal timeframe of 2-12 seconds.
Nothing seemed out of the ordinary - it was just a regular day's processing
as had been many times in the past. How can there be such a huge decrease in
performance so rapidly? What could have caused it?
--
McGeeky
http://mcgeeky.blogspot.com
| |
|
| have you updated you table statistics?
and defrag your indexes
"McGeeky" <anon@anon.com> wrote in message
news:OnY8TKUSGHA.5656@TK2MSFTNGP11.phx.gbl...
> Hi. This is a bit of an open ended question which I don't think there is a
> single answer to but I woud appreciate suggestions on though. For the last
> few months a stored procedure has been taking between 2-12 seconds to
> complete. It runs several times a day. Suddenly today in the space of a
> minute the stored procedure went from 2 seconds to complete to 35 minutes!
> It was inexplicable. There was nothing else running on the machine (that I
> know of) at the time. Further invocations continued to take at least 10
> minutes to run. Only after bouncing the SQL Server instance did the stored
> procedure go back to its normal timeframe of 2-12 seconds.
>
> Nothing seemed out of the ordinary - it was just a regular day's
> processing
> as had been many times in the past. How can there be such a huge decrease
> in
> performance so rapidly? What could have caused it?
>
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>
>
| |
| McGeeky 2006-03-21, 8:23 pm |
| The database is configured to auto update stats.
Would not updating stats cause performance to drop off a cliff all of a
sudden?
--
McGeeky
http://mcgeeky.blogspot.com
"Jéjé" < willgart@BBBhotmailA
AA.com> wrote in message
news:#v41#IVSGHA.3192@TK2MSFTNGP09.phx.gbl...
> have you updated you table statistics?
> and defrag your indexes
>
> "McGeeky" <anon@anon.com> wrote in message
> news:OnY8TKUSGHA.5656@TK2MSFTNGP11.phx.gbl...
a[color=darkred]
last[color=darkred]
minutes![color=darkr
ed]
I[color=darkred]
stored[color=darkred
]
decrease[color=darkr
ed]
>
>
| |
|
| auto update is not enough.
a good and planned cleansing is always better.
1 client delete and reload some tables at regular basis, if we don't update
the statistics a query against these table drop from seconds to minutes.
so updating stats and defrag indexes is really important.
regarding which way you use to defrag your indexes, the system will also
update the stats at the same time.
see the BOL and search the web for more info
"McGeeky" <anon@anon.com> wrote in message
news:utYEAPSTGHA.5924@TK2MSFTNGP09.phx.gbl...
> The database is configured to auto update stats.
>
> Would not updating stats cause performance to drop off a cliff all of a
> sudden?
>
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>
> "Jéjé" < willgart@BBBhotmailA
AA.com> wrote in message
> news:#v41#IVSGHA.3192@TK2MSFTNGP09.phx.gbl...
> a
> last
> minutes!
> I
> stored
> decrease
>
>
|
|
|
|