Give this article a Google +1!
Issue 7 Number 5 May 2017Tweet This Article.
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.
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.
On this page:
Elsewhere on this site: