[lmwnshn]

> less impactful now with Sql Server 2019

Could I ask what your process is for detecting whether a rewrite rule is still useful in subsequent versions of the DBMS? Do you read the release notes and test things out manually, or do you have an automated A/B test thing going on?

Additionally, have you ever had a rewrite rule change from being beneficial to being detrimental after upgrading versions? If so, how did you detect that?

Thanks!

Edit: Also, what considerations do you have for rewrite rule order? Do you find that it makes a significant difference in practice?

[i_s]

To check if rewrite rules are still helpful, yea - we just read release notes and test manually.

We've never had a rule change from being beneficial to detrimental. For most of them, I don't think that would be possible, because they just involve giving Sql less irrelevant things to chew on. For a few of them, like the manual scalar function inlining, I could see that being possible, so we will just need to keep checking.

For rewrite rule order, I guess we just do the ones that can enable other optimizations first. So far, that has been pretty simple to determine. For example, when we trim joins from inner queries, we first trim their selections (depending on what is actually used in the outer queries).

[zasdffaa]

> We've never had a rule change from being beneficial to detrimental

Whatever you're doing is really confusing me (see my prev post). A multi-thousand line piece of the SQL I wrote was tested on mssql 2019 and there was a blatant perf. fuckup from it's previous home on mssql 2016 (or was it 2014). Poss. down to the new cardinality estimator, I dunno.

> For example, when we trim joins from inner queries, we first trim their selections (depending on what is actually used in the outer queries).

If you looked at the query plans you will see this happens automatically. And very reliably because it is easy (indeed, quite straightforward) to do automatically.

Also... 'we just read release notes' - these optimisations are not documented (except maybe in one place which they carefully undocumented after the 1st release) because these are trade secrets. The optimiser is one of the most important and carefully guarded parts of mssql - it's not in the notes and never will be.

No offence but... seriously, wat???

[lmwnshn]

Thanks! Super interesting.