I have a sneaking suspicion that the guy who wrote the partitioning functions at my place of work did a fairly poor job. For certain queries (maybe 20% of them) we see incredible performance boosts when using
OPTION(LOOP JOIN). Isn't this bad?
Shouldn't partitioning be done in a balanced way that really allows the optimizer to take over and properly optimize? I know the optimizer isn't always right, and I don't know much about partitioning, but I just feel that queries shouldn't require hints as often as we use them. Thoughts?
We are currently running SQL Server 2008 R2.