Rules and Cost Based Optimization

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TABMCSE - 25 Feb 2005 18:05 GMT

I have been trying to find out what optimization method SQL Server 2000
uses. Of the little that I have found on the subject, indicates that SQL 2000
uses Cost Based, however, I was told by our consultant that this is incorrect
and it is really or should really be rules based. I need to find a way to see
what method we are using and to change it to rules based method to improve
performance due to the way the database was designed. Please advise. Thank
you.

Reply to this Message

Andrew J. Kelly - 25 Feb 2005 18:11 GMT

You should find a new consultant. The engine is essentially cost based and
there are no rules short of query hints to change the way it optimizes the
query. If you designed a poor database you will probably have less than
optimal performance. But that does not mean you can not tune it. You don't
change the engine you change the way you access the data by making sure you
tune the queries and the schema as best you can.

Signature

Andrew J. Kelly SQL MVP

> I have been trying to find out what optimization method SQL Server 2000
> uses. Of the little that I have found on the subject, indicates that SQL
[quoted text clipped - 6 lines]
> performance due to the way the database was designed. Please advise. Thank
> you.

Reply to this Message

TABMCSE - 25 Feb 2005 18:19 GMT

Thank you for your quick response. The consultant, upon me letting him know
this information, has recanted his position and stated that he was thinking
of Oracle. I think it is time for a new consultant! Thanks again!

> You should find a new consultant. The engine is essentially cost based and
> there are no rules short of query hints to change the way it optimizes the
[quoted text clipped - 13 lines]
> > performance due to the way the database was designed. Please advise. Thank
> > you.

Reply to this Message

pdxJaxon - 25 Feb 2005 18:38 GMT

Sounds like someone got caught blowin smoke....

Kinda hard to trust much moving forward.

GAJ

Reply to this Message