A slow Stored Procedure is extremely faster (going from 2 secs to 8 millisecs) with QUERYTRACEON 4136 and ARITHABORT ON, on the local SQL Server 2008 I use to develop. The "fixed" SP, on another SQL Server 2008, goes back to slowness: with or without QUERYTRACEON 4136 and ARITHABORT ON, it doesn't make any difference.
Removing any fix and just adding WITH RECOMPILE gives me back the fast performances, so it's clear that the server is ignoring both fixes.
I didn't install that second server, while I installed my local dev server and it's "out of the box": I didn't touch any configuration. What I should check on the other server, what could cause it to ignore QUERYTRACEON and ARITHABORT ON?
An additional weirdness with ARITHABORT ON
Maybe this could help to figure out what's going on. Or maybe it will simply mud things even more.
SET ARITHABORT ON SET NOCOUNT ON
ARITHABORT is ignored. Even using WITH RECOMPILE.
SET NOCOUNT ON SET ARITHABORT ON
ARITHABORT is not ignored.