It depends on the specific application. for competition, I suspect it is NOT a fad or unnecessary if you want to win. For daily or combat use, its still quite expensive, and still has issues like the lens fogging, breaking, or getting obscured with water or dirt.
I agree with you on fogging and obscuration, the former more if you are going in and out of areas with notably different levels of humidity and temperature (I’ve talked to officers from the south that mentioned fogging being such a problem that they actually put a cloth over the lens of the red dot when riding in a car with the AC on). For obscuration, in the event the lens is completely blocked you can use the housing itself as a rough aiming device. It is in no way ideal, but it is an option.
The durability is actually surprisingly good in a number of cases. Aaron Cowan of Sage Dynamics has become noted in the red dot “community” for relatively harsh testing of red dots. His baseline for a duty optic is 2000 rd fired and four shoulder height drops of a pistol with red dot and the optic impacting concrete, asphalt, or some other hard surface. A number of them have done very well, some with minor cracks of the lens that still allow use of the red dot. Others have failed to the point of the lens shattering. He has one RMR with 20,000 rd fired, 40 shoulder height drop tests, and no breakages and no shift in zero.
https://youtu.be/2yzpJBrLU5U
You mentioned cost. RMRs aren’t overly cheap (most of the ones I’ve bought were ~$420). Is it worth it? He has also tested Holosun optics in the range of $200-$300 that have also held up very well. Now they’re made in China, and many may not like that, but if a person has a limited budget, it’s an option (I have a number of them myself, in full disclosure).
I have done a lot of full day shooting courses with pistols with red dots, over a dozen now. I have seen surprisingly few failures, and the ones I have seen were almost all users who didn’t torque the optic properly and apply some form of Loctite (there is a learning curve here). The worst experience I had with a red dot was a two day pistol course with Kyle Lamb, where it rained heavily the second half of the day. This taught me the value of an enclosed red dot, and the value for officers of a duty holster that has a moveable cover for the optic (Safariland 6390RDS was what I was using). Now up close it was still usable, but at distance our dots were scattered notably and even using irons through the lens became hard as both sides of the lens were covered in rain drops. For someone carrying concealed drawing the pistol from under a garment and likely firing for a matter of seconds it may not be an issue, but there are real environmental limitations that need to be considered.