IMO they are mostly a complete and utter waste of time and money - and I do have experience of dealing with "blown up" equipment.
However, where I've experienced problems, it's pretty well always been obvious that the problem would
NOT have been mitigated at all by an SPD in the mains as it's all been differential surges between separate services (typically between phone lines and mains) or in one significant event earth differentials across a site.
Exhibit A (early 90s) - laptop brought back from Madagascar. Customer living part of the time in the UK, but at the time working in Madagascar. Suffered major lightning strike to one or both of phone and mains. Phone connector on modem board had some heat distortion to the plastic, chips "blown to bits", laptop itself blown, power brick blown open. Customer reports that table lamp completely unaffected, as was the standard (not mains powered) phone.
Exhibit B (late 90s) - my boss at the time lived in a rather nice house in the country. After a good thunderstorm, anything connected between phone and mains (fax, answering machine) blown, nothing else damaged.
Exhibit C (mid 90s) - shortly before I started work at company in exhibit B but I was doing their IT. There was (for this area) an unusually powerful thunderstorm, and customer phoned to say there'd been a loud bang, the computers were down, and there was a "burning smell" from the computer room. From the description I reckon they had a ground strike a few hundred yards away in the fields. At the time we had something in the order of 40-50 devices connected to one server over RS232 serial lines - these were a mix of printers, "green screen" terminals, Macs, and PCs. Basically, the further away from the main server, the more extensive the damage - many devices close to it were not damaged at all, others in the building lost their serial ports, for some in the other building (but sharing the same earth and substation which supplied only that site) were more badly damaged. The central server came off worst as it had multiple paths feeding the surge into it. This was a classic ground differential problem which an SPD would not have helped with.
Now
IFF you fit an SPD
AND you take every other service via that point and through a surge protector sharing the same common "ground" point, then and only then would an SPD actually do much good in most situations. In other situations it could actually make things worse
As for equipment getting more sensitive, well that's sort of true and sort of a red herring. It's only true in as much as switch mode power supplies are much less forgiving of surges than the old fashioned transformer rectifier types. But what
should be happening is equipment getting less easily damaged - it's not like lightning is an unknown phenomenon. Unfortunately, I think it's more a case of equipment design getting crapper - especially the "Chinese Export" carp with all cost (and standards conformance) designed out of it.