> SQL2000 Std Ed.

> Customers table 4 million records

> Users search by phone# - during periods of heavy use the cpu will peg to
> 100% and searches will take 20+ seconds.

> Here's the facts:
> - where cluase uses '... where fPhone like @TelNo '
> - No blocking, stored proc uses (nolock) isol. level.
> - Query plan uses index on fPhone field - total cost 1.895 and less w/ the
> more numbers entered in search.
> - No to < 5% fragmentation - rebuilt when at 5% or greater
> - Run profiler - see duration at 20000 milliseconds (rules out the
network -
> def. db)
> - For long running spid - run sp_lock and see TAB lock on customers table

> Was troubleshooting w/ user - user app in asp connecting w/ ado over
oledb -
> I was witnessing user spid taking 20+ seconds, I would grab exact same
query
> in profiler and run in Query Analyzer - it would take 1 seconds in QA -
user
> runs again 20 seconds, I run in QA - 1 to 2 seconds. User runs same
again -
> 20 seconds - data cache not speeding things up.

> I am assuming the app is getting stuck on a procedure cache that somehow
> ignores the index on fPhone and does table scan - searches with 2 or 3
> numbers do table scan - searches where all 7 numbers entered - index used.
> Perhaps 1 user enters 3 numbers to search on - table scan query plan saved
in
> cache - next user searches by 7 numbers - db uses query plan w/ table
scan.

> Is this table lock escalation - is that common during periods of high
> activity?

> Any ideas how to combat?

> Thanks,
> Chris