4 ms·

Not that I disagree with your point, but the opposite of > Also having the optimizer decide to do something bad off hours is not a good situation again hints o

by remus 5y ago

Not that I disagree with your point, but the opposite of > Also having the optimizer decide to do something bad off hours is not a good situation again hints or plan locking would help here. Can also happen when you force a particular query plan, only for it to turn to treacle when some assumption you made about the data suddenly breaks. As with most things there's no free lunch.

lazide 5y ago
When doing disaster planning, at least something blowing up because of a data change is preventable or mitigateable. Staging environments. Rollbacks, etc. The system randomly deciding to drive itself off a cliff for no reason, with no known way to stop it next time is quite concerning.
- Arnavion 5y ago
  And if you do find your query has turned to treacle, looking for whether you used a wrong query hint is already in your checklist, because it's right there in the query that you're already debugging. You can't forget to double-check it.
grogers 5y ago
In practice what happens in this case is that the query slows down over time, often days or weeks after deploying the thing that makes the query worse. It takes time to fill up the data that slows things down. This gives you plenty of time to monitor and take action. If it does happen more quickly than that (e.g. if a table starts getting filled for the first time, statistics can be off) then rolling back is easy. But if the query planner decides to screw up your query plan it happens instantaneously and there's no possibility of rollback - only emergency deploying code fixes to try to tweak the query. In most OLTP use cases, you almost always know exactly how a query is supposed to execute, so always using index hints is totally reasonable to prevent bad query plans. Basically, you breaking your own query with bad hints usually breaks things a lot less and at better times than the query planner doing it, and is usually easier to fix too.
- sagarm 5y ago
  Slow degradation is a common case, but a day with bad skew due to spam or other unusual activity can cause a previously optimal query plan to never complete.

by remus 5y ago

lazide 5y ago

When doing disaster planning, at least something blowing up because of a data change is preventable or mitigateable. Staging environments. Rollbacks, etc. The system randomly deciding to drive itself off a cliff for no reason, with no known way to stop it next time is quite concerning.

Arnavion 5y ago
And if you do find your query has turned to treacle, looking for whether you used a wrong query hint is already in your checklist, because it's right there in the query that you're already debugging. You can't forget to double-check it.

grogers 5y ago

In practice what happens in this case is that the query slows down over time, often days or weeks after deploying the thing that makes the query worse. It takes time to fill up the data that slows things down. This gives you plenty of time to monitor and take action. If it does happen more quickly than that (e.g. if a table starts getting filled for the first time, statistics can be off) then rolling back is easy. But if the query planner decides to screw up your query plan it happens instantaneously and there's no possibility of rollback - only emergency deploying code fixes to try to tweak the query. In most OLTP use cases, you almost always know exactly how a query is supposed to execute, so always using index hints is totally reasonable to prevent bad query plans. Basically, you breaking your own query with bad hints usually breaks things a lot less and at better times than the query planner doing it, and is usually easier to fix too.

sagarm 5y ago
Slow degradation is a common case, but a day with bad skew due to spam or other unusual activity can cause a previously optimal query plan to never complete.