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

Interactive Data and Clickthrough Reports

My reports are not allowing me to "Clickthrough" to more detailed reports.

I want my report to automatically show a detail record when they select some "interactive data" in my reports.

I have read a page on this stuff ( http://msdn2.microsoft.com/en-us/library/ms345252.aspx ) which states:

In Report Builder, a clickthrough report is a report that provides detailed information about the data contained within the main report. A clickthrough report is displayed when the Report Builder user clicks the interactive data that appears in the main report. These reports are automatically generated by the report server. You, as the model designer, determine what is displayed in clickthrough reports by setting the DefaultDetailAttribute and DefaultAggregateAttribute properties that you assign to an entity in the report model.

Hence, within my report model i have created an entity with a number of DefaultDetailAttributes.

I have not created a specific "click through" report as i would be happy to view the automatically generated report that should contain the attributes defined in the "DefaultDetailAttributes" list.

However, when i view the report, no data items are links (or "interactive data" as the msdn article describe them).

Has anyone got any ideas on how to make the report data interactive?

Thanks,

pete

We are not running SQL Server Enterprise edition. Doh.