yblatti
commented
9 years ago Original author : Van Hoecke Hélène
A solution is to run this query that creates all missing index on foreign keys :
Select
'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].['
+ tab.[name]
+ ']'') AND name = N''IX_'
+ cols.[name]
+ ''') '
+ 'CREATE NONCLUSTERED INDEX [IX_'
+ cols.[name]
+ '] ON [dbo].['
+ tab.[name]
+ ']( ['
+ cols.[name]
+ '] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
From sys.foreign_keys keys
Inner Join sys.foreign_key_columns keyCols
On keys.object_id = keyCols.constraint_object_id
Inner Join sys.columns cols
On keyCols.parent_object_id = cols.object_id
And keyCols.parent_column_id = cols.column_id
Inner Join sys.tables tab
On keyCols.parent_object_id = tab.object_id
Order by tab.[name], cols.[name]
Original author : Van Hoecke Hélène
There is some missing indexes in the SQL server database schema.
For example, #__sdi_resource and its related tables don't have indexes on foreign keys, which are usefull for the queries executed to build the resources page.