1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.