Just came across these bad boys, that can drastically increase your query performance as well as reduce the over head in the maintenance and storage costs of the index
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate ON Production.BillOfMaterials (ComponentID, StartDate) WHERE EndDate IS NOT NULL ;
For more information on these see here
Also just had my eyes opened to this new concept in SQL Server 2008 called xEvents
which can be helpful for performance reasons...