Tuesday, July 29, 2008 11:38 PM,
Filed Under
SQL
If you want a quick way to set some default indexes up in your database the best candidates for your indexes would be the foreign keys. They are usually highly selective and many of our queries tend to have some sort of filter on the foreign keys, this makes them good candidates for an index.
You may be quick to think:
'but sql server has indexes on the foreign keys'
That's not true, since a foreign key is an integrity constraint while an index is for performance. Have a look at an interesting discussion over at sqlblog about this: http://sqlblog.com/blogs/greg_low/archive/2008/07/29/indexing-foreign-keys-should-sql-server-do-that-automatically.aspx
The following SQL Script will create a non clustered index on each of the foreign key columns in our database.
--Create non clustered indexes for all foreign key references in the database
DECLARE @sql nvarchar(max)
SELECT @sql = IsNull(@sql + ';' + char(13) , '') + 'CREATE NONCLUSTERED INDEX [IX_' + tablename + '_' + columnname +'] ON [dbo].[' + tablename + '] ( [' + columnname + '] ASC)'
FROM
(
--Script all foreign key columns that are not already an index SELECT
o.name AS tablename, cols.name AS columnName FROM sys.foreign_key_columns fc
inner join sys.objects o on fc.parent_object_id = o.object_id
inner join sys.columns cols on cols.object_id = o.object_id and fc.parent_column_id = cols.column_id
EXCEPT
SELECT o.name, cols.name FROM sys.index_columns icols
inner join sys.objects o on icols.object_Id = o.object_id
inner join sys.columns cols on cols.object_id = o.object_id and icols.column_id = cols.column_id
) T
ORDER BY
tablename,
columnname
--Display the sql that will be executed
Print @sql
--Run the sql and create all the indexes
exec sp_executesql @sql
To monitor the performance of your existing indexes check out the script sp_indexInfo by Tibor Karaszi.
It is a stored procedure that provides some useful metrics for all the indexes in a given table or in a given database.