3 ms·

On a somewhat related note... We've moved to MSSQL due to several reasons including customer demand. We're experiencing the MSSQL query planner occasionally g

On a somewhat related note... We've moved to MSSQL due to several reasons including customer demand. We're experiencing the MSSQL query planner occasionally generating terrible plans for core queries which then gets cached, leading to support calls. Workaround for now has been to have our query geneator append a fixed-value column which and have it change the value every 10 minutes, as a cache defeat. Still, surprised the engine doesn't figure this out itself, like try regenerating plans frequently if they contain non-trivial table scans say. Or just expire cache entries every 15 minutes or so, so a bad plan doesn't stick around for too long.

baq 2y ago
It’s one of RDBMS peculiarities that you just need to learn about. Any half decent MSSQL course or book will tell you about the plan cache and the occasional gotcha exactly like yours. Time wasted debugging this issue by the unaware is probably in kilo man years by now. …but MSSQL is still a fantastic database, if you can afford it. Postgres and mysql come with their own set of gotchas, some of which need an actually decent book to be explained. (Note all the RDBMS manuals are decent books and everyone without exception should read at least the TOC of the db they’re using, which IME is still a rarity.)
- magicalhippo 2y ago
  My point was more that I feel it's a discrepancy that the DB tries to be very clever, but seemingly doesn't self-evaluate its decisions enough to determine when it wasn't. Why should I have to guard against poor optimization decisions when the whole point is to just let the DB figure out the best way?
  - baq 2y ago
    Because the point isn't that the DB should figure everything out, as it is a baseless claim that it's a 'poor optimization decision'. The point is to allow you to meet your requirements within your constraints. You're trading some of your constraints for different ones (here - you don't want to implement your own database, so you're using one that's already built with its own requirements and constraints). A database is a database, not a holy grail. I haven't used MSSQL for some time now, but I'm sure there are tools which do exactly what you say you want, i.e. continuously monitor the DBMS and suggest actions or even act itself. This is usually done by a team of DBAs, though.
rotis 2y ago
To add to sibling comment. This sounds like a parameter sniffing. Read it up. It should help you understand the problem. It is a known issue with SQL Server. Or rather a tradeoff. You don't want to compile a new execution plan for every query. This would eat up CPU by itself.
- magicalhippo 2y ago
  Yeah seems likely but haven't had time to really dig into it. For now our workaround works. So my point was that if they want to keep parameter sniffing as default then short cache expiration can at the very least mitigate the performance cliffs that can occur. Better to have a slow query for 10-15 minutes than for hours. Recompiling once in a while shouldn't have a significant performance impact. In general though I think in many cases Carmack's approach when he made Quake's renderer is a good one. That is, it's often better to use an algorithm which is on average slightly slower but has much lower variance in execution time, than one which is on average slightly faster but has much greater variance. Parameter sniffing seems to fall into the latter category.

baq 2y ago

It’s one of RDBMS peculiarities that you just need to learn about. Any half decent MSSQL course or book will tell you about the plan cache and the occasional gotcha exactly like yours. Time wasted debugging this issue by the unaware is probably in kilo man years by now. …but MSSQL is still a fantastic database, if you can afford it. Postgres and mysql come with their own set of gotchas, some of which need an actually decent book to be explained. (Note all the RDBMS manuals are decent books and everyone without exception should read at least the TOC of the db they’re using, which IME is still a rarity.)

magicalhippo 2y ago
My point was more that I feel it's a discrepancy that the DB tries to be very clever, but seemingly doesn't self-evaluate its decisions enough to determine when it wasn't. Why should I have to guard against poor optimization decisions when the whole point is to just let the DB figure out the best way?
- baq 2y ago
  Because the point isn't that the DB should figure everything out, as it is a baseless claim that it's a 'poor optimization decision'. The point is to allow you to meet your requirements within your constraints. You're trading some of your constraints for different ones (here - you don't want to implement your own database, so you're using one that's already built with its own requirements and constraints). A database is a database, not a holy grail. I haven't used MSSQL for some time now, but I'm sure there are tools which do exactly what you say you want, i.e. continuously monitor the DBMS and suggest actions or even act itself. This is usually done by a team of DBAs, though.

rotis 2y ago

To add to sibling comment. This sounds like a parameter sniffing. Read it up. It should help you understand the problem. It is a known issue with SQL Server. Or rather a tradeoff. You don't want to compile a new execution plan for every query. This would eat up CPU by itself.

magicalhippo 2y ago
Yeah seems likely but haven't had time to really dig into it. For now our workaround works. So my point was that if they want to keep parameter sniffing as default then short cache expiration can at the very least mitigate the performance cliffs that can occur. Better to have a slow query for 10-15 minutes than for hours. Recompiling once in a while shouldn't have a significant performance impact. In general though I think in many cases Carmack's approach when he made Quake's renderer is a good one. That is, it's often better to use an algorithm which is on average slightly slower but has much lower variance in execution time, than one which is on average slightly faster but has much greater variance. Parameter sniffing seems to fall into the latter category.