Intermittent query slowdowns and corresponding high CPU utilization

Hi. We are experiencing an issue on SQL Server that sounds very simlar to
the hotifx detailed here: http://support.microsoft.com/kb/835864
Only trouble is we are running on a single CPU server when the article
suggests it is only applicable to multple CPU servers. Has anyone had
experience with this issue and resolving it?
--
McGeeky
http://mcgeeky.blogspot.comOn Fri, 28 Apr 2006 14:55:11 +0100, "McGeeky" <anon@.anon.com> wrote:
>Hi. We are experiencing an issue on SQL Server that sounds very simlar to
>the hotifx detailed here: http://support.microsoft.com/kb/835864
>Only trouble is we are running on a single CPU server when the article
>suggests it is only applicable to multple CPU servers. Has anyone had
>experience with this issue and resolving it?
I may be seeing it myself on a small, old two-processor box running on
only 512mb. Thanks for pointing out the article.
Microsoft is infamous for admitting to one special-case of bugs that
turn up in about a million other environments.
Doesn't guarantee that what you or I see is the same bug, since even
Microsoft may not have verified all the situations.
Josh|||Do you see it happen to any query? We are experiencing it only on one
query - it generally takes only 5-10 seconds but occassionally SQL Server
goes nuts and it takes 20 minutes with CPU on 100%.
--
McGeeky
http://mcgeeky.blogspot.com
"jxstern" <jxstern@.wherever.com> wrote in message
news:t5n452t4bn1h2r4f7iv4cpaqbutsch2skv@.4ax.com...
> On Fri, 28 Apr 2006 14:55:11 +0100, "McGeeky" <anon@.anon.com> wrote:
>>Hi. We are experiencing an issue on SQL Server that sounds very simlar to
>>the hotifx detailed here: http://support.microsoft.com/kb/835864
>>Only trouble is we are running on a single CPU server when the article
>>suggests it is only applicable to multple CPU servers. Has anyone had
>>experience with this issue and resolving it?
>
> I may be seeing it myself on a small, old two-processor box running on
> only 512mb. Thanks for pointing out the article.
> Microsoft is infamous for admitting to one special-case of bugs that
> turn up in about a million other environments.
> Doesn't guarantee that what you or I see is the same bug, since even
> Microsoft may not have verified all the situations.
> Josh|||On Tue, 2 May 2006 12:25:06 +0100, "McGeeky" <anon@.anon.com> wrote:
>Do you see it happen to any query? We are experiencing it only on one
>query - it generally takes only 5-10 seconds but occassionally SQL Server
>goes nuts and it takes 20 minutes with CPU on 100%.
Not at my current location.
Saw something that might have been this at a previous shop, where an
SP in a particular test (load) script goes nuts, but attempts to rerun
with even the same data via query analyzer see no problem.
There were a lot of executions of prepared plans involved, we might
have mismapped the numbers to the source, should have run a test with
SP statement start and plan capture set in profiler, so I can't be
ENTIRELY certain. And even if it was what it seemed, the cause might
not have been the memory allocation issue mentioned in the MS
document, since this wasn't really a high-transaction system.
Josh|||Your scenario sounds exactly like mine: its one query in particular but
attempts to run it in query analyzer with the exact same parameters fail to
reproduce the problem.
I like your idea of setting up a trace on the stored procedure plan. A
possibility is that SQL Server gets mixed up occasionally and creates a
really bad access plan.
--
McGeeky
http://mcgeeky.blogspot.com
"jxstern" <jxstern@.wherever.com> wrote in message
news:chsf521jm3l6e86tamq0c6t0covng5eg4m@.4ax.com...
> On Tue, 2 May 2006 12:25:06 +0100, "McGeeky" <anon@.anon.com> wrote:
>>Do you see it happen to any query? We are experiencing it only on one
>>query - it generally takes only 5-10 seconds but occassionally SQL Server
>>goes nuts and it takes 20 minutes with CPU on 100%.
> Not at my current location.
> Saw something that might have been this at a previous shop, where an
> SP in a particular test (load) script goes nuts, but attempts to rerun
> with even the same data via query analyzer see no problem.
> There were a lot of executions of prepared plans involved, we might
> have mismapped the numbers to the source, should have run a test with
> SP statement start and plan capture set in profiler, so I can't be
> ENTIRELY certain. And even if it was what it seemed, the cause might
> not have been the memory allocation issue mentioned in the MS
> document, since this wasn't really a high-transaction system.
> Josh|||Can you capture plans in SQL Server 2000?
--
McGeeky
http://mcgeeky.blogspot.com
"jxstern" <jxstern@.wherever.com> wrote in message
news:chsf521jm3l6e86tamq0c6t0covng5eg4m@.4ax.com...
> On Tue, 2 May 2006 12:25:06 +0100, "McGeeky" <anon@.anon.com> wrote:
>>Do you see it happen to any query? We are experiencing it only on one
>>query - it generally takes only 5-10 seconds but occassionally SQL Server
>>goes nuts and it takes 20 minutes with CPU on 100%.
> Not at my current location.
> Saw something that might have been this at a previous shop, where an
> SP in a particular test (load) script goes nuts, but attempts to rerun
> with even the same data via query analyzer see no problem.
> There were a lot of executions of prepared plans involved, we might
> have mismapped the numbers to the source, should have run a test with
> SP statement start and plan capture set in profiler, so I can't be
> ENTIRELY certain. And even if it was what it seemed, the cause might
> not have been the memory allocation issue mentioned in the MS
> document, since this wasn't really a high-transaction system.
> Josh