Missing Index Warning In SQL Server 2008 SSMS RC0

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]

Technorati Tags:

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s