troubleshooting Question

Different client results in different excution plan?

Avatar of rlep
rlep asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
4 Comments1 Solution441 ViewsLast Modified:
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)



ASKER CERTIFIED SOLUTION
cm0605

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros