A nice new feature in the RC0 build of SQL Server 2008 is what I call the "missing index warning" that shows up when you have the graphical execution plan turned on in SQL Server Management Studio (SSMS), as you can see in green, below. In the pane where the SQL statement is shown, you can see "Missing Index (Impact 99.652….", complete with a skeleton index creation script.
Just in case you are curious, here is the query that generated that warning (using the AdventureWorks database). Since there is no index on CarrierTrackingNumber, and it is used in the WHERE clause of the query, SQL Server wants to do a clustered index scan of a 121,317 row table even though the query returns only 12 rows. This generates 1240 logical reads, which is pretty costly.
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber FROM Sales.SalesOrderDetail WHERE CarrierTrackingNumber = '4911-403C-98'
Adding the index (shown below) gives you an index seek, with 3 logical reads, which is quite an improvement in this case.
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_CarrierTrackingNumber] ON [Sales].[SalesOrderDetail] ( [CarrierTrackingNumber] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]