1

We had an issue in our dev environment where a procedure call timed out from the web server after 30 seconds. I traced the query and ran it manually (same params and all) from SSMS and it executed in about 2 seconds. I then ran

dbcc dropcleanbuffers
dbcc freeproccache

and after that the call from the web server also completed in time just fine.

I would suspect that the same plan should be used since I was using the exact same parameters from both connections, but I'm not sure.

My question is: Can there be different plans or buffers for different connections? Or could it have been some other side effect caused by me running the above dbcc-commands?

Andreas Ågren
  • 644
  • 4
  • 11

1 Answers1

3

When you say "same params and all" then you may find different credentials were used or the same compiled plan wasn't re-used based on timing.

I suspect here that the first web call and the SSMS call used different plans. The DBCCs then cleared the plan, so the second web call worked as expected

There are criteria that determine plan re-use such as using schema on all object references (eg dbo.table not table) which will be affected by different credentials.

However, the main culprit is usually parameter sniffing. See

Failing that, some SET options may be different too:

Note: these follow through to good background reading...

Community
  • 1
gbn
  • 69,809
  • 8
  • 163
  • 243
  • Erland's link worth mentioning as well – Martin Smith Dec 29 '11 at 12:54
  • Thanks for the links. Different credentials were used so that could be a reason then. But I'm not sure what you specifically meant by timing? In any way I guess this problem was more likely to be caused by different plans being used than something with the buffers, i.e. dbcc dropcleanbuffers probably didn't have any effect by itself? – Andreas Ågren Dec 29 '11 at 14:10
  • @Andreas:by timing, I mean a lag between web timeout and running in SSMS (eg lunch or such) which means the plan was invalid or purged. So the SSMS plan wasn't the same cached and compiled one from the web server. DBCC would clear the cache, but you did that after running in SSMS. – gbn Dec 29 '11 at 14:16
  • @Andreas: probably credentials then and something else preventing plan re-use – gbn Dec 29 '11 at 14:28
  • @gbn Doesn't it seem unlikely to be a parameter sniffing issue since both calls were using the same parameters to the same procedure? As you say different settings/credentials causing different plans to be selected seems more likely. If so, to me it would then seem that it's possible for one proc to have more than one plan in the cache at the same time, is that correct? – Andreas Ågren Dec 29 '11 at 14:38
  • yes, there are can more then one plan in cache – gbn Dec 29 '11 at 14:40