@navZ88, every CPU at present uses SSE (Streaming SIMD Extensions) to perform calculations.
In simple terms, a CPU extension is a collection of instructions for the CPU.
The most common CPU extensions are: MMX, SSE, SSE2, SSE3, SSSE3, SSE4.1, SSE4.2 and AVX (arranged lauch-date wise, from old to new)
There are others as well, but these are main ones.
Note that the various SSE versions are incompatible among each other.
But CPUs supporting higher versions of SSE support the lower versions as well.
For example, the current i-series Intel Sandy Bridge processors are AVX processors.
That means, they support all the above mentioned instructions upto AVX.
While AMD Phenom II and Athlon II CPUs are just SSE3, that means they don't support SSSE3, SSE4.1, SSE4.2 and AVX.
But they do have a AMD exclusive SSE4a extension set that's incompatible with all Intel CPUs and due to the major market share of the latter, apps/games pay less attention towards SSE4a. A point to be noted is that the recently lauched AMD Bulldozer CPUs (FX-xxxx) support all SSE extensions and even AVX.
Many games/applications demand certain SSE requirements
For example, Autodesk Maya, widely used to create 3D animation, requires an Intel/AMD CPU with at least SSE3.
http://www.autodesk.de/adsk/servlet/pc/index?siteID=452932&id=14595158
What happens when the CPU doesn't meet the SSE requirments is that the game/app will just fail to run, or will crash when the unsupported SSE instruction is executed. Some apps/games won't straight away mention the SSE requirement, but go the other way and demand a CPU with at least Intel Pentium D or AMD Athlon 64 or higher CPU, both of these "least" requirements support SSE3 max. This implies that P4 and older CPUs without SSE3 won't work with these apps/games.
However, SSE3 CPUs are fully compatible at present, but it's hard to say till when it will hold true because developers are really loving working with AVX as it's much flexible and faster than SSE.
About Piledriver, it's a new CPU microarchitecture by AMD that will be released in 2012 in Trinity APUs that will replace the current Llano platform but the socket will change to FM2. Pliedriver for "CPUs" will replace the current Bulldozer platform in Q4 2012 or early 2013 according to AMD. Phenom II is based on old K10 microarchitecture.
With Piledriver, AMD claims 10% performance improvement over Bulldozer, which, unfortunately is around 30% behind the now old Intel Sandy Bridge, so just think how far it would be behind Ivy Bridge that's to be launched in maybe April 2012, which claims around 20% performance improvement over Sandy Bridge!!!
Hope this lengthy explanation satisfies your queries.