One way to improve performance in SQL Server is simply to ensure that there are indexes on all foreign key. This is NOT done automatically, which is kind of surprising given how much of an impact such a change would have on the performance of a significant install base of SQL Server.
I have a simple stand-alone SQL Script that I execute to generate the SQL for the missing foreign key indexes. Here it is:
SELECT DISTINCT 'CREATE INDEX IX_' + REPLACE(objconstraint.name,'.','_') + ' ON ' + fkcols.name + '.' + objfk.name + '(' + STUFF(( select ',' + col.name from sys.foreign_key_columns fkcol2 join sys.columns col on fkcol2.parent_object_id = col.object_id and fkcol2.parent_column_id = col.column_id and fkcol2.constraint_object_id=fkcol.constraint_object_id),1,1,'') + ');' FROM sys.foreign_key_columns fkcol JOIN sys.objects objfk ON fkcol.parent_object_id = objfk.object_id JOIN sys.schemas as fkcols ON fkcols.schema_id = objfk.schema_id JOIN sys.objects objconstraint ON fkcol.constraint_object_id = objconstraint.object_id LEFT JOIN sys.index_columns ic ON ic.object_id = fkcol.parent_object_id AND ic.column_id = fkcol.parent_column_id AND ic.index_column_id = fkcol.constraint_column_id WHERE ic.object_id IS NULL
This script outputs the results in the results grid, so you can cut and paste it into a new query window for execution.
I was asked why I didn’t use INFORMATION_SCHEMA views in this bit of SQL. The answer is simple – the standard for INFORMATION_SCHEMA doesn’t include Indexes. Enjoy!