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
Last activity: Nov 19, 2020, 09:06 PM