Since I'm typing this on my new Eee 1101HA I'm going to keep it short. (I hate these mini keyboards)
Keeping an eye on our production Sql Server box is an integral part of my daily routine. I would not categorize myself as a DBA as I don't do much administrative work related to our databases. I'm dealing mainly with issues that are related to development and making sure our system runs efficiently. One of those tasks is to make sure that our database is properly indexed and maintaining indexes as the database grows. Anyone who has done any significant work against a database knows the importance of proper indexing and that proper indexing is a balancing act.
Clustered, non clustered, covering, fill factors and page splits are just a tiny subset of the things that you have to keep on top of.
My absolute favorite feature in Sql Server 2008 is filtered indexes. In a nutshell you create an index with a filter which is used to determine what goes into the index and what not.
Let's say you have a table with some bit flags. The selectivity on these columns might be too low to be of any real use for even the smartest query analyzer and you'll often see scans when filtering on these bit flags.
In some of our tables we have bit flags where the vast majority of the records have these bit flags set to false. In these scenarios filtered indexes just shine. After creating a filtered index on only the true values of the bit flag we had queries that would take minutes to execute drop to a few milliseconds.
For more information and some important issues related to maintaining filtered indexes, you should check out the following excellent posts.