This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing one T-SQL script to find a Table which has more than 10 Indexes in SQL Server.
More Indexes on a Table, are creating a performance issue because Indexes also require dedicated CPU I/O for writing it into the Index Pages.
More Indexes will slow down the insertion or any other DML operation of that particular Table.
Below is a script to find out tables which are having more indexes:
You can change the value of @threshold.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE @threshold INT; SET @threshold = 10; SELECT [s].[name] + '.' + [t].[name] AS TableName FROM sys.tables AS t INNER JOIN sys.schemas AS s ON [t].[schema_id] = [s].[schema_id] WHERE EXISTS ( SELECT 1 FROM sys.indexes AS i WHERE [i].[object_id] = [t].[object_id] GROUP BY [i].[object_id] HAVING COUNT(*) > @threshold ) GO |
