Excellent suggestions David! I'm also a humongous fan of Adam's sp_whoIsActive.

For index maintenance I've predominately gone with Michelle Ufford's (@SQLFool) Index Defrag Script for this. Stick to what you know and all that.

Glenn Berry has a number of great scripts on his blog too. Such as his SQL Server 2012 Diagnostic Information Queries. Also available for SQL Server 2005 and 2008.

Richard Ding's sp_SDS, the lovely people over at Brent Ozar PLF's sp_Blitz. 

Impeccable timing on a mention for sp_Blitz there Chris. Brent just blogged about the latest release of this stored procedure, which I'm slightly ashamed to say I've not actually toyed with yet.

I'm not familiar with sp_SDS. Will have a gander.

My personal fav are -

1. Whoisactive
2. SP_Blitz
3. Glenn Berry's diagnostic queries

I don't see this one mentioned but it is fairly new and I sat on in one of her WebEx's; Kendra Little has a great video and download in parallel with the sp_blitz called sp_blitzindex

http://www.brentozar.com/archive/2012/12/instant-index-insight-how-to-use-sp_blitzindex-video/

Enjoy

Good catch/share.

So I downloaded the SSMS Tools utility that was noted here. I must say I like it very much. I have the ability to use the SQL Search Utility but the option of having your cursor over say a udf for example right clicking and selecting locate object and it take you straight to the object is quite nice. The color toolbars dependent for each database has helped some of our newbies to know when they are on some very important servers. The tool tips suffice if they are needed. All around I'm a fan.

Thanks for noting it here.

Stop having fun at work Chris.

Are you talking about (ssmsboost.com)?

Nice one Luke! Be sure to let us know how you get on.

I've used similar scripts in the past (I'm keen to get my hands dirty with this one) and they almost always identify areas we can improve our database systems. The one that seems to crop up most in my experience is un-used indexes. As the workload for an environment changes over time it can be years since a given index was originally deployed, sometimes these things fall out of use and go unnoticed. All the while consuming storage and incurring write overhead. It can certainly add up.

As an aside, we're big on talking Professional Development around these parts  so make sure you document all the proactive efforts you are taking and the positive effects they have for the systems/application and company. It will help you build a stronger case come review time.

My all time favourite is aba_lockinfo by Erland Sommarskog as it shows the lead blocker, object locking chain and SQL cmds in process: http://www.sommarskog.se/sqlutil/aba_lockinfo.html
There is a beta_lockinfo version, but I still prefer aba_lockinfo

Glenn Berry's diagnostic queries are great, especially instantaneous CPU history query.
As is this query here: http://social.msdn.microsoft.com/Forums/en-GB/transactsql/thread/8af7ac39-7b09-4b84-9c1c-95573c7350d8 If I find the time to roll my own this would be the starting point.

As with any of these make sure you understand what is being returned!