In 20+ years of consulting on the SQL Server relational engine at all kinds of scale I can count on one hand the number of times it was REALLY OPTIMAL to mess with any of the various affinities.