I am not new to SQL Server but have mostly spent my time working on consolidation projects, building new environments, doing upgrades, and working on some ETL. What I seriously lack is the ability to jump on a troubled server and figure out what is or could be causing issues. Standard things like disk I/O, low PLE I can look at. What I don't know how to do is find out which queries are causing issues, where contention is, what queries are eating up my cache, crushing my disk, spiking my CPU, etc. I have ran the queries that Glenn Barry put out, but once I get some of the results I am not 100% sure what to do with them. What is the book, webinar, class that I should attend to really build out this skill set?
This is often a problem with troubleshooting scripts, if they pump out a number - bing! 59! - there's often no context as to what that means or what it should be. Is a lower number better or worse? is the range 0-10 or -1000 to + 3million. Take for example a script that shows the top 10 wait types - even in the best performing server in the world, there will be 10 top wait types, so that information has to be taken, read and understood in context. I'd also look at [Brent Ozar's server takeover script - sp_blitz]. Whilst this is not a troubleshooting script per se, it does look for many common issues and not only reports them out but the website has a lot of supporting information about the what, why and where. :
One issue that I've seen is that index/statistics maintenance is scheduled before jobs that rearrange data in databases, while optimal would have been the other way around. Not the answer to your question but make sure that your server is optimally configured. If you are looking for a class I would seriously have a look at:
http://www.sqlskills.com/T_ImmersionPerformanceTuning.asp While I haven't attended the class myself it looks really good and it seems to be addressing your needs. At this years PASS Summit the new version of sp_blitz will be released. It will be able to catch query issues such as implicit conversion, cursors, and bad functions. Sp_blitz is moving into troubleshooting territory maybe?