I use SMALL magnum primers when they are explicitly called for(notably with w296/H110), but otherwise don't worry to much about them. I'm getting ready to start playing with AA #9, and I think it generally calls for them. I occasionally use VV N110, which actually generally calls for a small rifle primer(I use it too when called for).
I have used them with my "mild" 357 mag Unique loads as well as with 2400 when I was running low on regular SP primers and plenty of Magnums, but they certainly aren't necessary with powders like Unique and 2400.
I'll also add that I have never used a magnum large pistol primer. Two companies-Winchester and Remington-don't even make them. Winchester primers are explicitly marked "for regular and magnum loads." The charts I've seen show that regular CCI LP primers are nearly as energetic as CCI Magnum SP primers. When in doubt, I grab Winchester LP primers for "hot" loads with 296. This is also a convenient marker for me, as Winchester primers are unplated brass and all the other primer brands I use(CCI, Remington, and Federal) are nickel plated. If I see a handloaded 44 mag or 45 Colt with a brass primer, I know it's most likely a max load.