Coming in the next beta of the Database Health Report is a new feature called the Historic Reports which installs a small database to track database statistics over time. This has an extremely light load on the overall database performance, and will allow you to track down details of why the database was slow at a specific point in time.
Here is the current rough version of the Historic waits report for one database. With this report, you can filter down to specific time frames with different granularity options to find a specific point in time where the system was slow, and from there determine what query or queries were causing the system to be slow.
The new historic waits reports should completely replace and make obsolete the existing waits reports that were included in Beta 1 and Beta 2. What I love about the new historic waits reporting is that this is something that I could use to make my job easier every day.
For now please download Beta 2 of the Database Health Reports or register on this site and you will be informed when Beta 3 of the Database Health Reports is available. Beta 2 was cool, but Beta 3 is going to be spectacular. Beta 3 is schedule for about November 15th, so I have about 3 and half weeks to get all the cool new historic waits monitoring features in for the Beta. Beta 3 will support monitoring of SQL Server 2005, 2008, 2008R2 and 2012.