Spatial Index ignored when using additional non-spatial conditions

Question

Why does SQL Server ignore my spatial index when I use additional conditions? If I add an INDEX hint it will use the correct index, and runs a lot faster. I would like to avoid using the hint if possible.

Here is my SQL:

 PRINT CHAR(13) + 'Distance (No Hint)'
 SET STATISTICS TIME ON;
 SELECT p1.profileId, p2.profileId
 FROM profiles AS p1 WITH (NOLOCK)
 LEFT JOIN dbo.profiles AS p2 WITH (NOLOCK)
 ON p1.location.STDistance(p2.location) < 50 * 1609.344
 WHERE p1.profileId BETWEEN 1000 AND 1010
 SET STATISTICS TIME OFF;
 PRINT CHAR(13) + 'Distance + Gender (No Hint)'
 SET STATISTICS TIME ON;
 SELECT p1.profileId, p2.profileId
 FROM profiles AS p1 WITH (NOLOCK)
 LEFT JOIN dbo.profiles AS p2 WITH (NOLOCK)
 ON p1.location.STDistance(p2.location) < 50 * 1609.344
 AND p1.isMale = ~p2.isMale
 WHERE p1.profileId BETWEEN 1000 AND 1010
 SET STATISTICS TIME OFF;
 PRINT CHAR(13) + 'Distance + Gender (Hint)'
 SET STATISTICS TIME ON;
 SELECT p1.profileId, p2.profileId
 FROM profiles AS p1 WITH (NOLOCK)
 LEFT JOIN dbo.profiles AS p2 WITH (NOLOCK INDEX(IX_location))
 ON p1.location.STDistance(p2.location) < 50 * 1609.344
 AND p1.isMale = ~p2.isMale
 WHERE p1.profileId BETWEEN 1000 AND 1010
 SET STATISTICS TIME OFF;

And here are the results:

Distance (No Hint)
(2206 row(s) affected)
(1 row(s) affected)
 SQL Server Execution Times:
 CPU time = 359 ms, elapsed time = 295 ms.
Distance + Gender (No Hint)
(1000 row(s) affected)
(1 row(s) affected)
 SQL Server Execution Times:
 CPU time = 3323 ms, elapsed time = 9183 ms.
Distance + Gender (Hint)
(1000 row(s) affected)
(1 row(s) affected)
 SQL Server Execution Times:
 CPU time = 234 ms, elapsed time = 307 ms.

Answer 1

It would appear bitwise not in p1.isMale = ~p2.isMale causes the optimiser to pick wrong. Try p1.isMale <> p2.isMale instead

• Jake Braun: Thanks! Very interesting that the optimizer treats those expressions differently given that field is a BIT.
• MickyT: I found it weird too. Essentially it's exactly the same expression