Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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...

share|improve this answer
    
    
@MartinSmith: it's in the accepted answer in my last link –  gbn Dec 29 '11 at 12:55
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.