sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 877.201.3428 toll-free

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

Give this article a Google +1!

A Story About Foreign Keys

Issue 7 Number 5 May 2017

 

This month I am going to illustrate the importance of indexing Foreign Keys (FKs) and I am going to brag about how I saved a foundering company in one simple step.

I will start with the bragging and later explain what I did to save the company.

Years ago I got a desperate call from the CEO of a financial services company. The company created and managed documents for loans, mortgages, etc. It had a few small rural banks as clients and things had been going well. However they had finally landed one of the biggest banks in the United States as a client and things immediately went to pieces. Their application and their SQL Server database could not handle the increased load.

Not only the big bank was talking about cancelling their contract but the other small banks were threatening the same thing.

I hopped on a plane and was in their office the next morning.

The place was a madhouse. The staff were running around in a panic trying to get things done with an application and database that were deathly slow.

I looked at the database. It had an Attribute\Value schema. Those types of schemas in a relational database can be real performance problems. At first I thought that was it. Everyone was in despair because they couldn't start over with a different sort of schema in the time they had left.

On further examination I noticed that there were a lot of FKs declared and most of them were not indexed. As you probably know, the Primary Key is always indexed because it IS an index, but the corresponding Foreign Key is not automatically indexed.

Without much hope that this could help the problem, I decided to index all the FKs. It was a big database and I didn't have the time to find the unindexed FKs and index them, so I wrote the script that you will find below. It first finds the FKs and then creates a list of creation scripts for all unindexed FKs.

It was the end of the day so I started the script and went back to my hotel.

When I returned the following morning I found the CEO sitting in my cube. She was crying. She said "You have saved my company." I went back in to the big office. I remember thinking of the scene in the Wizard of OZ when everyone is dancing and singing "The Wicked Witch is Dead." The mood was like that. I was a pretty popular guy that day.

Here's the script. It is old but it still works. You will note that it does not create any indexes. It creates the text of the index creation commands. I did that so we could check for mistakes in the script before running the creation commands.

  • SELECT     pk.table_name,
  •     c.COLUMN_NAME
  • into #FK
  • FROM     INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
  • INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ON
  •     c.TABLE_NAME = pk.TABLE_NAME AND
  •     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
  • LEFT OUTER JOIN
  •     (SELECT    TABLE_NAME        = o.name,
  •         INDEX_NAME        = x.name,
  •         ORDINAL_POSITION     = convert(int, xk.keyno),
  •         COLUMN_NAME        = c.name
  •     FROM    sysobjects o
  •     INNER JOIN sysindexes x ON
  •         x.id = o.id
  •     INNER JOIN syscolumns c ON
  •         o.id = c.id
  •     INNER JOIN sysindexkeys xk ON
  •         o.id = xk.id
  •     WHERE     o.type in ('U') AND
  •         x.indid = xk.indid AND
  •         c.colid = xk.colid AND
  •         -- uncomment out below statement if you
  •         -- only wish to count leftmost columns as being indexed.
  •         --> convert(int, xk.keyno) = 1 AND
  •         xk.keyno <= x.keycnt AND
  •         (x.status&32) = 0   -- No hypothetical indexes
  •     ) ikeys ON
  •     ikeys.TABLE_NAME = pk.TABLE_NAME AND
  •     ikeys.COLUMN_NAME = c.COLUMN_NAME
  • WHERE     pk.CONSTRAINT_TYPE = 'FOREIGN KEY' AND
  •     c.ORDINAL_POSITION = 1 AND  -- leftmost column in FK
  •     ikeys.COLUMN_NAME IS NULL
  • --select * from #FK
  • select o.name, schema_name(o.schema_id)as scma
  • into #schema
  • from sys.objects o
  • where type = 'U'
  • and o.name not like 'sys%'
  • select o.name
  • , sum(i.rows) as rows
  • , sum(case      when indid < 2 then i.dpages
  •         when indid=255 then i.used
  •         else 0 end) as allpages
  • into #tables
  • from sysobjects o
  • join sysindexes i on o.id = i.id
  • where (indid < 2 or indid = 255)
  • and o.name not like 'sys%'
  • group by o.name
  • --order by allpages desc
  • /*
  • select #fk.table_name
  • ,     #fk.COLUMN_NAME
  • ,    #tables.rows
  • */
  • SELECT     'CREATE NONCLUSTERED INDEX idx_' +
  • --    RTRIM(#fk.table_name) +
  • --    '_' +
  •     #fk.COLUMN_NAME +
  •     ' ON [' +
  •         RTRIM(#schema.scma) +
  •     '].[' +
  •     RTRIM(#fk.table_name) +
  •     '](' +
  •     #fk.COLUMN_NAME +
  •     ')' + CHAR(10) + 'GO' + CHAR(10)
  •     
  • from #fk join #tables on #fk.table_name = #tables.name
  • join  #schema on #schema.name = #tables.name
  • where rows > 1000
  • order by rows desc
  • drop table #FK
  • drop table #tables
  • drop table #schema

 

Afterward by the Author

I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.

As always, if you have questions or comments give me a call toll-free at 1.877.201.3428 or send me email.

Kurt Survance, SQL Consulting, Inc.

Return to Top

Go to SQL Consulting Home

On this page:

A Story About Foreign Keys

Elsewhere on this site:

 
Call SQL Consulting
 
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator