Ever wondered why you encounter slow queries in the application, but they work fine from SSMS?

I’m sure everyone encounters this issue sooner or later and it has many variations, so I’ll focus on the one I encounter most often.

Common scenario

You produce a great stored procedure, test it meticulously, it passes QA, ends up on production, and everyone is happy. Then couple of months later the client reports a timeout.

You identify the procedure easily (because you have database error logging), though it seems to be working on your end. Still, you queue up a recompile using sys.sp_recompile (the issue must be due to parameter sniffing) and ask the client to try again. But this time it doesn’t seem to be working.

Reason why you can’t get the same error as the client is because you’re both running different execution plans. Now… the real question is why are you getting different execution plans, but I won’t go into that because that has already been covered in maximum detail by this great article. What I’ll do is try to give you a couple of guidelines I follow when faced with this issue.

Troubleshooting

Execution plan you’ll get depends on connection settings you’re using. You can check connection settings by querying sys.dm_exec_sessions DMV, though most likely your application is using SET ARITHABORT OFF. Some applications use OFF by default and SSMS uses ON. Microsoft’s recommendation is to always use ON and, if viable, it would be best to update your applications accordingly. Setting it inside of the procedure is a hack and I would advise against this idea.

Sometimes only thing you need to do to get the procedure working is set ARITHABORT to OFF, mark it for recompile and execute it. You’ll generate a new plan and it will work for some time and then you’ll need to repeat the process.

SET ARITHABORT OFF;
EXEC sys.sp_recompile @objname = N'dbo.csp_SomeProcedure'; -- nvarchar(776)
EXEC dbo.csp_SomeProcedure;

Looks simple enough, right? It won’t always work because sometimes the engine will keep generating the same poor plan. If you have Query Store enabled, you might be successful by forcing a better plan.

I don’t like any of these temporary solutions and I only use them to buy time to dig deeper into the procedure; there’s always something that can be tweaked to help query engine generate better plans, no matter the connection settings.

Optimization guidelines

Some of the main culprits are:

  • scalar functions – look for ways to refactor them into (correlated) subqueries or views
  • complex subqueries – many queries can always be simplified
  • implicit conversions – sometimes implicit data conversion prevents query engine from using indexes
  • APPLY operators – these can be dangerous as scalar functions if not implemented correctly
  • TOP without ORDER BY – one would expect the results would be ordered by clustered index in that case, but query engine struggles sometimes
  • table variables – poor cardinality estimation
  • missing indexes – try adding some without breaking something else
  • numerous parameters – sometimes OPTIMIZE FOR UNKNOWN helps, sometimes you’ll need to get your hands dirty with some dynamic SQL
  • stale statistics – if the data changed too much since last refresh, check your maintenance plans or implement them

…and to infinity and beyond. There are too many to cover by a single blog post, but hopefully these hints help you in your quest.