Solved

why not use the index?

Posted on 2014-12-02
4
154 Views
Last Modified: 2014-12-24
I have this query
I created an index

no difference analyzing statistics and execution plan, why?
I'm using a table of the AdventureWorks BD

use
AdventureWorks

--1 Obtener plan de ejecucion estimado
set statistics io on
      SELECT TOP 10
      p.ProductID,
      p.[Name],
      p.StandardCost,
      p.[Weight],
      ROW_NUMBER() OVER (ORDER BY p.Name DESC) AS RowNumber
      FROM Production.Product p
      WHERE p. StandardCost >150
set statistics io off

--(10 row(s) affected)
--Table 'Product'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

create NONCLUSTERED index  idx_Producto_StandardCost ON Production.Product (StandardCost)
0
Comment
Question by:enrique_aeo
  • 2
4 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 40477736
The index idx_Producto_StandardCost is for only one column:  StandardCost.

The query references four columns:  ProductId, Name, StandardCost, Weight.

Therefore, the index does not 'cover' all four columns the query needs, i.e. is not a Covering Index, and can't be used by the query.

So, if you want to speed up this query, then the index has to include all four columns.   Keep in mind that this can be at the expense of other SELECT queries, and definately INSERT-UPDATE-DELETEs.
0
 

Author Comment

by:enrique_aeo
ID: 40477765
can you give me T-SQL code please
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40477784
>can you give me T-SQL code please
Explain what you wish to do.
0
 
LVL 47

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40478331
You don't have any physical reads, only logical reads and that means SQL Server engine already has the records in memory so is going directly to cache instead of disk.
Also the number of scan is 1 that means no need for use the index. The engine is smart enough to know if it worthily or not to use an index. With 10 records that may be all in a single page I would say that you need to test it with a lot more data (thousands of records).
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now