Dynamic Management Views (DMVs) are one of the nicest new DBA manageability feature in SQL Server 2005. The feature exposes much of what was hidden or unavailable in SQL Server 2000. Let me start the series with one of my favorite DMVs for the lazy DBA like myself: sys.dm_db_missing_index_details. Well, I won't start by saying I'm lazy perse, but this does give you a nice starting point to investigate.
The sys.dm_db_missing_index_details DMV gives you hints on what indexes the database engine thinks you need on a given database. It gives you the object name where the index would be useful and the columns that it would recommend. Use the object_name(object_id) function to translate the object id to it's real logical name. Where this is going to be extremely useful is when you go into a new environment for the first time and need a general gist of where the weaknesses are. Then, you can go logically through the system to make sure this gist is correct.
There are a few important notes with DMVs. DMVs are only in scope until you stop and start SQL Server. After that, the DMVs are all flushed and it may take time to get the true statistics again. The other thing to keep in mind with the sys.dm_db_missing_index_details in particular is the recommendations it gives you are not the law. Much like the Databaes Engine Tuning Advisor, it may recommend indexes that are 16 columns wide, which is probably not practical in your environment. Use its recommendations with a bit of untrustingness.