Most importantly, this update includes the new trace flag that will allow users running Standard Edition to enable Locked Pages in Memory. This is a huge win for a lot of customers out there. You can read about this specific enhancement in this blog post by Bob Ward:
There are some other important updates in this CU as well (at least IMHO), including updates that fix "could not produce a query plan" errors (see KB #961106), as well as two big "incorrect query results" bugs when running parallel queries (see KB #968449) and when using the ROW_NUMBER() function (see KB #970198). Note that some of the individual KBs may reference Cumulative Update 5 for SQL Server 2008 instead of / in addition to CU2 for SP1; obviously you should not download CU5 if you are running Service Pack 1. Also note that you can't get the Locked Pages in Memory fix if you stay on the RTM track instead of moving up to SP1.
You can request the hotfix (and read about the other fixes included) in the following KB article: