The newer block C Sparrow isn't too bad...
but it was still a beam-riding weapon meaning the firing ship had to "paint" the target and remain in the area. As opposed to firing the missile and then maneuvering out of the way. No one really knew it was shit until the Vietnam war. By that time the Basic Point Defense Missile System (BPDMS) had been installed on a number of ship classes. Once Sparrows reliability - or lack of - became widespread, mods were done (Improved Point Defense Missile System)(IPDMS) which addressed some of the problems but it was still a buggy missile.
One of the ships tested AMRAAM out of the same launcher, which is 900% better. But by that time, vertical launch had taken over the Spruance Class and the Knox Class were being retrofitted with CIWS. AMRAAM can pretty much be fired by any aircraft so every plane on the carrier became a virtual fighter. So a lot of the carriers started just phasing out the IPDMS systems. A lot of the carriers and LHA's/LHD's have SeaRAM in their place. Notably Enterprise. Coral Sea just had CIWS on it's own; no BPDMS or IPDMS.