Tell me more ×
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. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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.

share|improve this question
that sounds great. thanks for the advice and the assistance!! – misterManager Mar 10 at 2:33
No problem. Good luck! – ErikA Mar 10 at 2:34
1  
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 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 at 13:28
2  
(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 at 13:40

migrated from serverfault.com Mar 10 at 6:00

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.