CODEWITHSUNDEEP
sql-serverindexing
Kahn
Kahn

Reputation: 1660

Nonclustered index functionality relative to clustered index seek

the question is quite simple, but we've had so many issues with index/statistics updates not always resulting in the proper new execution plans in low-load environments that I need to check this with you guys here to be sure.

Say that you have the following tables:

/*
TABLES: 
TABLE_A (PK_ID INT, [random columns], B_ID INT (INDEXED, and references TABLE_B.PK_ID))
TABLE_B (PK_ID INT, [random columns], C_ID INT (INDEXED, and references TABLE_C.PK_ID))
TABLE_C (PK_ID INT, [random columns])
*/

SELECT *
FROM TABLE_A A
JOIN TABLE_B B ON B.PK_ID = A.B_ID
JOIN TABLE_C C ON C.PK_ID = B.C_ID
WHERE A.randcolumn1 = 'asd' AND B.randcolumn2 <> 5

Now, since B is joined to A with its clustered PK column, shouldn't that mean that the index on B.C_ID will not be used as the information is already returned through the B.PK_ID clustered index? In fact, is it not true that the index on B.C_ID will never be used unless the query specifically targets the ID values on that index?

This may seem like a simple and even stupid question, but I want to make absolutely sure I'm getting this right. I'm thinking of making adjustments on our indexing, since we have a lot of unused indexes which have been inherited from an old datamodel and they're taking up quite a bit of space in a DB this size. And experience has shown that we cannot fully trust the execution plans on any environment apart from the production thanks to its extreme load compared to testing environments, which makes it difficult to test this out reliably.

Thanks!

Upvotes: 0

Views: 39

Answers (1)

Andomar
Andomar

Reputation: 238296

The query optimizer is free to do as it pleases. It could execute the second join by scanning the C table, and for each row, looking up the matching row in B. The index you describe would help with that lookup.

SQL Server provides statistics to tell you if an index is actually used:

select  db_name(ius.database_id) as Db
,       object_name(ius.object_id) as [Table]
,       max(ius.last_user_lookup) as LastLookup
,       max(ius.last_user_scan) as LastScan
,       max(ius.last_user_seek) as LastSeek
,       max(ius.last_user_update) as LastUpdate
from    sys.dm_db_index_usage_stats as ius
where   ius.[database_id] = db_id()
        and ius.[object_id] = object_id('YourTableName')
group by 
        ius.database_id
,       ius.object_id

If the index isn't used for more than 2 months, it is usually safe to drop it.

Upvotes: 1

Related Questions