http://www.sqlservercentral.com/blogs/steve_jones/2010/10/20/common-sql-server-_1320_-not-indexing-fks/

Printed 2014/04/23 01:40AM

Common SQL Server – Not Indexing FKs

By Steve Jones, 2010/10/20

This series looks at Common SQL Server mistakes that I see many people making in SQL Server.

Foreign Keys

It’s way too often that I see people building databases without including declared referential integrity (DRI) in their databases. Even when I see people setting a primary key on tables, it seems that often they ignore foreign keys and creating linkages between tables that link them together.

However, even when people have declared a FK, they often don’t create an index on that column. Perhaps they assume that SQL Server will create the index like it does for PKs, but it does not.

If I create these two tables and join them with a FK:

CREATE TABLE [dbo].[Products](
    [ProductID] [int] NOT NULL,
    [ProductName] [varchar](50) NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
    [ProductID] ASC
)
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[ProductDetails](
    [ProductDetailID] [int] NOT NULL,
    [ProductID] [int] NULL,
    [SKU] [varchar](50) NULL,
    [Price] [numeric](18, 2) NULL,
CONSTRAINT [PK_ProductDetails] PRIMARY KEY CLUSTERED
(
    [ProductDetailID] ASC
)
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductDetails]  WITH CHECK ADD  CONSTRAINT [FK_ProductDetails_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
GO

ALTER TABLE [dbo].[ProductDetails] CHECK CONSTRAINT [FK_ProductDetails_Products]
GO

If I go and check indexes on ProductDetails, I find that there is only one index, the index for the PK.

FKIndex_a

Why is this a problem? It’s because of performance. We should realize that indexes speed up performance by reducing the amount of work that SQL Server has to do.

With FK columns, what I’ve often found with child tables is that I know the value of the FK column I am searching for and don’t need to join with the parent table. However without an index on the FK column, this query requires a table scan.

select
sku
, price
from ProductDetails pd
where pd.ProductID = 3

If you are creating FKs in your database, don’t forget to index them where appropriate.

Auto Creation

I’ve seen some people ask why SQL Server doesn’t automatically create indexes on those FK columns. I am torn on this, but I like the 80/20 rle. If 80% of the tables would benefit from it, I think it should be done. I am leaning towards some intelligent mechanism to do this.

The main issue is that you might not want just an index on the FK column. You might want some sort of covering index that includes columns in addition to the FK column to prevent key/bookmark lookups to the clustered index. If you can avoid those, you can drastically increase performance.

There is also the chance that with your query load, you never use these indexes. That can be horrible for performance as well since there is overhead to maintain these indexes on all insert/update/delete operations.

The Advice

Look at the queries that are coming into your database. Check the missing index DMVs and if you find that the FK columns are being used, index them.

If you’re not sure, or don’t know how to look for missing indexes, here’s a reference.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.