I had your dilemma 10 years ago, and almost went for coarser sand, but I knew I'd eventually want sand sleeping Wrasses. I went with Aragamax Select grain size 0.5mm-1.5mm and have used it ever since.
It's a pain with blowing around with new tanks and positioning wave makers/powerheads, but once you have things figured out it's good.
Don't point flow at it and it's all good.
I don't vacuum and don't need to, but I do occasionally scoop out dirty spots and wash it before placing back in the tank.
If you're looking for sand that won't blow around, pick something with at least a grain size of 2mm. Larger is better, but keep in mind most burrowing fish/critters won't be happy with it.