Sql Server query optimisation

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 tr开发者_C百科ying 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?

Run a trace in SQL Profiler to see what's actually being submitted to the server. Of course, you need to be aware of the impact of traces on production servers. In my experience very short traces that are limited to a small set are not a big problem for servers that don't have a very high transactions per second load. Also, you can run a trace server-side which reduces its impact so that's an option for you.

Once you see what's actually being submitted to the database this may help you understand the problem. For example, sometimes DB libraries prepare statements (getting a handle to a sort of temporary stored proc) but this can be costly if it is done for each issuance of the query, plus it's not needed with sp_executesql. Anyway, there's no way of knowing for sure whether it will be helpful until you try it.