Message from discussion Performance Issue

Thread-Topic: Performance Issue
thread-index: AcbZGokpbnoMc8p1STeoYLgkcp5QMA==
X-WBNR-Posting-Host: 65.125.177.4
From: =?Utf-8?B?Q2hyaXM=?= <Ch...@discussions.microsoft.com>
References:  <0D4EA01E-209A-4654-BF81-8B85BAF147CF@microsoft.com>
Subject: RE: Performance Issue
Date: Fri, 15 Sep 2006 15:59:02 -0700
Lines: 57
Message-ID: <E7254A17-6CA1-43AD-B375-15C9F38F5212@microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
Newsgroups: microsoft.public.sqlserver.programming
NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
Path: g2news2.google.com!news3.google.com!news2.google.com!news.maxwell.syr.edu!news.tele.dk!news.tele.dk!small.news.tele.dk!newsfeed.cw.net!cw.net!news-FFM2.ecrc.de!TK2MSFTFEEDS02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGXA01.phx.gbl


My suspicions were correct. User running tel search, taking 15 seconds. 
Found TAB lock on cust table. Ran same query in QA - 1 second.

Executed dbcc freeprocache

User query now executing in 1 second response time.

How is a bad plan getting stuck in cache? 

Is this a statistics issue or somehting? How do I rebuild/refresh stats? 
Index getting rebuilt when > 5% fragmentation.

Thanks,
Chris

"Chris" wrote:

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