Stiffness is good when it helps to improve alignment, strength, mobility, and dynamic motor control. It is typically a deliberate engagement of muscles prior to movement. As an example, engaging your anterior core promotes posterior pelvic tilt thus improving (rather than limiting) range of motion in the hips.
Pain and limited range of motion often signal stiffness. This kind of stiffness is often caused by or leads up to an injury such as a sprain or tear, not to mention an increased likelihood of faulty movement patterns. The problem with bad stiffness is that it may indicate overactivation of the nervous system. This increased sensitivity can impede performance in everything from lifting, to jumping and running.