Hmm. Just checked.

When a .NET application connects, this is what is sent through with the connection.

set quoted_identifier on

set arithabort off

set numeric_roundabort off

set ansi_warnings on

set ansi_padding on

set ansi_nulls on

set concat_null_yields_null on

set cursor_close_on_commit off

set implicit_transactions off

set language us_english

set dateformat mdy

set datefirst 7

set transaction isolation level serializable

SSMS is sending

set quoted_identifier on

set arithabort on

set numeric_roundabort off

set ansi_warnings on

set ansi_padding on

set ansi_nulls on

set concat_null_yields_null on

set cursor_close_on_commit off

set implicit_transactions off

set language us_english

set dateformat mdy

set datefirst 7

set transaction isolation level read committed

and SSRS is sending

set quoted_identifier on

set arithabort off

set numeric_roundabort off

set ansi_warnings on

set ansi_padding on

set ansi_nulls on

set concat_null_yields_null on

set cursor_close_on_commit off

set implicit_transactions off

set language us_english

set dateformat mdy

set datefirst 7

set transaction isolation level read committed

The ARITHABORT setting differs here, the ANSI WARNINGS don't. So is the ARITHABORT a red herring here and the ANSI setting overriding it (all databases are at level 100, we have nothing legacy fortunately)? If this is the case then I can emilinate it from my list and leave the developers in peace.

I can see that I have two execution plans for the same procedure, one runs like a dog and I can see this is the result of a clustered index scan (the procedure code needs a rework anyway). The other plan handles it a different way using an index lookup and is far better at what it does (this appears to have been created in our initial investigations running it through SSMS judging by the creation date and execution counts). I can invoke the bad plan by setting the arithabort off in SSMS and thereby simulate the performance issue that the .NET app is getting (30+ seconds to run the procedure as opposed to 1 second maximum). Switching the setting invokes the good plan. I'm at a complete loss as to why it decided to use a clustered index scan in the first plan, but I don't know what parameters it was being passed when the plan compiled.

This may just finally be a case of bad code, bad parameters and a bad plan.