> 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?
|
