If Apps.mfrId is null, you are joining in every manufacturer.
From your testing, "OR ISNULL(apps.mfrId, 0) = 0" does this cheaper than "OR Apps.mfrId IS NULL OR Apps.mfrId = 0"
These do seem pretty equivalent, and you already do have a fix, so the question is just why and/or how to make the non-performer work.
When it comes to such performance, if everything you stated is correct, as we start to ask why, we start to point to the Query Optimizer. If it performs differently for the same parameters, it will have a different query plan. You'll probably see a table scan instead of index usage, or some other explanation to the bad performance.
I would encourage you to compare the query plans, or get someone else to, to help bring in an answer as to what it is doing differently. But, you already can tell it's doing something differently.
One possibility is that when Query Optimizer fixes are released, they by default are not turned on unless flag 4199 (for all fixes, or other flags for specific fixes) are turned on. This is because a general fix might be good for most, but also might break an application that was optimized in the environment of existing the prior quirks.
https://dba.stackexchange.com/questions/102292/trace-flag-4199-enable-globally
Does turning 4199 on help?
select top 2000 Apps.object_id
from
Manufacturer
INNER JOIN Apps ON (
Apps.mfrId = Manufacturer.Id
OR Apps.mfrId IS NULL
OR Apps.mfrId = 0
)
where
Apps.OBJECT_ID = 6879149
OPTION(QUERYTRACEON 4199)
Another related topic is parameter sniffing. Sometimes a query plan can get cached that is optimal for one parameter but horrible for another parameter. In your case one app might return 1 manufacturer, but another app might return all manufacturers, which is why this is worth mentioning. This usually shows up as the same code inconsistently performing poorly when given different parameters.
You can try turning off parameter sniffing or forcing recompiling to help diagnose if that appears to be part of the issue.
What are the main differences between OPTION(OPTIMIZE FOR UNKNOWN) and OPTION(RECOMPILE)?
I have seen situations where if felt like the Query Optimizer had given up on perusing indexes, just because the statement became too complex. But this doesn't seem likely in your example; other than the topic of query optimizer bugs get fixed, but to utilize them you still have to turn on query flags, or else that patch you install may very well do nothing.
Sometimes you can also try to help guide the sql optimizer. If there is an index that should always be used, that can be given as a query hint.
I'd also be curious to know if the following removes the issue:
select top 2000 Apps.object_id
from
(
select Apps.object_id, Apps.mfrId
from Apps
where Apps.OBJECT_ID = 6879149
) Apps
left join Manufacturer ON (
Apps.mfrId = Manufacturer.Id
OR Apps.mfrId IS NULL
OR Apps.mfrId = 0 )
If all query optimizer fixes are turned on, and the problem still persists, we have to ask why the query optimizer did this. It can only use indexes that exist, and it can only figure out if using an index will be beneficial if statistics exist. Meanwhile, outdated statistics will lead to bad choices. It can be good to rebuild/reorganize indexes and update statistics periodically. You may want to try doing so and see if it has any affect.
Conclusion
Since you already have a working fix, do use it. But your question is why do two very similar things achieve very different results. Assuming the same parameter is being used, and including the fact that your two options are so similar, the issue points to the query optimizer making a bad choice. This suggests you may need to both install the latest patches (if not installed) and also enable 4199 to actually enable all the query optimizer fixes that you have already installed via sql server patches. This includes adding OPTION(QUERYTRACEON 4199) to the bottom of your sql, or enabling 4199 globally, or similar.
