Timming out sp (>15 secs), maybe lack of index/stats maintenance

richard101 2014-06-18 09:00:26

Hi again

This seems to run in under a second for me, though the customer sent a screen-shot of the front-end app timing out after 15 seconds.

Any help please

Richard

Aaron Bertrand 2014-06-18 11:36:03
While it's possible that the app is getting a different plan than SSMS, you should confirm that first. From here it sounds like the problem may be on the network side trying to transfer a lot of data or perhaps the app itself is doing sub-optimal things. Aside from comparing the plans, you could narrow this down by running the query in SSMS on the app server. Still fast there?
richard101 2014-06-18 11:59:36
Thanks Aaron, looks like it may have been a front-end code issue after all.
Mark Freeman 2014-06-19 01:50:27
When I ran into this situation with a stored procedure, it turned out that the application (which used Entity Framework) was getting a different plan than what I was getting through SSMS due to a different in the default SET options for each. I recompiled the stored procedure and the application started getting results as quickly as SSMS.