A well-written article about a knotty problem and a naughty design for an RDBMS. I am afraid the article's "let the DBA fix it" comments will earn this article a low rating - I will not contribute to that as a rating, but I will explain the low rating (from a DBA's viewpoint):
I have almost 1000 databases (one per customer, across multiple environments) using a similar "Item" and "ItemSetting" EAV design. As a twist (of the EAV knife), "Item" includes a PrimaryID and a SecondaryID relationships as self-references. Apart from one or two Item Settings that will be present per active system user, most are singular to absent (depending upon the features a customer wants to use). I typically see approximately 100 different query hashes used in approximately 1000 different execution plans, per database. That's about a million execution plans to ponder. Parameter sniffing issues are common, but the number of plan guides I can create are finite, and many plan guides become obsolete in the next release. Now that we implemented "microservices", the issues are compounded ("Who's on First? What's on Second? IDunno's on Third!"), ASYNC_NETWORK_IO waits have spiked, and we have doubled the sizes of app and web servers (as a partial mitigation for that wait). All because some thought the database's %_Settings tables were "too many and too confusing". There was, and still is, no dictionary for the developers, and two different ORMs are in use.
Meanwhile, a coworker DBA (or me or another, when he is out of office) spends half their day manually running scripts designed "customize" ItemSettings. When ItemSettings are "missing", the DBAs say "that's an application problem". But what the DBAs say falls on the deaf ears of Agile development and a need to keep production up. We only have 100 customers in production, and our customer base has grown 10-fold in the past 3 years. We plan on hiring a junior DBA. Given our growth rate, that junior DBA will do nothing but "fix" ItemSettings, full time, with the next 2 years.
This doesn't scale. There are rumblings about somewhat trashing the Item/ItemSettings EAV design. I and my DBA coworkers already know our skills are squandered on the trash heap of an EAV design. I expect our next move will be towards a hierarchical DBMS (away from SQL Server). That for me is not a pleasant thought, with the saving grace being that our business may survive.