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].[Transacti onLog].[Da tePerforme d] DESC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([STS].[dbo].[T ransaction Data].[Tra nsactionID ], [Expr1015]) OPTIMIZED WITH UNORDERED PREFETCH)
| |--Compute Scalar(DEFINE:([Expr1012]= CONVERT_IM PLICIT(num eric(18,5) ,[STS].[db o].[Transa ctionData] .[DataValu e],0)))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([STS].[dbo].[A ctionDetai ls].[Detai lID]) OPTIMIZED)
| | |--Index Seek(OBJECT:([STS].[dbo].[ ActionDeta ils].[Acti onID-DID-O ther]), SEEK:([STS].[dbo].[ActionD etails].[D etailName] ='Remainin gBurnIn') ORDERED FORWARD)
| | |--Index Seek(OBJECT:([STS].[dbo].[ Transactio nData].[DI D-Value-TI D]), SEEK:([STS].[dbo].[Transac tionData]. [DetailID] =[STS].[db o].[Action Details].[ DetailID]) ORDEREDFOR WARD)
| |--Index Seek(OBJECT:([STS].[dbo].[ Transactio nLog].[TSN -Transacti onID]), SEEK:([STS].[dbo].[Transac tionLog].[ TSN]=[@TSN ] AND [STS].[dbo].[TransactionLo g].[Transa ctionID]=[ STS].[dbo] .[Transact ionData].[ Transactio nID]) ORDERED FORWARD)
|--RID Lookup(OBJECT:([STS].[dbo] .[Transact ionLog]), 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].[Transa ctionData] .[DetailID ]=[STS].[d bo].[Actio nDetails]. [DetailID] ))
|--Compute Scalar(DEFINE:([Expr1012]= CONVERT_IM PLICIT(num eric(18,5) ,[STS].[db o].[Transa ctionData] .[DataValu e],0)))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1004], [Expr1017]) WITH ORDERED PREFETCH)
| |--Nested Loops(Inner Join, OUTER REFERENCES:([STS].[dbo].[T ransaction Log].[Tran sactionID] , [Expr1016]) WITH ORDERED PREFETCH)
| | |--Sort(ORDER BY:([STS].[dbo].[Transacti onLog].[Da tePerforme d] DESC))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1015]) WITH UNORDERED PREFETCH)
| | | |--Index Seek(OBJECT:([STS].[dbo].[ Transactio nLog].[TSN ]), SEEK:([STS].[dbo].[Transac tionLog].[ TSN]=[@TSN ]) ORDERED FORWARD)
| | | |--RID Lookup(OBJECT:([STS].[dbo] .[Transact ionLog]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
| | |--Index Seek(OBJECT:([STS].[dbo].[ Transactio nData].[Tr ansactionI D]), SEEK:([STS].[dbo].[Transac tionData]. [Transacti onID]=[STS ].[dbo].[T ransaction Log].[Tran sactionID] ) ORDERED FORWARD)
| |--RID Lookup(OBJECT:([STS].[dbo] .[Transact ionData]), SEEK:([Bmk1004]=[Bmk1004]) LOOKUP ORDERED FORWARD)
|--Index Seek(OBJECT:([STS].[dbo].[ ActionDeta ils].[Acti onID-DID-O ther]), SEEK:([STS].[dbo].[ActionD etails].[D etailName] ='Remainin gBurnIn') ORDERED FORWARD)
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].[Transacti
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([STS].[dbo].[T
| |--Compute Scalar(DEFINE:([Expr1012]=
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([STS].[dbo].[A
| | |--Index Seek(OBJECT:([STS].[dbo].[
| | |--Index Seek(OBJECT:([STS].[dbo].[
| |--Index Seek(OBJECT:([STS].[dbo].[
|--RID Lookup(OBJECT:([STS].[dbo]
**** 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].[Transa
|--Compute Scalar(DEFINE:([Expr1012]=
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1004], [Expr1017]) WITH ORDERED PREFETCH)
| |--Nested Loops(Inner Join, OUTER REFERENCES:([STS].[dbo].[T
| | |--Sort(ORDER BY:([STS].[dbo].[Transacti
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1015]) WITH UNORDERED PREFETCH)
| | | |--Index Seek(OBJECT:([STS].[dbo].[
| | | |--RID Lookup(OBJECT:([STS].[dbo]
| | |--Index Seek(OBJECT:([STS].[dbo].[
| |--RID Lookup(OBJECT:([STS].[dbo]
|--Index Seek(OBJECT:([STS].[dbo].[
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Any takers at 500 points?
ASKER
Not exactly the explanation I was hoping for, but it does seem to resolve the symptom.
ASKER