Can you determine if index is used by execution plan

Question

Can you determine if index is used by execution plan

kevin_nikolai

SSCarpal Tunnel

Points: 4343
More actions
November 3, 2008 at 3:10 pm

#127348

Lets say you have a stored procedure which queries 5 tables,
via either (inner / outer / cross / full) joins.
1.
After creating indexes on the 5 tables, you now want to determine which indexes are actually being used by the execution plan (to remove indexes not used by execution plan).
Is there a way of doing this ?
2.
Lets say the execution plan shows each table, and the table index cost - does it actually use that table index or is it just an estimation ?
Regards
Kevin
sonyt65@yahoo.com

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

They are estimations, but generally they are fairly accurate. Under certain scenarios, the actual plan can differ from the estimate. What is better for what you are describing is to use the DMV's over time to identify which indexes are not being used. The sys.dm_db_index_usage_stats DMV provides usage information for the indexes in a database. It may be that a specific index is being used by other parts of the system and is still critical but just not to your one stored procedure. Tuning should be done based on the overall system, not just a single procedure within the system.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

One other thing to note is that you need to preseed your table with an amount of data for index tuning against a new table set or database to be accurate. The optimizer is cost based, so the cost to scan over one index on a small table is much less than to scan over the same index on a larger table. This is a lesson I learned first hand as we watched a new database go from extremely fast to extremely slow over a two week period as data was put into it in production. What initially seemed appropriate for indexing rapidly become inappropriate and scans began to take longer and longer to complete. I now set a requirement to estimate the production database size or rowcount for tables, and then preseed them with "junk" data that fits the data model to tune code.