data "movement" the culprit?

sql2k sp3
I have a report query that sometimes runs in 3 seconds, other times I kill
it after 10 minutes. When its taking a long time, there is no no blocking, no
extremely high cpu/ memory usage, no huge amount of activity. Something I
just noticed though is that someone put the Clustered Index on a date filed
even though this is an OLTP DB. Is there any possibility that when its taking
a long time to return results, whats happening is that data was recently
inserted that would cause the data in the table to move because of where the
clustering is? That SQL is searching for a moving target based on the data
range of the SP, which is in the middle of being rearranged because of it's
clustering setup? If this were the case, would it cause actual blocking(dont
forget Im not seeing any.)? I know I'm grasping here, but dont know what else
could cause this.
TIA, ChrisR
It's unlikely that is the cause. A page split if it occurs should only take
ms not minutes. And you would see blocking. My guess is you have a bad query
plan and disk bottlenecks. But the only way to be sure is to monitor the
server from all aspects to see what is the bottleneck. What about wait's
during that time?
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> sql2k sp3
> I have a report query that sometimes runs in 3 seconds, other times I kill
> it after 10 minutes. When its taking a long time, there is no no blocking,
> no
> extremely high cpu/ memory usage, no huge amount of activity. Something I
> just noticed though is that someone put the Clustered Index on a date
> filed
> even though this is an OLTP DB. Is there any possibility that when its
> taking
> a long time to return results, whats happening is that data was recently
> inserted that would cause the data in the table to move because of where
> the
> clustering is? That SQL is searching for a moving target based on the data
> range of the SP, which is in the middle of being rearranged because of
> it's
> clustering setup? If this were the case, would it cause actual
> blocking(dont
> forget Im not seeing any.)? I know I'm grasping here, but dont know what
> else
> could cause this.
> TIA, ChrisR
|||I havent looked, but will. Does this number represent how long it take SQL to
satisfy a query? Im doing my testing in Query Analyzer so I can already tell
that.
"Andrew J. Kelly" wrote:

> It's unlikely that is the cause. A page split if it occurs should only take
> ms not minutes. And you would see blocking. My guess is you have a bad query
> plan and disk bottlenecks. But the only way to be sure is to monitor the
> server from all aspects to see what is the bottleneck. What about wait's
> during that time?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
>
>
|||Waits tell you allot about what sql server is waiting on most. See here for
more details:
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90871384-25AD-485B-BB51-B623B8671647@.microsoft.com...[vbcol=seagreen]
>I havent looked, but will. Does this number represent how long it take SQL
>to
> satisfy a query? Im doing my testing in Query Analyzer so I can already
> tell
> that.
> "Andrew J. Kelly" wrote: