?
Solved

why not use the index?

Posted on 2014-12-02
4
Medium Priority
?
168 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 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 66

Expert Comment

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

Accepted Solution

by:
Vitor Montalvão earned 1000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

752 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