Avatar of rlep
rlep
 asked on

Different client results in different excution plan?

This is the second or third time I've run into this issue over the past several years and want to see if anyone else is experiencing it or has a solution....

I have an automated client app that executes a particular SP 5-10k times/day, typically in << 1s.  Saturday and Monday evening, the SP started timing out at the 30 second (connection timeout from the client).  I could make sense of the situation if every execution of the SP resulted in the same execution plan, but executing it from the client app resulted in slow plan and executing from Management Studio resulted in fast plan 40 seconds later.

Troubleshooting:
1.  Updated stats on Transaction Log      Result: No performance change
2.  Killed client app and restarted       Result: No performance change
2.  Restarted client PC and client app      Result: Performance back to normal.

I could probably resolve the symptom by providing an index hint to the SP, but I'm trying not to rewrite 100s of SPs to force execution plans.

Any help would be great!

-- Rob


**** Times out at 30 seconds
StmtText from PC1 app 2008-04-08 17:52:23:590  
--------
Top(TOP EXPRESSION:((1)))
|--Sort(ORDER BY:([STS].[dbo].[TransactionLog].[DatePerformed] DESC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([STS].[dbo].[TransactionData].[TransactionID], [Expr1015]) OPTIMIZED WITH UNORDERED PREFETCH)
|    |--Compute Scalar(DEFINE:([Expr1012]=CONVERT_IMPLICIT(numeric(18,5),[STS].[dbo].[TransactionData].[DataValue],0)))
|    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([STS].[dbo].[ActionDetails].[DetailID]) OPTIMIZED)
|    |         |--Index Seek(OBJECT:([STS].[dbo].[ActionDetails].[ActionID-DID-Other]), SEEK:([STS].[dbo].[ActionDetails].[DetailName]='RemainingBurnIn') ORDERED FORWARD)
|    |         |--Index Seek(OBJECT:([STS].[dbo].[TransactionData].[DID-Value-TID]), SEEK:([STS].[dbo].[TransactionData].[DetailID]=[STS].[dbo].[ActionDetails].[DetailID])ORDEREDFORWARD)
|    |--Index Seek(OBJECT:([STS].[dbo].[TransactionLog].[TSN-TransactionID]), SEEK:([STS].[dbo].[TransactionLog].[TSN]=[@TSN] AND [STS].[dbo].[TransactionLog].[TransactionID]=[STS].[dbo].[TransactionData].[TransactionID]) ORDERED FORWARD)
|--RID Lookup(OBJECT:([STS].[dbo].[TransactionLog]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)



**** Runs to completion in 19 mS
StmtText from Management Studio on PC2 at 2008-04-07 17:53:13.293      
--------
Top(TOP EXPRESSION:((1)))
|--Nested Loops(Left Semi Join, WHERE:([STS].[dbo].[TransactionData].[DetailID]=[STS].[dbo].[ActionDetails].[DetailID]))
|--Compute Scalar(DEFINE:([Expr1012]=CONVERT_IMPLICIT(numeric(18,5),[STS].[dbo].[TransactionData].[DataValue],0)))
|    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1004], [Expr1017]) WITH ORDERED PREFETCH)
|         |--Nested Loops(Inner Join, OUTER REFERENCES:([STS].[dbo].[TransactionLog].[TransactionID], [Expr1016]) WITH ORDERED PREFETCH)
|         |    |--Sort(ORDER BY:([STS].[dbo].[TransactionLog].[DatePerformed] DESC))
|         |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1015]) WITH UNORDERED PREFETCH)
|         |    |         |--Index Seek(OBJECT:([STS].[dbo].[TransactionLog].[TSN]), SEEK:([STS].[dbo].[TransactionLog].[TSN]=[@TSN]) ORDERED FORWARD)
|         |    |         |--RID Lookup(OBJECT:([STS].[dbo].[TransactionLog]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
|         |    |--Index Seek(OBJECT:([STS].[dbo].[TransactionData].[TransactionID]), SEEK:([STS].[dbo].[TransactionData].[TransactionID]=[STS].[dbo].[TransactionLog].[TransactionID]) ORDERED FORWARD)
|         |--RID Lookup(OBJECT:([STS].[dbo].[TransactionData]), SEEK:([Bmk1004]=[Bmk1004]) LOOKUP ORDERED FORWARD)
|--Index Seek(OBJECT:([STS].[dbo].[ActionDetails].[ActionID-DID-Other]), SEEK:([STS].[dbo].[ActionDetails].[DetailName]='RemainingBurnIn') ORDERED FORWARD)



Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
rlep

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
cm0605

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
rlep

ASKER
I'm not experiencing the problem now, but will try a recompile the next time it happens.  My big concern is not how to resolve the symptom, which this might help with, but rather to understand what is happening so that I know how to prevent it in the future.

rlep

ASKER
Any takers at 500 points?
rlep

ASKER
Not exactly the explanation I was hoping for, but it does seem to resolve the symptom.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy