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