Hi,
I'm very interested in query optimization and I really enjoy this job. I
have read everything in BOL and also books like "Inside SQL Server 2000" and
"Guru's Guide to SQL Server Architecture and Internals" and "Microsoft SQL
Server 2000 Performance Optimization and Tuning Handbook".
But in new groups I read some information from MVPs that is not included in
any of mentioned resources. I'd like to know if there are other references
( like web sites, books ,...) that can give more information about this
Artificial Intelligence (Query Optimizer!).
Thanks in advance,
LeilaLeila,
SQL Server Performance Tuning Tips
http://www.sql-server-performance.com/tips_performance.asp
Lot of info in the MVP's web site.
Erland Sommarskog's home page
http://www.sommarskog.se/
http://vyaskn.tripod.com/
http://www.aspfaq.com/
http://www.karaszi.com/SQLServer/default.asp
http://users.drew.edu/skass/sql/
AMB
"Leila" wrote:
> Hi,
> I'm very interested in query optimization and I really enjoy this job. I
> have read everything in BOL and also books like "Inside SQL Server 2000" and
> "Guru's Guide to SQL Server Architecture and Internals" and "Microsoft SQL
> Server 2000 Performance Optimization and Tuning Handbook".
> But in new groups I read some information from MVPs that is not included in
> any of mentioned resources. I'd like to know if there are other references
> ( like web sites, books ,...) that can give more information about this
> Artificial Intelligence (Query Optimizer!).
> Thanks in advance,
> Leila
>
>|||hmmm. the optimizer is pretty smart, but you are smarter.
rather then learning how the optimizer works, i spent time figuring out
how I would like my queries processed, and how to encourage (FORCE!)
the optimizer to do it the fastest way.|||Yes, people are smarter than computers, but computers are usually faster.
Why is it that so few people can actually beat a computer at chess? The
computer can evaluate millions of possible moves in less time than a human
can physically move a pawn. So the SQL Server optimizer can evaluate 1000s
of different alternatives to find ones that you might never have thought of.
Yes, there are times where you can come up with a better plan for one
particular query at one particular time. However, once you decide to FORCE
the optimizer to do something, it becomes static. As your data changes, your
forced choice may no longer be the best, and may actually hurt performance.
Also, as the optimizer is upgraded to add new techniques, you'll never have
a chance to know if those new choices can make your queries run even faster.
I think better than forcing the optimizer, is learning how to encourage the
optimizer to do the right thing without having to use force. Having the
right indexes in place, writing your queries in the best way and keeping
your statistics up to date can go a long way. Yes, there are times you have
to use hints to force the optimizer's behavior; that's why there are hints
in the product. But they should be a last resort, not a first.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1144690845.075086.204670@.t31g2000cwb.googlegroups.com...
> hmmm. the optimizer is pretty smart, but you are smarter.
> rather then learning how the optimizer works, i spent time figuring out
> how I would like my queries processed, and how to encourage (FORCE!)
> the optimizer to do it the fastest way.
>|||Yes, I'm fan of not forcing the optimizer. I remember that I backed up the
database of a customer to bring it to the office for some query
optimizations. In a particulare SP I realized that merge join could be much
faster but optimizer did not chooose it at all. I forced merge join. It
became incredibly faster, When I took my changes to the customer's server,
the merge join was very slow. When I removed the merge join hint, it worked
better! The only difference between my computer and customer's, was the
hardware!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OddYysMXGHA.4704@.TK2MSFTNGP02.phx.gbl...
> Yes, people are smarter than computers, but computers are usually faster.
> Why is it that so few people can actually beat a computer at chess? The
> computer can evaluate millions of possible moves in less time than a human
> can physically move a pawn. So the SQL Server optimizer can evaluate 1000s
> of different alternatives to find ones that you might never have thought
> of.
> Yes, there are times where you can come up with a better plan for one
> particular query at one particular time. However, once you decide to FORCE
> the optimizer to do something, it becomes static. As your data changes,
> your forced choice may no longer be the best, and may actually hurt
> performance.
> Also, as the optimizer is upgraded to add new techniques, you'll never
> have a chance to know if those new choices can make your queries run even
> faster.
> I think better than forcing the optimizer, is learning how to encourage
> the optimizer to do the right thing without having to use force. Having
> the right indexes in place, writing your queries in the best way and
> keeping your statistics up to date can go a long way. Yes, there are times
> you have to use hints to force the optimizer's behavior; that's why there
> are hints in the product. But they should be a last resort, not a first.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Doug" <drmiller100@.hotmail.com> wrote in message
> news:1144690845.075086.204670@.t31g2000cwb.googlegroups.com...
>> hmmm. the optimizer is pretty smart, but you are smarter.
>> rather then learning how the optimizer works, i spent time figuring out
>> how I would like my queries processed, and how to encourage (FORCE!)
>> the optimizer to do it the fastest way.
>|||Even if the hardware is identical, there is as big difference between
your own test box and a production box dealing with dozens of
concurrent requests. The optimizer has no way of knowing what the
occurrence might be, but still it assumes some degree of concurrency
when it chooses the plan. For instance, a merge join might require a
sort - and that could be both CPU and memory hungry, causing
bottlenecks and contention for resources. So on an identical but busy
production server a merge join might run way slower.
Even if a hint seems to be useful at the time you make a hot fix to
your urgent problem at hand, it may start causing harm when the
situation has changed, such as:
- a new constraint
- a new index
- changed statistics
Do not use optimizer hints just because you can - they should be your
last resort. Use proper indexes, index covering and (sometimes only)
indexed views, and you will almost eliminate the need for hints
whatsoever.|||"Leila" <Leilas@.hotpop.com> wrote in message
news:uwlTOANXGHA.4924@.TK2MSFTNGP05.phx.gbl...
> Yes, I'm fan of not forcing the optimizer. I remember that I backed up the
> database of a customer to bring it to the office for some query
> optimizations. In a particulare SP I realized that merge join could be
much
> faster but optimizer did not chooose it at all. I forced merge join. It
> became incredibly faster, When I took my changes to the customer's server,
> the merge join was very slow. When I removed the merge join hint, it
worked
> better! The only difference between my computer and customer's, was the
> hardware!
I haved ONE experience where forcing a hint on a query was the "right"
thing. And that was simply due to a "bug" between SP3 and SP4 in SQL 2K.
As I couldn't upgrade to SP4 right away and w/o the hint, the query was
doing a table scan of a 45 gig table, I think it was the right call. Once
we moved to SP4 and confirmed that on that edition of SQL (Enterprise with
clustering vs. standard) the hint wasn't needed, it was removed. For the
reason you gave above, so that as conditions change, the optimizer can
adatp.
And yes, I agree with Kalen (big surprise there :-) that "encouraging" the
optimizeris generally the right thing.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OddYysMXGHA.4704@.TK2MSFTNGP02.phx.gbl...
> > Yes, people are smarter than computers, but computers are usually
faster.
> >
> > Why is it that so few people can actually beat a computer at chess? The
> > computer can evaluate millions of possible moves in less time than a
human
> > can physically move a pawn. So the SQL Server optimizer can evaluate
1000s
> > of different alternatives to find ones that you might never have thought
> > of.
> > Yes, there are times where you can come up with a better plan for one
> > particular query at one particular time. However, once you decide to
FORCE
> > the optimizer to do something, it becomes static. As your data changes,
> > your forced choice may no longer be the best, and may actually hurt
> > performance.
> > Also, as the optimizer is upgraded to add new techniques, you'll never
> > have a chance to know if those new choices can make your queries run
even
> > faster.
> >
> > I think better than forcing the optimizer, is learning how to encourage
> > the optimizer to do the right thing without having to use force. Having
> > the right indexes in place, writing your queries in the best way and
> > keeping your statistics up to date can go a long way. Yes, there are
times
> > you have to use hints to force the optimizer's behavior; that's why
there
> > are hints in the product. But they should be a last resort, not a first.
> >
> > --
> > HTH
> > Kalen Delaney, SQL Server MVP
> > www.solidqualitylearning.com
> >
> >
> > "Doug" <drmiller100@.hotmail.com> wrote in message
> > news:1144690845.075086.204670@.t31g2000cwb.googlegroups.com...
> >> hmmm. the optimizer is pretty smart, but you are smarter.
> >>
> >> rather then learning how the optimizer works, i spent time figuring out
> >> how I would like my queries processed, and how to encourage (FORCE!)
> >> the optimizer to do it the fastest way.
> >>
> >
> >
>|||Ok, fair enough.
When I write code, i write generic code without hints.
Almost always that works. Once in a while you run into an
"opportunity" where something isn't running as fast as someone would
like.
in those cases, i'm not afraid to go try to figure out how the data
should be pulled, using either existing indexes or new and needed
indexes.
in those cases, I'm not afraid to force the engine to do it my way.
Documentation obviously occurs.
If you "hint", sometimes something will happen and one day the engine
will choose a poor path based on statistics, the moon, or whatever.
I expressed myself poorly in my original post. My intent was something
along the lines of suggesting to Leila that rather then understanding
the goofy, weird, and strange processes that a given optimizer uses to
find its idea of optimal, you might be better served to think about the
way queries SHOULD be run.
My argument is along the lines that each optimizer, for each version
of SQL, for each data engine, for different settings, will behave
differently.
The best way to pull the data is however probably not that subjective,
and a human's better understanding of how the data REALLY works can
allow opportunties to drastically speed things up.
get a decent sized set of tables (terrabytes, or even gigabytes on old
hardware), and you will KNOW when you get it right.
regards,
doug|||I appreciate everybody. I enjoy discussing experiences with regard of query
optimization, but I would be most grateful if someone could write something
in answer to main question!!
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1144808205.118085.272490@.g10g2000cwb.googlegroups.com...
> Ok, fair enough.
> When I write code, i write generic code without hints.
> Almost always that works. Once in a while you run into an
> "opportunity" where something isn't running as fast as someone would
> like.
> in those cases, i'm not afraid to go try to figure out how the data
> should be pulled, using either existing indexes or new and needed
> indexes.
> in those cases, I'm not afraid to force the engine to do it my way.
> Documentation obviously occurs.
> If you "hint", sometimes something will happen and one day the engine
> will choose a poor path based on statistics, the moon, or whatever.
> I expressed myself poorly in my original post. My intent was something
> along the lines of suggesting to Leila that rather then understanding
> the goofy, weird, and strange processes that a given optimizer uses to
> find its idea of optimal, you might be better served to think about the
> way queries SHOULD be run.
> My argument is along the lines that each optimizer, for each version
> of SQL, for each data engine, for different settings, will behave
> differently.
> The best way to pull the data is however probably not that subjective,
> and a human's better understanding of how the data REALLY works can
> allow opportunties to drastically speed things up.
> get a decent sized set of tables (terrabytes, or even gigabytes on old
> hardware), and you will KNOW when you get it right.
> regards,
> doug
>
Showing posts with label enjoy. Show all posts
Showing posts with label enjoy. Show all posts
Friday, March 9, 2012
Query Optimizer
Hi,
I'm very interested in query optimization and I really enjoy this job. I
have read everything in BOL and also books like "Inside SQL Server 2000" and
"Guru's Guide to SQL Server Architecture and Internals" and "Microsoft SQL
Server 2000 Performance Optimization and Tuning Handbook".
But in new groups I read some information from MVPs that is not included in
any of mentioned resources. I'd like to know if there are other references
( like web sites, books ,...) that can give more information about this
Artificial Intelligence (Query Optimizer!).
Thanks in advance,
LeilaLeila,
SQL Server Performance Tuning Tips
http://www.sql-server-performance.c...performance.asp
Lot of info in the MVP's web site.
Erland Sommarskog's home page
http://www.sommarskog.se/
http://vyaskn.tripod.com/
http://www.aspfaq.com/
http://www.karaszi.com/SQLServer/default.asp
http://users.drew.edu/skass/sql/
AMB
"Leila" wrote:
> Hi,
> I'm very interested in query optimization and I really enjoy this job. I
> have read everything in BOL and also books like "Inside SQL Server 2000" a
nd
> "Guru's Guide to SQL Server Architecture and Internals" and "Microsoft SQL
> Server 2000 Performance Optimization and Tuning Handbook".
> But in new groups I read some information from MVPs that is not included i
n
> any of mentioned resources. I'd like to know if there are other references
> ( like web sites, books ,...) that can give more information about this
> Artificial Intelligence (Query Optimizer!).
> Thanks in advance,
> Leila
>
>|||hmmm. the optimizer is pretty smart, but you are smarter.
rather then learning how the optimizer works, i spent time figuring out
how I would like my queries processed, and how to encourage (FORCE!)
the optimizer to do it the fastest way.|||Yes, people are smarter than computers, but computers are usually faster.
Why is it that so few people can actually beat a computer at chess? The
computer can evaluate millions of possible moves in less time than a human
can physically move a pawn. So the SQL Server optimizer can evaluate 1000s
of different alternatives to find ones that you might never have thought of.
Yes, there are times where you can come up with a better plan for one
particular query at one particular time. However, once you decide to FORCE
the optimizer to do something, it becomes static. As your data changes, your
forced choice may no longer be the best, and may actually hurt performance.
Also, as the optimizer is upgraded to add new techniques, you'll never have
a chance to know if those new choices can make your queries run even faster.
I think better than forcing the optimizer, is learning how to encourage the
optimizer to do the right thing without having to use force. Having the
right indexes in place, writing your queries in the best way and keeping
your statistics up to date can go a long way. Yes, there are times you have
to use hints to force the optimizer's behavior; that's why there are hints
in the product. But they should be a last resort, not a first.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1144690845.075086.204670@.t31g2000cwb.googlegroups.com...
> hmmm. the optimizer is pretty smart, but you are smarter.
> rather then learning how the optimizer works, i spent time figuring out
> how I would like my queries processed, and how to encourage (FORCE!)
> the optimizer to do it the fastest way.
>|||Yes, I'm fan of not forcing the optimizer. I remember that I backed up the
database of a customer to bring it to the office for some query
optimizations. In a particulare SP I realized that merge join could be much
faster but optimizer did not chooose it at all. I forced merge join. It
became incredibly faster, When I took my changes to the customer's server,
the merge join was very slow. When I removed the merge join hint, it worked
better! The only difference between my computer and customer's, was the
hardware!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OddYysMXGHA.4704@.TK2MSFTNGP02.phx.gbl...
> Yes, people are smarter than computers, but computers are usually faster.
> Why is it that so few people can actually beat a computer at chess? The
> computer can evaluate millions of possible moves in less time than a human
> can physically move a pawn. So the SQL Server optimizer can evaluate 1000s
> of different alternatives to find ones that you might never have thought
> of.
> Yes, there are times where you can come up with a better plan for one
> particular query at one particular time. However, once you decide to FORCE
> the optimizer to do something, it becomes static. As your data changes,
> your forced choice may no longer be the best, and may actually hurt
> performance.
> Also, as the optimizer is upgraded to add new techniques, you'll never
> have a chance to know if those new choices can make your queries run even
> faster.
> I think better than forcing the optimizer, is learning how to encourage
> the optimizer to do the right thing without having to use force. Having
> the right indexes in place, writing your queries in the best way and
> keeping your statistics up to date can go a long way. Yes, there are times
> you have to use hints to force the optimizer's behavior; that's why there
> are hints in the product. But they should be a last resort, not a first.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Doug" <drmiller100@.hotmail.com> wrote in message
> news:1144690845.075086.204670@.t31g2000cwb.googlegroups.com...
>|||Even if the hardware is identical, there is as big difference between
your own test box and a production box dealing with dozens of
concurrent requests. The optimizer has no way of knowing what the
occurrence might be, but still it assumes some degree of concurrency
when it chooses the plan. For instance, a merge join might require a
sort - and that could be both CPU and memory hungry, causing
bottlenecks and contention for resources. So on an identical but busy
production server a merge join might run way slower.
Even if a hint seems to be useful at the time you make a hot fix to
your urgent problem at hand, it may start causing harm when the
situation has changed, such as:
- a new constraint
- a new index
- changed statistics
Do not use optimizer hints just because you can - they should be your
last resort. Use proper indexes, index covering and (sometimes only)
indexed views, and you will almost eliminate the need for hints
whatsoever.|||"Leila" <Leilas@.hotpop.com> wrote in message
news:uwlTOANXGHA.4924@.TK2MSFTNGP05.phx.gbl...
> Yes, I'm fan of not forcing the optimizer. I remember that I backed up the
> database of a customer to bring it to the office for some query
> optimizations. In a particulare SP I realized that merge join could be
much
> faster but optimizer did not chooose it at all. I forced merge join. It
> became incredibly faster, When I took my changes to the customer's server,
> the merge join was very slow. When I removed the merge join hint, it
worked
> better! The only difference between my computer and customer's, was the
> hardware!
I haved ONE experience where forcing a hint on a query was the "right"
thing. And that was simply due to a "bug" between SP3 and SP4 in SQL 2K.
As I couldn't upgrade to SP4 right away and w/o the hint, the query was
doing a table scan of a 45 gig table, I think it was the right call. Once
we moved to SP4 and confirmed that on that edition of SQL (Enterprise with
clustering vs. standard) the hint wasn't needed, it was removed. For the
reason you gave above, so that as conditions change, the optimizer can
adatp.
And yes, I agree with Kalen (big surprise there :-) that "encouraging" the
optimizeris generally the right thing.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OddYysMXGHA.4704@.TK2MSFTNGP02.phx.gbl...
faster.[vbcol=seagreen]
human[vbcol=seagreen]
1000s[vbcol=seagreen]
FORCE[vbcol=seagreen]
even[vbcol=seagreen]
times[vbcol=seagreen]
there[vbcol=seagreen]
>|||Ok, fair enough.
When I write code, i write generic code without hints.
Almost always that works. Once in a while you run into an
"opportunity" where something isn't running as fast as someone would
like.
in those cases, i'm not afraid to go try to figure out how the data
should be pulled, using either existing indexes or new and needed
indexes.
in those cases, I'm not afraid to force the engine to do it my way.
Documentation obviously occurs.
If you "hint", sometimes something will happen and one day the engine
will choose a poor path based on statistics, the moon, or whatever.
I expressed myself poorly in my original post. My intent was something
along the lines of suggesting to Leila that rather then understanding
the goofy, weird, and strange processes that a given optimizer uses to
find its idea of optimal, you might be better served to think about the
way queries SHOULD be run.
My argument is along the lines that each optimizer, for each version
of SQL, for each data engine, for different settings, will behave
differently.
The best way to pull the data is however probably not that subjective,
and a human's better understanding of how the data REALLY works can
allow opportunties to drastically speed things up.
get a decent sized set of tables (terrabytes, or even gigabytes on old
hardware), and you will KNOW when you get it right.
regards,
doug|||I appreciate everybody. I enjoy discussing experiences with regard of query
optimization, but I would be most grateful if someone could write something
in answer to main question!!
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1144808205.118085.272490@.g10g2000cwb.googlegroups.com...
> Ok, fair enough.
> When I write code, i write generic code without hints.
> Almost always that works. Once in a while you run into an
> "opportunity" where something isn't running as fast as someone would
> like.
> in those cases, i'm not afraid to go try to figure out how the data
> should be pulled, using either existing indexes or new and needed
> indexes.
> in those cases, I'm not afraid to force the engine to do it my way.
> Documentation obviously occurs.
> If you "hint", sometimes something will happen and one day the engine
> will choose a poor path based on statistics, the moon, or whatever.
> I expressed myself poorly in my original post. My intent was something
> along the lines of suggesting to Leila that rather then understanding
> the goofy, weird, and strange processes that a given optimizer uses to
> find its idea of optimal, you might be better served to think about the
> way queries SHOULD be run.
> My argument is along the lines that each optimizer, for each version
> of SQL, for each data engine, for different settings, will behave
> differently.
> The best way to pull the data is however probably not that subjective,
> and a human's better understanding of how the data REALLY works can
> allow opportunties to drastically speed things up.
> get a decent sized set of tables (terrabytes, or even gigabytes on old
> hardware), and you will KNOW when you get it right.
> regards,
> doug
>
I'm very interested in query optimization and I really enjoy this job. I
have read everything in BOL and also books like "Inside SQL Server 2000" and
"Guru's Guide to SQL Server Architecture and Internals" and "Microsoft SQL
Server 2000 Performance Optimization and Tuning Handbook".
But in new groups I read some information from MVPs that is not included in
any of mentioned resources. I'd like to know if there are other references
( like web sites, books ,...) that can give more information about this
Artificial Intelligence (Query Optimizer!).
Thanks in advance,
LeilaLeila,
SQL Server Performance Tuning Tips
http://www.sql-server-performance.c...performance.asp
Lot of info in the MVP's web site.
Erland Sommarskog's home page
http://www.sommarskog.se/
http://vyaskn.tripod.com/
http://www.aspfaq.com/
http://www.karaszi.com/SQLServer/default.asp
http://users.drew.edu/skass/sql/
AMB
"Leila" wrote:
> Hi,
> I'm very interested in query optimization and I really enjoy this job. I
> have read everything in BOL and also books like "Inside SQL Server 2000" a
nd
> "Guru's Guide to SQL Server Architecture and Internals" and "Microsoft SQL
> Server 2000 Performance Optimization and Tuning Handbook".
> But in new groups I read some information from MVPs that is not included i
n
> any of mentioned resources. I'd like to know if there are other references
> ( like web sites, books ,...) that can give more information about this
> Artificial Intelligence (Query Optimizer!).
> Thanks in advance,
> Leila
>
>|||hmmm. the optimizer is pretty smart, but you are smarter.
rather then learning how the optimizer works, i spent time figuring out
how I would like my queries processed, and how to encourage (FORCE!)
the optimizer to do it the fastest way.|||Yes, people are smarter than computers, but computers are usually faster.
Why is it that so few people can actually beat a computer at chess? The
computer can evaluate millions of possible moves in less time than a human
can physically move a pawn. So the SQL Server optimizer can evaluate 1000s
of different alternatives to find ones that you might never have thought of.
Yes, there are times where you can come up with a better plan for one
particular query at one particular time. However, once you decide to FORCE
the optimizer to do something, it becomes static. As your data changes, your
forced choice may no longer be the best, and may actually hurt performance.
Also, as the optimizer is upgraded to add new techniques, you'll never have
a chance to know if those new choices can make your queries run even faster.
I think better than forcing the optimizer, is learning how to encourage the
optimizer to do the right thing without having to use force. Having the
right indexes in place, writing your queries in the best way and keeping
your statistics up to date can go a long way. Yes, there are times you have
to use hints to force the optimizer's behavior; that's why there are hints
in the product. But they should be a last resort, not a first.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1144690845.075086.204670@.t31g2000cwb.googlegroups.com...
> hmmm. the optimizer is pretty smart, but you are smarter.
> rather then learning how the optimizer works, i spent time figuring out
> how I would like my queries processed, and how to encourage (FORCE!)
> the optimizer to do it the fastest way.
>|||Yes, I'm fan of not forcing the optimizer. I remember that I backed up the
database of a customer to bring it to the office for some query
optimizations. In a particulare SP I realized that merge join could be much
faster but optimizer did not chooose it at all. I forced merge join. It
became incredibly faster, When I took my changes to the customer's server,
the merge join was very slow. When I removed the merge join hint, it worked
better! The only difference between my computer and customer's, was the
hardware!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OddYysMXGHA.4704@.TK2MSFTNGP02.phx.gbl...
> Yes, people are smarter than computers, but computers are usually faster.
> Why is it that so few people can actually beat a computer at chess? The
> computer can evaluate millions of possible moves in less time than a human
> can physically move a pawn. So the SQL Server optimizer can evaluate 1000s
> of different alternatives to find ones that you might never have thought
> of.
> Yes, there are times where you can come up with a better plan for one
> particular query at one particular time. However, once you decide to FORCE
> the optimizer to do something, it becomes static. As your data changes,
> your forced choice may no longer be the best, and may actually hurt
> performance.
> Also, as the optimizer is upgraded to add new techniques, you'll never
> have a chance to know if those new choices can make your queries run even
> faster.
> I think better than forcing the optimizer, is learning how to encourage
> the optimizer to do the right thing without having to use force. Having
> the right indexes in place, writing your queries in the best way and
> keeping your statistics up to date can go a long way. Yes, there are times
> you have to use hints to force the optimizer's behavior; that's why there
> are hints in the product. But they should be a last resort, not a first.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Doug" <drmiller100@.hotmail.com> wrote in message
> news:1144690845.075086.204670@.t31g2000cwb.googlegroups.com...
>|||Even if the hardware is identical, there is as big difference between
your own test box and a production box dealing with dozens of
concurrent requests. The optimizer has no way of knowing what the
occurrence might be, but still it assumes some degree of concurrency
when it chooses the plan. For instance, a merge join might require a
sort - and that could be both CPU and memory hungry, causing
bottlenecks and contention for resources. So on an identical but busy
production server a merge join might run way slower.
Even if a hint seems to be useful at the time you make a hot fix to
your urgent problem at hand, it may start causing harm when the
situation has changed, such as:
- a new constraint
- a new index
- changed statistics
Do not use optimizer hints just because you can - they should be your
last resort. Use proper indexes, index covering and (sometimes only)
indexed views, and you will almost eliminate the need for hints
whatsoever.|||"Leila" <Leilas@.hotpop.com> wrote in message
news:uwlTOANXGHA.4924@.TK2MSFTNGP05.phx.gbl...
> Yes, I'm fan of not forcing the optimizer. I remember that I backed up the
> database of a customer to bring it to the office for some query
> optimizations. In a particulare SP I realized that merge join could be
much
> faster but optimizer did not chooose it at all. I forced merge join. It
> became incredibly faster, When I took my changes to the customer's server,
> the merge join was very slow. When I removed the merge join hint, it
worked
> better! The only difference between my computer and customer's, was the
> hardware!
I haved ONE experience where forcing a hint on a query was the "right"
thing. And that was simply due to a "bug" between SP3 and SP4 in SQL 2K.
As I couldn't upgrade to SP4 right away and w/o the hint, the query was
doing a table scan of a 45 gig table, I think it was the right call. Once
we moved to SP4 and confirmed that on that edition of SQL (Enterprise with
clustering vs. standard) the hint wasn't needed, it was removed. For the
reason you gave above, so that as conditions change, the optimizer can
adatp.
And yes, I agree with Kalen (big surprise there :-) that "encouraging" the
optimizeris generally the right thing.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OddYysMXGHA.4704@.TK2MSFTNGP02.phx.gbl...
faster.[vbcol=seagreen]
human[vbcol=seagreen]
1000s[vbcol=seagreen]
FORCE[vbcol=seagreen]
even[vbcol=seagreen]
times[vbcol=seagreen]
there[vbcol=seagreen]
>|||Ok, fair enough.
When I write code, i write generic code without hints.
Almost always that works. Once in a while you run into an
"opportunity" where something isn't running as fast as someone would
like.
in those cases, i'm not afraid to go try to figure out how the data
should be pulled, using either existing indexes or new and needed
indexes.
in those cases, I'm not afraid to force the engine to do it my way.
Documentation obviously occurs.
If you "hint", sometimes something will happen and one day the engine
will choose a poor path based on statistics, the moon, or whatever.
I expressed myself poorly in my original post. My intent was something
along the lines of suggesting to Leila that rather then understanding
the goofy, weird, and strange processes that a given optimizer uses to
find its idea of optimal, you might be better served to think about the
way queries SHOULD be run.
My argument is along the lines that each optimizer, for each version
of SQL, for each data engine, for different settings, will behave
differently.
The best way to pull the data is however probably not that subjective,
and a human's better understanding of how the data REALLY works can
allow opportunties to drastically speed things up.
get a decent sized set of tables (terrabytes, or even gigabytes on old
hardware), and you will KNOW when you get it right.
regards,
doug|||I appreciate everybody. I enjoy discussing experiences with regard of query
optimization, but I would be most grateful if someone could write something
in answer to main question!!
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1144808205.118085.272490@.g10g2000cwb.googlegroups.com...
> Ok, fair enough.
> When I write code, i write generic code without hints.
> Almost always that works. Once in a while you run into an
> "opportunity" where something isn't running as fast as someone would
> like.
> in those cases, i'm not afraid to go try to figure out how the data
> should be pulled, using either existing indexes or new and needed
> indexes.
> in those cases, I'm not afraid to force the engine to do it my way.
> Documentation obviously occurs.
> If you "hint", sometimes something will happen and one day the engine
> will choose a poor path based on statistics, the moon, or whatever.
> I expressed myself poorly in my original post. My intent was something
> along the lines of suggesting to Leila that rather then understanding
> the goofy, weird, and strange processes that a given optimizer uses to
> find its idea of optimal, you might be better served to think about the
> way queries SHOULD be run.
> My argument is along the lines that each optimizer, for each version
> of SQL, for each data engine, for different settings, will behave
> differently.
> The best way to pull the data is however probably not that subjective,
> and a human's better understanding of how the data REALLY works can
> allow opportunties to drastically speed things up.
> get a decent sized set of tables (terrabytes, or even gigabytes on old
> hardware), and you will KNOW when you get it right.
> regards,
> doug
>
Query Optimizer
Hi,
I'm very interested in query optimization and I really enjoy this job. I
have read everything in BOL and also books like "Inside SQL Server 2000" and
"Guru's Guide to SQL Server Architecture and Internals" and "Microsoft SQL
Server 2000 Performance Optimization and Tuning Handbook".
But in new groups I read some information from MVPs that is not included in
any of mentioned resources. I'd like to know if there are other references
( like web sites, books ,...) that can give more information about this
Artificial Intelligence (Query Optimizer!).
Thanks in advance,
LeilaLeila,
SQL Server Performance Tuning Tips
http://www.sql-server-performance.c...performance.asp
Lot of info in the MVP's web site.
Erland Sommarskog's home page
http://www.sommarskog.se/
http://vyaskn.tripod.com/
http://www.aspfaq.com/
http://www.karaszi.com/SQLServer/default.asp
http://users.drew.edu/skass/sql/
AMB
"Leila" wrote:
> Hi,
> I'm very interested in query optimization and I really enjoy this job. I
> have read everything in BOL and also books like "Inside SQL Server 2000" a
nd
> "Guru's Guide to SQL Server Architecture and Internals" and "Microsoft SQL
> Server 2000 Performance Optimization and Tuning Handbook".
> But in new groups I read some information from MVPs that is not included i
n
> any of mentioned resources. I'd like to know if there are other references
> ( like web sites, books ,...) that can give more information about this
> Artificial Intelligence (Query Optimizer!).
> Thanks in advance,
> Leila
>
>
I'm very interested in query optimization and I really enjoy this job. I
have read everything in BOL and also books like "Inside SQL Server 2000" and
"Guru's Guide to SQL Server Architecture and Internals" and "Microsoft SQL
Server 2000 Performance Optimization and Tuning Handbook".
But in new groups I read some information from MVPs that is not included in
any of mentioned resources. I'd like to know if there are other references
( like web sites, books ,...) that can give more information about this
Artificial Intelligence (Query Optimizer!).
Thanks in advance,
LeilaLeila,
SQL Server Performance Tuning Tips
http://www.sql-server-performance.c...performance.asp
Lot of info in the MVP's web site.
Erland Sommarskog's home page
http://www.sommarskog.se/
http://vyaskn.tripod.com/
http://www.aspfaq.com/
http://www.karaszi.com/SQLServer/default.asp
http://users.drew.edu/skass/sql/
AMB
"Leila" wrote:
> Hi,
> I'm very interested in query optimization and I really enjoy this job. I
> have read everything in BOL and also books like "Inside SQL Server 2000" a
nd
> "Guru's Guide to SQL Server Architecture and Internals" and "Microsoft SQL
> Server 2000 Performance Optimization and Tuning Handbook".
> But in new groups I read some information from MVPs that is not included i
n
> any of mentioned resources. I'd like to know if there are other references
> ( like web sites, books ,...) that can give more information about this
> Artificial Intelligence (Query Optimizer!).
> Thanks in advance,
> Leila
>
>
Subscribe to:
Posts (Atom)