We recently ran into a problem with SMS Report number 97 that seems to be cropping up from time to time. In essence, the problem arises due to a different SQL view of Add/Remove programs data in the SQL database to account for the new 64 bit add/remove programs information now tracked by SMS in SP3. With SP3, MS has changed the default reports to use this new view in all instances of Add/Remove programs queries. The old SQL view was v_gs_Add_Remove_Programs which has been replaced with v_ Add_Remove_Programs.
There is a workaround and description of the issue on TechNet here: http://blogs.technet.com/smsandmom/archive/2007/06/28/sms-2003-sp3-add-remove-programs-reports-may-cause-a-cpu-spike-and-or-sql-timeout.aspx
and Chris Stauffer blogged about running into it here: http://myitforum.com/cs2/blogs/cstauffer/archive/2007/07/09/problem-with-report-97-after-upgrading-to-sp3.aspx
I ran into this problem about a week ago, but had it show up in an interesting manner...through Ron Crumbaker's SMS Web Tool which we use extensively at my company. Whenever a tech would click on the add/remove programs button to query what programs are installed on a machine, the tool would hang, and nothing in it would work for anyone. In our case, the SMS reporting server did not show any excessive CPU or memory usage, but it was SQL that was getting hung up. Stopping and restarting the SQL server would bring it back to life immediately.
After disabling the Add/Remove programs button in the web tool, I ran across the above mentioned post on technet about this issue. Sounded like exactly the issue we were running into, and sure enough the Add/Remove button in Ron's web tool was calling SMS report 97, which now uses the new SQL view v_ Add_Remove_Programs for displaying Add/Remove programs information.
Unfortunately the workaround suggested on technet is to copy the old reports (from SP2) back into SMS with a different name. Not only would I have to change the web tool to point at the new report name, but I would also have to track down any other reports we had in the system that may have linked to report 97 and change them as well. I decided instead to change report 97 to use the old v_gs_Add_Remove_Programs view wherever it was calling v_ Add_Remove_Programs previously. Sure enough, as soon as I made those changes and saved the report, it started working great. I re-enabled the button in the web tool, and voila! No more hangs, no more timeouts, no more screaming techs...well OK, they only stopped screaming about this issue. 
I have not yet gone through all of our reports to replace all instances of the new SQL view in hopes that MS comes up with a workaround soon, but I'm not going to wait long because when this crashes in our site it crashes hard.