Let's imagine we have the following table
TABLE Something id description status PK (clustered index): id NC Index: id_status
Is having the NC index useless?
If we replace NC index id_status with index status, and the query references status and id in the JOIN query, does this mean PK_id and PK_status will be both used?
Not really an answer, just help to find all Stored Procedures that have dependency on specific table. This way you can check how your indexes are used or not used by SP
SELECT OBJECT_NAME(sed.referencing_id) AS 'ReferEntity' ,o.type_desc AS 'Description' ,UPPER(sed.referenced_database_name) AS 'DB_Name' ,sed.referenced_schema_name AS 'Schema' ,sed.referenced_entity_name as 'EntityName' ,count(*) over (partition by OBJECT_NAME(sed.referencing_id)) as CountAll FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id WHERE o.type_desc = 'SQL_STORED_PROCEDURE' and sed.referenced_entity_name = 'MyTable' order by 1