Is that index really unused?

Question

Is that index really unused?

Ian_McCann

SSCertifiable

Points: 5163
More actions
January 26, 2012 at 2:42 am

#250207

Is it correct that an index can assist in a query, but still show as unused in the execution plan or in sys.dm_db_index_usage_stats.
I think I have seen an example where an order by or a group by took advantage of an index, but the usage was unreported. I have been unable to backtrack to the article or be able to recreate an example myself.
Many thanks in advance
Ian

Viewing 7 posts - 1 through 6 (of 6 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

Yes, the mere existence of a *unique* index can be used by the optimizer to simplify an execution plan based on the guarantees that uniqueness provides. The index will not be marked as 'used' in this case because it will often not appear in the final execution plan.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 2

Ian_McCann

SSCertifiable

Points: 5163

January 26, 2012 at 4:17 am

#1438672

Thanks Paul, you have saved my sanity.

johnitech.itech Hall of Fame Points: 3252 More actions · Answer 3

:hehe:

Grouping Records

We can use a GROUP BY clause to group records and aggregate values, for example, counting the number of orders placed by a customer. To process a query with a GROUP BY clause, the database will often sort the results on the columns included in the GROUP BY. The following query counts the number of products at each price by grouping together records with the same UnitPrice value.

SELECT Count(*), UnitPrice FROM Products GROUP BY UnitPrice

The database can use the IDX_UnitPrice index to retrieve the prices in order. Since matching prices appear in consecutive index entries, the database is able count the number of products at each price quickly. Indexing a field used in a GROUP BY clause can often speed up a query.

===============================

http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx

Paul White SSC Guru Points: 150467 More actions · Answer 4

johnitech.itech (1/26/2012)
Grouping Records...

Yes all that is true, but the index would appear in the execution plan and be counted in usage stats. The question was if an index could be useful without appearing in the execution plan or being reported in the usage stats DMV.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 5

SQL Kiwi (1/26/2012)
Yes, the mere existence of a *unique* index can be used by the optimizer to simplify an execution plan based on the guarantees that uniqueness provides. The index will not be marked as 'used' in this case because it will often not appear in the final execution plan.

Hi Paul,

Could you provide an example of how the optimizer can utilize knowledge about a unique constraint when generating an execution plan? I don't need a query, just an idea would be enough.

Alex Suprun

Paul White SSC Guru Points: 150467 More actions · Answer 6

Alexander Suprun (1/26/2012)
Could you provide an example of how the optimizer can utilize knowledge about a unique constraint when generating an execution plan? I don't need a query, just an idea would be enough.

Rob Farley has a good example and explanation at http://msmvps.com/blogs/robfarley/archive/2008/11/09/unique-indexes-with-group-by.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi