SQL Server 2008 ignoring QUERYTRACEON 4136 and ARITHABORT ON 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.

This way:

SET ARITHABORT ON
SET NOCOUNT ON


ARITHABORT is ignored. Even using WITH RECOMPILE.

This way:

SET NOCOUNT ON
SET ARITHABORT ON


ARITHABORT is not ignored.
I tried to include most of the details in your other question but specific to this question arithabort and query trace are not solutions just tools to find problems.  You need to implement a fix once you find the problem.  Arithabort may work temporarily because it forces a new plan for a while but it might go right back to what it did before.

Option recompile is an okay bandaid but you are creating an overhead by getting a new plan every single execution and especially if your stored procedure is executed many many times this can create issues.  Setting a local parameter to the parameter ie- Set @name = @lname is an old trick to fix before sql server had an adequate solution and we found this method hit or miss.  Optimize for unknown the microsoft supported hint for parameter sniffing has had the best results and kept company critical processes from having issues.  It is just a hint that basically says to the engine be extra careful about changing plans we found a problem here.