我有一个大约包含220个表的SQL Server 2000数据库,其中这些表之间有多个外键关系。通过性能分析,我们发现其中一些外键关系缺少索引。而我不想只是被动地解决性能问题,我希望主动查找所有缺失索引的外键。
如何以编程方式确定哪些外键缺少索引?
我有一个大约包含220个表的SQL Server 2000数据库,其中这些表之间有多个外键关系。通过性能分析,我们发现其中一些外键关系缺少索引。而我不想只是被动地解决性能问题,我希望主动查找所有缺失索引的外键。
如何以编程方式确定哪些外键缺少索引?
SELECT *
FROM sys.foreign_keys fk
WHERE EXISTS
(
SELECT *
FROM sys.foreign_key_columns fkc
WHERE fkc.constraint_object_id = fk.object_id
AND NOT EXISTS
(
SELECT *
FROM sys.index_columns ic
WHERE ic.object_id = fkc.parent_object_id
AND ic.column_id = fkc.parent_column_id
AND ic.index_column_id = fkc.constraint_column_id
)
)
我手头没有 SQL Server 2000
的副本,但是你可能需要更改 sys.foreign_key
为 sysforeignkeys
等,就像这里
所描述的那样。
此查询选择所有不具备覆盖组成键的所有列的索引的外键。
这同样支持多列外键。
然而,如果存在一个复合索引覆盖了所有列,但它们不是在该索引中最左边的列,则将返回错误结果。
比如,如果有一个 FOREIGN KEY (col2, col3)
和一个索引 (col1, col2, col3)
,那么尽管该索引对于该外键是无法使用的,但此时将会返回该索引存在。
AND ic.index_column_id = fkc.constraint_column_id
,以确保索引也符合正确的索引键列顺序。否则,就跟我日常使用的完全一样。 - gbn这里有一个适用于SQL Server 2000的答案,由一位同事撰写:
/*
Description:
This script outputs a table with all the current database un-indexed foreign keys.
The table has three columns ( TableName , ColumnName, ForeignKeyName )
TableName: The table containing the un-indexed foreign key
ColumnName: The foreign key column that’s not indexed
ForeignKeyName: Name of foreign key witch column doesn’t have an index
*/
DECLARE
@TableName varchar(255),
@ColumnName varchar(255),
@ForeignKeyName sysname
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE FKColumns_cursor CURSOR Fast_Forward FOR
SELECT cu.TABLE_NAME, cu.COLUMN_NAME, cu.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS ic
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON ic.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE ic.CONSTRAINT_TYPE = 'FOREIGN KEY'
CREATE TABLE #temp1(
TableName varchar(255),
ColumnName varchar(255),
ForeignKeyName sysname
)
OPEN FKColumns_cursor
FETCH NEXT FROM FKColumns_cursor INTO @TableName, @ColumnName, @ForeignKeyName
WHILE @@FETCH_STATUS = 0
BEGIN
IF ( SELECT COUNT(*)
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 c.colid = xk.colid AND o.id = xk.id AND x.indid = xk.indid
WHERE o.type in ('U')
AND xk.keyno <= x.keycnt
AND permissions(o.id, c.name) <> 0
AND (x.status&32) = 0
AND o.name = @TableName
AND c.name = @ColumnName
) = 0
BEGIN
INSERT INTO #temp1 SELECT @TableName, @ColumnName, @ForeignKeyName
END
FETCH NEXT FROM FKColumns_cursor INTO @TableName, @ColumnName, @ForeignKeyName
END
CLOSE FKColumns_cursor
DEALLOCATE FKColumns_cursor
SELECT * FROM #temp1 ORDER BY TableName
在上述代码的基础上进行改进,去除临时表并获取创建索引脚本。
/*
Description:
*/
DECLARE
@SchemaName varchar(255),
@TableName varchar(255),
@ColumnName varchar(255),
@ForeignKeyName sysname
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE FKColumns_cursor CURSOR Fast_Forward FOR
SELECT cu.TABLE_SCHEMA, cu.TABLE_NAME, cu.COLUMN_NAME, cu.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS ic
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON ic.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE ic.CONSTRAINT_TYPE = 'FOREIGN KEY'
CREATE TABLE #temp1(
SchemaName varchar(255),
TableName varchar(255),
ColumnName varchar(255),
ForeignKeyName sysname
)
OPEN FKColumns_cursor
FETCH NEXT FROM FKColumns_cursor INTO @SchemaName,@TableName, @ColumnName, @ForeignKeyName
WHILE @@FETCH_STATUS = 0
BEGIN
IF ( SELECT COUNT(*)
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 c.colid = xk.colid AND o.id = xk.id AND x.indid = xk.indid
WHERE o.type in ('U')
AND xk.keyno <= x.keycnt
AND permissions(o.id, c.name) <> 0
AND (x.status&32) = 0
AND o.name = @TableName
AND c.name = @ColumnName
) = 0
BEGIN
INSERT INTO #temp1 SELECT @SchemaName, @TableName, @ColumnName, @ForeignKeyName
END
FETCH NEXT FROM FKColumns_cursor INTO @SchemaName,@TableName, @ColumnName, @ForeignKeyName
END
CLOSE FKColumns_cursor
DEALLOCATE FKColumns_cursor
SELECT 'CREATE INDEX IDX_' + ForeignKeyName + ' ON ' + SchemaName + '.' + TableName + '(' + ColumnName +')'
FROM #temp1
ORDER BY TableName
drop table #temp1
--Finds foreign keys without indexes
--How to interpret:
--When we delete frpm PkTable, it checks FkColumn for the PkId we are deleting.
--So if FkTable doesn't have an index on FkColumn, then we cannot delete a row from PkTable because it is too slow.
SELECT rt.name as PkTableName, rc.name as PkColumnName,
fk.name FkName, t.name as FkTableName, c.name as FkColumnName, ddps.row_count, i.name as IndexName
FROM sys.foreign_key_columns fkc
inner join sys.foreign_keys fk on fkc.constraint_object_id = fk.object_id
inner join sys.tables t on fkc.parent_object_id = t.object_id
inner join sys.columns c on fkc.parent_object_id = c.object_id and fkc.parent_column_id = c.column_id
inner join sys.tables rt on fkc.referenced_object_id = rt.object_id
inner join sys.columns rc on fkc.referenced_object_id = rc.object_id and fkc.referenced_column_id = rc.column_id
inner join sys.indexes ri on t.object_id = ri.object_id
inner JOIN sys.dm_db_partition_stats AS ddps ON ri.OBJECT_ID = ddps.OBJECT_ID AND ri.index_id = ddps.index_id
left join sys.index_columns ic on ic.object_id = t.object_id and ic.column_id = c.column_id
left join sys.indexes i on ic.object_id = i.object_id and i.index_id = ic.index_id
where ri.index_id < 2 and i.index_id is null and ddps.row_count > 0
order by
--PkTableName,
ddps.row_count desc