Message from discussion Performance Issue

Date: Fri, 15 Sep 2006 21:01:42 +0000 (UTC)
Message-ID: <c4366def1d3ca8c8a729a9b2e148@msnews.microsoft.com>
From: Simon Sabin <SimonSa...@noemail.noemail>
Subject: Re: Performance Issue
References: <0D4EA01E-209A-4654-BF81-8B85BAF147CF@microsoft.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 1046.1
Newsgroups: microsoft.public.sqlserver.programming
NNTP-Posting-Host: 84-92-17-17.plus.com 84.92.17.17
Lines: 1         
Path: g2news2.google.com!news3.google.com!news4.google.com!border1.nntp.dca.giganews.com!border2.nntp.dca.giganews.com!nntp.giganews.com!pd7cy3no!shaw.ca!news.alt.net!msrtrans!TK2MSFTFEEDS01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP02.phx.gbl

Hello Chris,

You shouldn't be getting a table lock. Can you change the SP to use WITH 
RECOMPILE?

What is the isolation level that is being used?

Is there any process updating the customer table?

How many reads/writes are occuring (look in profiler)

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons


> 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