CODEWITHSUNDEEP
sql-serversql-server-2008
user172839
user172839

Reputation: 1065

Choosing indexes

Let's imagine we have the following table

 TABLE Something
      id
      description
      status

      PK (clustered index): id
      NC Index: id_status

  1. Is having the NC index useless?

  2. 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?

Upvotes: 0

Views: 33

Answers (1)

user275683
user275683

Reputation:

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

Upvotes: 1

Related Questions