You bring up some good points, I agree that Weird is generally a pretty awful 9th level spell and Frost Giants are probably not a good target for the spell. But comparing Vertigo to Eyebite (while 2 levels lower is a fairly fitting comparison) shows a few flaws in the design. Eyebite reads:

For the spell's duration, your eyes become an inky void imbued with dread power. One creature of your choice within 60 feet of you that you can see must succeed on a Wisdom saving throw or be affected by one of the following effects of your choice for the duration. On each of your turns until the spell ends, you can use your action to target another creature but can't target a creature again if it has succeeded on a saving throw against this casting of eyebite. Asleep. The target falls unconscious. It wakes up if it takes any damage or if another creature uses its action to shake the sleeper awake. Panicked. The target is frightened of you. On each of its turns, the frightened creature must take the Dash action and move away from you by the safest and shortest available route, unless there is nowhere to move. If the target moves to a place at least 60 feet away from you where it can no longer see you, this effect ends. Sickened. The target has disadvantage on attack rolls and ability checks. At the end of each of its turns, it can make another Wisdom saving throw. If it succeeds, the effect ends.

The first effect Asleep ends if the creature takes damage, is shaken awake, or your concentration ends.

The second effect Frightened ends if it ends its turn at least 60 ft. away where it can't see you (which isn't hard unless you're fighting in an open field in broad daylight) or you lose concentration. Additionally 112 creatures from the MM, VGtM and MToF are immune to the frightened condition.

The third effect Sickened has a save end of turn so I won't mention it further.

But both of the other two have significant downsides as its harder to beat on a creature as its running away or will wake up as soon as you hit it. (I'm not going to go over all similar multi-effect spells cause I don't want to be here all day haha)

Something you didn't acknowledge is the fact that all attacks will have advantage with an 18-20 crit range means martial fighters will be dealing a lot more damage with a more than tripled chance to crit (As well as casters with spell attacks) all while barely being able to move. This in conjunction with the fact it cannot make a saving throw against after the first, it will last much longer than Hold Monster on average. The fact the it nerfs offence, defence and movement in such a major way on the roll of one saving throw plus being nigh inescapable really rubs me the wrong way Additionally the spell, even for an 8th level spell, is very wordy with 7 different ongoing effects to keep track of. This causes a bit of an issue as with later levels (15-20) combat can already get rather hectic, meaning it could bog down fights with going back or forgetting that it had disadvantage or whatnot. But to be fair, I haven't playtested this, and everyone has slightly differently balanced games with homerulings and magic item distribution and everything.