Message from discussion Performance Issue

From: "Jim Underwood" <james.underwoodATfallonclinic.com>
References: <0D4EA01E-209A-4654-BF81-8B85BAF147CF@microsoft.com>
Subject: Re: Performance Issue
Date: Fri, 15 Sep 2006 17:12:10 -0400
Lines: 68
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1807
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1807
Message-ID: <uwtgcuQ2GHA.324@TK2MSFTNGP05.phx.gbl>
Newsgroups: microsoft.public.sqlserver.programming
NNTP-Posting-Host: host12.fallon-clinic.com 65.206.105.12
Path: g2news2.google.com!news3.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!news.bnb-lp.com!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl

where fPhone like @TelNo

is @TelNo prefixed or suffixed with %?

if you prefix the search string with % then you can't really use an index.
If you append the % then you can, but the fewer values entered the less
efficient it is to use the index.

Is it really necessary to search on part of a phone number?  Would a use
ever not know the entire phone number?

It would likely be faster to do

where fPhone = @TelNo

Also, you could have a problem with parameter sniffing, but I'll let those
more knowledgeable explain that.

"Chris" <Ch...@discussions.microsoft.com> wrote in message
news:0D4EA01E-209A-4654-BF81-8B85BAF147CF@microsoft.com...
>
> 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
>
>