From my experience playing a necromancer, I actually found the Animate Dead and Raise Undead spells to be not very helpful. The undead are only really useful as cannon fodder or a meat shield for a couple rounds against stronger enemies, but their casting time makes it so the only way to use them is if you already know a fight is coming. If I have time to prepare for a fight, I probably wouldn't be in a position where I need a meat shield in the first place.
True Strike, Blade Ward, and Witch Bolt do all have occasional circumstances when they could be useful. (The first two if you can cast them just before a fight starts, for instance, and WB if you're in a long fight but down to your last spell slot.)
Mord's Sword though is almost strictly worse than an upcast flaming sphere. So that is a pretty good candidate... it's only job is to do damage and it does very badly at that.
Mending is in theory kind of useful but I have _never_ seen a situation come up in play where you'd cast it.
A bunch of spells seem to be in the game for NPCs to use, more often than PCs: animal messenger, dream, hold portal, magic mouth, programmed illusion,... I wouldn't say those are terrible, but I guess not many players would prepare them.
Yeah, if I had a ring of three wishes, I would probably just toss it. Truly though, I like that it is much less costly now, but I agree that some of the effects listed probably shouldn't risk permanently losing the spell. XP costs are dumb enough that I'm happier with this version though.
I would second @Gladius Legis on the list provided with the slight disagreement that Blade Ward--though niche-- has its uses.
In short there are two ways to measure a spell's effectiveness: how well it matches the fiction and lives up to the expectations in the description, and how well the mechanical implementation works along with the system mastery required to effectively use it. For example, few players, even newbies, would expect Illusory Script to be an dungeon pawning, knock-down-orcus type of spell. Mechanically, the spell largely delivers what it says on the tin, and is a somewhat niche utility spell that may come up in certain types of campaigns, but not one an erstwhile dungeon delver is likely to take to keep them and their companions alive while adventuring. As such, I don't really have a problem with it (save for maybe having the duration be longer for ancient treasure maps or some such story driven thing).
Spells like Mordenkainen's Sword, on the other hand, deliver quite a bit different experience: the high spell level, combined with concentration and action cost to use it, deliver quite poor results.
To add to the list, I think Friends has some serious design issues. Barkskin is rather poorly designed/explained. The Concentration requirement for Flame Blade & Hunter's Mark is a bit much, Ray of Enfeeblement is, well, feeble. Bestow Curse could probably stand to apply several conditions from the list offered rather than one and Phantasmal Killer is a poor man's Fear spell one level higher that requires two saves to start infliction not-that great damage for its level. The ninth level Weird is mere the mass version of the same, and even more not worth it at that level. Crown of Madness is, barring very niche circumstances, a one round spell masquerading as a multi-round spell with a lot of restrictions. Contagion is worded very differently from their 'explanation' on Sage Advice and could use a re-write. Higher level Necromancy spells probably need a condition on a failed save (exhaustion, reduce max hp, etc) to go with their low damage.
There are probably more that others would add to the list, and it is not possible to have a perfectly designed/balanced spell list (however one defines that), but these are some of the spells that stand out.
[MENTION=61932]Barolo[/MENTION] gave me a build for a tome pact warlock with Sentinel, Warcaster plus Shillelagh, Green Flame Blade, and Blade Ward. The latter effectively doubles your temp hp with Armor of Agathys, so you just spam Blade Ward and get in some grills with a buddy, forcing the DM into some no-win choices. Super fun.
I can't agree more, I started this thread because I was ordering wizard spells by usefulness for determining how much I would pay for an opportunity to scribe them. Witch bolt was, in fact, last on the list for its level. The only time I would even consider preparing it is if I already knew I was going into a fight where my main plan was to Contagion: Slimy Doom (already an unreliable plan that won't see much use) against an enemy with AC and saves good enough that the entire party has difficult dealing damage to proc the stun. And even then Magic Missile will probably get the job done better, but at the cost of more spell slots. I can't even agree with jaelis' statement that it would be useful to conserve spell slots, because cantrips don't take slots and hit harder except very early on.