A lot of professionals say that "no one person can know everything about SQL Server", which sounds like a wonderful challenge to take up. While it may be a number of decades before I can claim to know "everything" about today's current version of Microsoft's powerful database engine, there are some things that I'd like to really understand. Temporal tables and in-memory OLTP are two items that I believe would be incredibly useful at the day job but, more importantly, I'd like to gain a deep understanding of how SQL Server uses the hardware its installed on. By looking at the specifications of the storage mediums and other system components, can a person mathematically calculate a server's actual limitations?
The answer, as with anything involving technology, will be "it depends", but with a solid foundation in the underlying subsystem, it should be possible to answer hard questions before throwing money at ill-understood problems.