Sample Header Ad - 728x90

SQL Server 2008 ignoring QUERYTRACEON 4136 and ARITHABORT ON

1 vote
1 answer
233 views
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.
Asked by motoDrizzt (288 rep)
Jun 24, 2018, 07:54 AM
Last activity: Nov 19, 2020, 09:06 PM