Bitwise operators affecting performance

I have recently seen some stored procs coming my way that contain BITWISE operators that are causing the optimizer problems. I know from playing with execution plans that these are the cause of the issue, but I'm not 100% sure the reasoning. Is this because of short circuiting not functioning as procedural languages. With SQL server then evaluates everything in the WHERE clause at the same time and is forced to scan the whole table?

Attached is a snippet of code from inside the proc, I have amended slightly for testing. How can I rewrite this to remove the BITWISE?

```
DECLARE
@AdvertiserId INT,
@DirtyReason INT

SET @advertiserid = 3
SET @dirtyreason = 7

BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--DROP TABLE #temparraytable;

CREATE TABLE #tempArrayTable (advertiserHierarchyId int)
INSERT INTO #tempArrayTable (advertiserHierarchyId)
(SELECT convert(int,str) FROM dbo.SplitString('14167',','))

CREATE INDEX #ix_tempArrayTable ON #tempArrayTable (advertiserHierarchyId)

SELECT DISTINCT MA.ContentNetwork,MA.DestinationURL, MA.MasterAdGroupId, AA.Title, MA.ShortTitle, ma.LinkedObjectId, ma.LinkedObjectTypeId, MA.MasterHierarchyId,
AA.AdvertiserAdGroupId, AA.AdvertiserId, AA.AdvertiserHierarchyId, AdvertiserAdGroupCode, AA.AdvertiserHierarchyId, AA.Dirty,
AA.Deleted, AA.Paused, MA.MatchTypeId,
lob.MaxCostPerClick AS 'LinkedObjectROIMAXCPC'
FROM admanMasterAdGroup_tbl MA
INNER JOIN admanAdvertiserAdGroup_tbl AA ON MA.MasterAdGroupId = AA.MasterAdGroupId
INNER JOIN admanAdvertiserCreative_tbl AC ON AA.AdvertiserAdGroupId = AC.AdvertiserAdGroupId
INNER JOIN admanAdvertiserHierarchy_tbl AH ON AA.AdvertiserHierarchyId = AH.AdvertiserHierarchyId
INNER JOIN #tempArrayTable tat ON tat.advertiserHierarchyId = AA.AdvertiserHierarchyId
LEFT JOIN admanLinkedObjectROIBid_tbl lob ON lob.LinkedObjectId = ma.LinkedObjectId AND

Short circuiting does not always function the way you might expect in SQL Server. You really have very little say in what order your where clauses are evaluated; even if you do this filtering in a subquery or CTE first, the optimizer still might evaluate it in an order you don't expect. The only two options I've seen to possibly overcome this:

(a) dump the intermediate filtered results to a #temp table.

(b) try adding the bitwise operator to the ON condition, instead of the WHERE clause, and use FORCE ORDER to ensure that the joins are evaluated in the order that you write them. I don't understand the differences between your two queries, and I think the question contains a lot of unnecessary code, but I would try this variation:

FROM 
  dbo.admanAdvertiserAdGroup_tbl AS AA 
INNER JOIN 
  dbo.admanAdvertiserCreative_tbl AS AC 
    ON AA.AdvertiserAdGroupId = AC.AdvertiserAdGroupId 
    AND (AC.Dirty & @dirtyReason > 0)
INNER JOIN 
  dbo.admanMasterAdGroup_tbl AS MA  
    ON MA.MasterAdGroupId = AA.MasterAdGroupId 
...
WHERE AA.AdvertiserId = @AdvertiserId 
...
OPTION (FORCE ORDER);

(I've also taken the liberty to add dbo. to your table references, which you should always be using, and AS for readability. Sorry, I am a little meticulous about this.)

No guarantees, obviously, but worth a try. This is assuming that your conclusion about short circuiting is even correct: how did you come to this conclusion? Can you show the execution plans and what led you to short circuiting as a possible culprit? Is the bulk of the query dealing with way too many rows that theoretically should have been filtered out by the bitwise operator?

EDIT

I've blogged about this scenario: http://www.sqlperformance.com/2012/08/t-sql-queries/dry-principle

StackExchange Database Administrators Q#19549, answer score: 3