Thursday, February 16, 2012

Query plan troubleshooting

I was troubleshooting a medium-complexity query problem this morning in which the query execution within an app was orders of magnitude slower than the execution with the same parameters within Management Studio. I knew this could be due to different cached plans, but couldn't remember why.

Fortunately, Erland Sommerskog had written a detailed and helpful article explaining why this happens:

And indeed, the app was using ODBC and therefore the results of sys.dm_exec_plan_attributes showed ARITHABORT OFF, while my instance of Management Studio had it set to ON, therefore generating a separate cached plan. I'm going to update my settings in SSMS to set this off by default, especially since it appears that it has no effect with ANSI_WARNINGS set to on, other than to foil attempts to troubleshoot slow procs.

No comments: