Solved

why not use the index?

Posted on 2014-12-02
4
156 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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

840 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