How to index all foreign keys in MS SQL?

Question

We have large MS SQL database with many tables and foreign keys. We need to index all foreign keys in our tables due to performances and we are trying to avoid do it manually. Is there any way to MS SQL do it automatically or with some tools?

MS SQL server used is MS SQL Server 2008 R2.

Thanks a lot.

Answer 1

Script below should help:

declare @schemaName varchar(128)
declare @tableName varchar(128)
declare @columnName varchar(128)

declare @refSchemaName varchar(128)
declare @refTableName varchar(128)
declare @refColumnName varchar(128)

declare @sql nvarchar(max)
set @sql = ''

declare cur cursor for
select sch.name as SchemaName, tab.name as TableName, col.name as ColumnName, ref_sch.name as RefSchemaName, ref_tab.name as RefTableName, ref_col.name as RefColumnName
from sys.foreign_keys fk
join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id
join sys.tables tab on tab.object_id = fkc.parent_object_id
join sys.schemas sch on sch.schema_id = tab.schema_id
join sys.columns col on fkc.parent_column_id = col.column_id and fkc.parent_object_id = col.object_id
join sys.tables ref_tab on ref_tab.object_id = fkc.referenced_object_id
join sys.schemas ref_sch on ref_sch.schema_id = ref_tab.schema_id
join sys.columns ref_col on fkc.referenced_column_id = ref_col.column_id and fkc.referenced_object_id = ref_col.object_id

open cur
fetch next from cur into @schemaName, @tableName, @columnName, @refSchemaName, @refTableName, @refColumnName
while @@FETCH_STATUS = 0
begin
    set @sql = '
    if not exists (SELECT * FROM sys.indexes 
    WHERE name=''IX_' + @schemaName + @tableName + '_' + @columnName + ''' AND object_id = OBJECT_ID(''' + @schemaName + '.' + @tableName + ''')) 
    begin
        CREATE INDEX IX_' + @schemaName + @tableName + '_' + @columnName + ' ON [' + @schemaName + '].[' + @tableName + '] 
        ([' + @columnName + ']) WITH (FILLFACTOR = 80) 
    end'
    --print @sql
    exec sp_executesql @sql
    fetch next from cur into @schemaName, @tableName, @columnName, @refSchemaName, @refTableName, @refColumnName
end

close cur
deallocate cur

After selecting all foreign keys it creates indexes with name formated like 'IX_"schemaName""tableName"_"columnName"' and fillfactor = 80.

Answer 2

This will allow to find all the FKs and their associated columns. If one where so inclined they could write a script that tests cardinality of the tables involved and generate indexes

Select rc.constraint_name, table_name,column_name 
from 
    information_schema.REFERENTIAL_CONSTRAINTS rc
    INNER JOIN information_schema.CONSTRAINT_COLUMN_USAGE usage
    on rc.constraint_name = usage.constraint_name

Answer 3

I'd steer you away from trying to do this automatically and encourage you to examine your system (Google missing index DMVs (SQL 2005 or later) for a good start) and create appropriate indexes instead. If you try to do this automatically, you could potentially introduce issues like:

• Creating indexes on columns with low cardinality that will consume resources but never be used (e.g., a foreign key to a lookup table with a small number of possible values)

• Introducing bookmark lookups because the indexes you created could have benefited from including additional columns.

I'm not saying your foreign keys should not be indexed, just that there should be some thought put into the creation of those indexes.