SQL Query differences

30 July, 2012

Recently I had an issue where exactly the same query was working fine form SQL Management Studio but was extremely slow in my application. I did some digging and as expected everything comes to different execution plans. In SQL Server each query can have multiple query plans – separate plan for each query execution with different connection settings.

By default connection settings are set like this:

Default connection settings

The ARITHABORT flag is always different – so if you copy a query form application and execute it inside Management Studio – most likely different plan will be generated - because of different input parameters (parameter sniffing).

To be able to reuse existing plan (or increase chances to do so) you can use:

SET ARITHABORT OFF

or go and switch of below flag in SQL Management Studio:

SET ARITHABORT setting in MS SQL Management Studio
comments powered by Disqus