Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


migration rejected from Jul 11 '13 at 7:57

This question came from our site for system and network administrators. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Paul White, Mat, Mark Storey-Smith, StanleyJohns, dezso Jul 11 '13 at 7:57

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

that sounds great. thanks for the advice and the assistance!! – misterManager Mar 10 '13 at 2:33
No problem. Good luck! – EEAA Mar 10 '13 at 2:34
Table partitioning is not meant to be a performance enhancer. It is for ease of maintenance. Sure, the optimizer could potentially use partition elimination, but that is a secondary and lesser benefit to the administrative benefit of partitioned tables. As for your particular implementation, we'd need to see specifics. – Thomas Stringer Mar 10 '13 at 11:24
There are lots of reasons why an execution plan may not be optimal. Don't jump to conclusions and blame partitioning. Can you describe your table structure, partitioning scheme, an actual query, and an actual (not estimated) execution plan (preferably captured from SQL Sentry Plan Explorer, which will contain additional runtime metrics and other information that a normal .sqlplan won't), with and without your OPTION (LOOP JOIN) hint. Disclaimer: I work for SQL Sentry, but that's not why I'm suggesting you download the free version of the tool. – Aaron Bertrand Mar 10 '13 at 13:28
(In other words, I think your question should be "Why does this query require a loop join hint to perform well?" instead of a rather open-ended "How do I know if my partitioning is set up right?") – Aaron Bertrand Mar 10 '13 at 13:40

Short answer would be: SQL Server does its best to guess which type of join algorithm to use, but sometimes it chooses a less optimal method. Forcing the hint ensures optimal performance. Usually what can be done is to rebuild the statistics and then remove the join hint in order for the server to figure out the best way for itself (since it bases the choice partly on the statistics).

One should also note that the join hint might have been used in order to change the execution plan on purpose (perhaps the initial employee saw some benefits when using that hint).

Another alternative, and I've seen this in practice, sometimes the database server get's upgraded (2000 -> 2005, or 2005 -> 2008 (R2) or 2008(R2) -> 2012) and the stored procedures are left the same, not realizing that the database engine has changed which may increase the performance of the query without the hint.

I hope this helps.