I am optimising a large SP in Sql Server 2008 which uses a lot of dynamic Sql. It is a query which searches the database with a number of optional parameters and short of coding for every possible combination of parameters dynamic sql has proven to be the most efficient method of executing this. The sql striung is built including parameters and then passed to sp_executesql with the param list. When running this in SSMS with any combination of parameters it runs very quickly (<1s) and returns results. When running from a windows forms application however, it sometimes takes considerably longer.
I have read that the difference in the ARITHABORT option can cause this (ON as default in SSMS and OFF in ADO) however I am unsure as to whether turning this on fixes the issue or whether it masks it? Does the difference in settings make a difference to the query itself or does it just mean that Sql Server will use different cached execution plans? If so should clearing the cache and statistics reset the playing field?
I have also read differing points of view on the OPTION RECOMPILE setting. My understanding is that when sp_executesql is used with a parameter list then each combination of parameters will produce an execution plane however as the possible combinations of parameters are finite this will result in optimised queries. Other sources say it should be set to ON at the start of any SP that uses dynamic sql.
I realise that different situations require different settings however I am looking to understand these further before trying the arbritraily on my very busy 24x7 production server. Apologies for the ramblings, I guess my question boils down to:
What causes sql to run differently in SSMS and Window Forms? If it is ARITHABORT then is this an issue related to execution plans or should I turn it on as a server default? What is the optimal way to run queries with dynamic sql?