Back in the 90’s one of the credit reference agency’s Experien or Equifax here in the UK was using the Clarion dat file to service the country, so Mon-Sat only lookups, and all credit file updates were performed on a Sunday, but like you say, more indexes = faster lookups but slower inserts & updates.
Of course, thats why I tend to post the manufacturer links where possible, like the MS links above, but their documentation isnt always the best, I’ve been known to post links on here to MS Win32 api’s with mistakes.
But I’m also looking at this from a hardware perspective and there is the user base to take into account as well, like number of users, central DB with local and remote users, distributed replicated db’s on multiple sites.
Even the raid configuration can cause differences of opinions.
SSD RAID Load Testing Results from a Dell PowerEdge R720 - Brent Ozar Unlimited®
Some experts claim Raid10 is the best way to go but Brent didnt see it in the link above, Raid 5 was faster for reading.
There are lots of variables to factor in which can alter the SQL performance, and back when it was spin disks, even the placement of partitions like physically on the outside of the disk rather than the inside of the spin disk saw greater performance, which was what MS noticed and implemented back in the XP days, so prefetch data was stored in the outer parts of the spin disk, because they were a lot lot slower back then, and SSD’s et al now change all of that.
What is app launch prefetching and how does it help? - TechNet Articles - United States (English) - TechNet Wiki (microsoft.com)
Edit. I will add, in the early 00’s giving an SQL server more RAM outperformed what the type of index was in my experience, but that was SQL 2000 and SQL 2005 so things might have changed a bit since then.
Edit2 Also in the early 00’s I was using these quad port nics Intel PRO/1000 MT Server Adapter, pre rack server days with decent managed switches as this also improved performance in busy environments because networks are massive bottlenecks. I went with the Intel quad ports because I’d read they worked direct with the Intel cpu’s, unlike some other nic’s. Obviously rack server’s tend to have quad port nics as standard now a days, but if someone is still using standalone box server’s, getting a quad port nic seriously improves performance with some decent switches and decent cat 6 shielded (FTP) cables. And these are visible performance increases imo, compared to default sql server installations, but again that depends on the type of work the SQL server is doing.