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?
> 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?
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
> 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?
> 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?
SQL Server doesn't know it is a "Bad" plan or it wouldn't have generated it in the first place. Plans are supposed to be in the cache. This and the associated articles will go a long way towards defining how that all works and hopefully answer your questions in that regard. http://msdn2.microsoft.com/en-us/library/ms181055.aspx
-- Andrew J. Kelly SQL MVP
"Chris" <Ch...@discussions.microsoft.com> wrote in message
> 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?
>- Run profiler - see duration at 20000 milliseconds (rules out the >network - def. db)<
Duration takes into account network time, so this metric alone doesn't rule out the network, although this also isn't the first place I'd look.
You also said that the query is using an index on fPhone, but that a TAB lock is being taken. Whilst this can occur, you really need to provide more information to make these observations meaningful (eg, is the TAB lock an intent lock etc).
Any chance you can post the execution plan & sp_help output for the table?
Also - are you using a LIKE predicate because of the possibilities of area codes prefixing the ph no? I had an idea whilst reading your post which might work for you.. Because area codes always prefix a ph no, the front part of the number is variable (depending on how the user inputs their ph no). But the end of the ph number should always be the same, right? If this assumption is correct, it should be possible to store the number in reverse (also removing whitepsaces) in a second column & index that column. Then, to query the column, you flip your numbers around (& remove whitepsace) and compare this value against the index, using LIKE & you should get vastly improved performance from a range scan.. Just an idea..
> 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?
I created a sample script on my blog to demo how storing a reversed version of each phone number in a seperate column can avoid your wildcard search, allowing lightening fast index seeks instead of the table scan you're currently doing.
> I'm wondering what you mean by: >>- Run profiler - see duration at 20000 milliseconds (rules out the >>network - def. db)< > Duration takes into account network time, so this metric alone doesn't > rule out the network, although this also isn't the first place I'd look.
> You also said that the query is using an index on fPhone, but that a TAB > lock is being taken. Whilst this can occur, you really need to provide > more information to make these observations meaningful (eg, is the TAB > lock an intent lock etc).
> Any chance you can post the execution plan & sp_help output for the table?
> Also - are you using a LIKE predicate because of the possibilities of area > codes prefixing the ph no? I had an idea whilst reading your post which > might work for you.. Because area codes always prefix a ph no, the front > part of the number is variable (depending on how the user inputs their ph > no). But the end of the ph number should always be the same, right? If > this assumption is correct, it should be possible to store the number in > reverse (also removing whitepsaces) in a second column & index that > column. Then, to query the column, you flip your numbers around (& remove > whitepsace) and compare this value against the index, using LIKE & you > should get vastly improved performance from a range scan.. Just an idea..
>> 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?
I need to remove duplicate rows from a table. The process that I am using, which is shown below, is extremely slow - it takes about 30 minutes to process 2000 rows. Can anyone point out any obvious reasons that my technique is slow? - or perhaps propose a better technique?
DECLARE ABC_Cursor CURSOR FOR SELECT APPLICATION_NR, REASON, REASON_TYPE FROM Table_12 ORDER BY APPLICATION_NR, REASON, REASON_TYPE
OPEN ABC_Cursor
FETCH NEXT FROM ABC_Cursor /* Prime the cursor */ INTO @APPLICATION_NR, @REASON, @REASON_TYPE
WHILE @@FETCH_STATUS = 0 BEGIN
SET @LATEST_DCSN_TM = (SELECT MAX(DCSN_TM) FROM Table_12 WHERE APPLICATION_NR = @APPLICATION_NR AND REASON = @REASON AND REASON_TYPE = @REASON_TYPE)
SET @DELETE_STRING = 'DELETE FROM Table_12 WHERE DCSN_TM <> '''+@LATEST_DCSN_TM+''' AND APPLICATION_NR = '''+@APPLICATION_NR+''' AND REASON = '''+@REASON+''' AND REASON_TYPE = '''+ @REASON_TYPE+ ''''
EXEC(@DELETE_STRING)
FETCH NEXT FROM ABC_Cursor INTO @APPLICATION_NR, @REASON, @REASON_TYPE END
/* Clean house, always de-allocate the cursor */
CLOSE ABC_Cursor DEALLOCATE ABC_Cursor ---------------------------------------------------------- Thanks, Jim
> I need to remove duplicate rows from a table. The process >that I am using, which is shown below, is extremely slow - it takes >about 30 minutes to process 2000 rows. Can anyone point out any >obvious reasons that my technique is slow? - or perhaps propose a >better technique?
Hi Jim,
Try it with this:
DELETE FROM Table_12 WHERE DCSN_TM <> (SELECT MAX(a.DCSN_TM) FROM Table_12 AS a WHERE a.APPLICATION_NR = Table_12.APPLICATION_NR AND a.REASON = Table_12.REASON AND a.REASON_TYPE = Table_12.REASON_TYPE);
|
