First, the principles of the matter:
Cops do not sign up, or take an oath, to put zero value on their life. They do take an oath that involves risking their lives, that involves taking a chance at injury or death in order to uphold the peace. That is not the same as putting the suspect's life above their own.
Police have a duty to make every reasonable effort to bring suspects in alive, and to take reasonable risks to do so. Some cops of their own volition go above and beyond the reasonable in the order to fulfill that duty, and are rightly lauded as heroes for doing so. Some cops decide that they don't need to take any chance whatsoever, and will use lethal force at the first excuse; such are rightly seen as incompetent at best, villains more likely, and should be drummed out of the force or put in prison.
But if you start expecting every officer to put the life of the suspect, not the life of victims/potential victims, but the life of the suspect above their own, you are very soon not going to have much of a police force left, and what is left will be comprised of nothing but liars and the mentally unhinged.
It is perfectly acceptable for cops to take reasonable measures to minimize risks to themselves, but there is a boundary where that becomes unreasonable, and they're instead putting overly-large risks onto suspects or even victims instead.
Now, as to no-knock warrants in particular, does anybody actually have statistics on these they can trot out? Or is it just the sensational screw-ups we're going off of? Because as terrible as they are, that's not sufficient to remove the use altogether.
At the least, I'd say the evidentiary standards required to give a no-knock warrant should be much higher than those required for a regular warrant. IE, you have to have enough evidence to convict on some charges already, not you're trying to get evidence to convict, instead you're trying to get evidence of further crimes you suspect. Something along those lines.
Even then, such may not be worth the dangers they present. In fact, I lean towards that by default, knowing how I personally would respond to people kicking in my door with no warning, but I'd like to see some actual statistics on how they play out, especially in comparison to regular warrants.