Amazingly enough I have just learned for a new very convenient way of investigating bad performance. Usually when someone says “My SQL is running very slowly” I log on to the server and start looking at usual Task manager, Activity Monitor and of course – running a bunch of queries against system DMVs and decoding whatever info you find relevant. Well…shame on me There are quite cool SQL Server management reports published just for that purpose and they go under the name of SQL Server Performance Dashboard reports. They are released for SQL Server 2005 and you can download them from this link.
You can also use them with SQL Server 2008 but with some additional edit of the setup script. And all is very well documented here.
And for those of you who do not enough time to play with it on their own, there is a video that shows pretty much everything you need to know for Performance Dashboard – from setting it up, through using it and performing analysis.
Of course this reports are just providing you the data. You will have to interpret it yourself and turn it into proper information so you can react adequately to the situation
Happy reporting and troubleshooting!