1

Probably a straight forward answer, but I'm too bleary eyed to see it.

Have a simple query that is pulling the top 100 rows based on a char column.

  • The column has an index on the where column.

  • Returning all the columns (*)

  • There are ~100 millions rows

  • Statistics have been updated

The optimizer thinks a table scan is more efficient than running an index scan.

Sample Table:

CREATE TABLE [dbo].[tst](
[Mon] [char](6) NULL,
COL1 [varchar](50) NULL,
COL2 [varchar](50) NULL,
COL3 [varchar](50) NULL,
COL4 [varchar](50) NULL,
COL5 [varchar](50) NULL
)

Sample Query:

select
   top 100 *
from
   <table>
where
   mon = '201509'

This will take > 30 seconds to return since the optimizer chooses a table scan vs using the index seek. An index scan is < 1 sec.

Interestingly if a symbol is added in the where

e.g. '2015.09' or '2015 09'

It will use the index.

Searching straight '201509', it uses a table scan.

Add in a dot or space, it uses an index scan. Index

Can someone explain to me why the optimizer is choosing a table scan vs using the index?

Edit: Thanks all for the excellent feedback and information. I didn't know about traceflag 9130 and from that I can see the estimated rows are a lot less than the actual rows which is likely why the optimizer is choosing the table scan vs the index.

Traceflag 9130

Here is the Statistics Output: Without index hint:

Table 'TestTable'. Scan count 1, logical reads 982046, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 17437 ms, elapsed time = 17792 ms.

With Index hint:

Table 'TestTable'. Scan count 1, logical reads 104, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 4 ms.

  • 1
    Problem 1. It appears that you do not have a primary key on your table. – byrdzeye Nov 9 '15 at 20:31
  • 4
    your Mon column is a char 6, and 2015.09 is 7 characters. I am guessing the execution plan doing the seek is estimating 0 rows returned while the estimate for 201509 is much higher (the total count of rows that match). i am guessing that many rows match that value so the optimizer chooses to do a scan, instead of seek+lookup – Bob Klimes Nov 9 '15 at 20:34
  • 2
    top 100 without order by doesn't guarantee what 100 you get back. What is the exact number of rows in the whole table that match the mon='201509' condition? If you add querytraceon 9130 what is the estimated and actual rows going into the filter? SQL server will assume that values are evenly distributed through the table. They may not be. – Martin Smith Nov 9 '15 at 20:35
  • 2
    In addition to the other highlighted problems, the index doesn't cover the columns in your query. Scanning the table makes more sense (probably based on estimates) than using the narrower index and then having to go get the rest of the columns from the table anyway. Try changing * to Mon. – Aaron Bertrand Nov 9 '15 at 20:45
  • 1
    You have showed us a fictitious example without much details like indexs. Also, why are you using select top(100) * ? And top without order by clause ? – Kin Shah Nov 9 '15 at 22:13
1

The optimizer is convinced that if it's going to have to go back to the disk for retrieving column data anyway, it might as well scan the table in the first place, since that'll be less work for it to do. It'll use the seek with the CHAR( 7 ) scalar because the statistics for the index know it's not going to find anything, but if data needs to be returned, it has to consider both CPU and I/O weights.

USE tempdb;
GO

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'tst'
                    AND type = 'U' )
BEGIN
    --DROP TABLE dbo.tst;
    CREATE TABLE [dbo].[tst] 
    (
        Mon                     [char](6) NULL,
        COL1                    [varchar](50) NULL,
        COL2                    [varchar](50) NULL,
        COL3                    [varchar](50) NULL,
        COL4                    [varchar](50) NULL,
        COL5                    [varchar](50) NULL
    );

    INSERT INTO dbo.tst ( [Mon] )
    SELECT  TOP 100000000
            CONVERT( CHAR( 6 ), DATEADD( DAY, ( ABS( CHECKSUM( NEWID() ) ) % 10000 + 1 ),
                '20000101' ), 112 )         
    FROM    sys.all_objects so
    CROSS APPLY sys.all_objects sp;

    CREATE NONCLUSTERED INDEX IX__tst__Mon
        ON dbo.tst ( Mon )
    WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 100 );
END;

SELECT  Mon, COUNT( 1 )
FROM    dbo.tst
GROUP BY Mon
ORDER BY Mon;

SET STATISTICS IO, TIME ON;

SELECT  Mon, COL1
FROM    dbo.tst
WHERE   Mon = '201509'

SELECT  Mon, COL1
FROM    dbo.tst WITH ( INDEX = IX__tst__Mon )
WHERE   Mon = '201509'

SELECT  *
FROM    dbo.tst
WHERE   Mon = '201509'

SELECT  *
FROM    dbo.tst WITH ( INDEX = IX__tst__Mon )
WHERE   Mon = '201509'

SET STATISTICS IO, TIME OFF;

Specifying the hint, in both cases, does reduce the time required for the query to resolve, but the index seek + RID lookup actually results in a significant increase in the number of reads necessary ( my test indicated a 60% increase ). Obviously it's not a 1:1 trade off, since the time difference is about 6x, but regardless, the optimizer is choosing the scan instead.

If you can INCLUDE the columns you need in the index, you'll get the best of both worlds, eliminating the RID lookup and the additional reads.

--DROP INDEX dbo.tst.IX__tst__Mon
CREATE NONCLUSTERED INDEX IX__tst__Mon
    ON dbo.tst ( Mon )
INCLUDE ( COL1, COL2, COL3, COL4, COL5 )
WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 100 );

SET STATISTICS IO, TIME ON;

SELECT  *
FROM    dbo.tst
WHERE   Mon = '201509'

SET STATISTICS IO, TIME OFF;
  • 1
    100 Rid lookups will certainly be a lot quicker than scanning an entire 100 million row table though. Looks like in the OP's case it probably ends up scanning a much greater proportion of the table than it estimated it would before finding 100 rows that qualify. Perhaps as the value is statistically quite common but the qualifying rows are clumped together towards the end of the scan. – Martin Smith Nov 9 '15 at 21:21
  • Agreed, even in the smaller test example here the index hint is faster, but the batch weights given by the optimizer are in favour of the scan, regardless ( 30 / 70 split in my test ). OP mentions better time with the seek as well - would be interesting to see SET STATISTICS output messages in his scenario. – Avarkx Nov 9 '15 at 21:24
  • Be careful when you compare difference in speed. Databases are using the cache to store data. The first execution does the physical reads and the second one benefits from the data already in the cache. To compare the response time of 2 SQLs you must make sure that they both use cache or retrieve the data from disk. – Marco Nov 10 '15 at 7:29
  • If he includes all the columns in his index, he's effectively doubling the space of his table on disc. There's certainly a lot of applications where that tradeoff is worth it, but it really doesn't seem to be the point of this question. – Sascha Rambeaud Nov 10 '15 at 10:28
  • @Marco, absolutely true! The 30 / 70 weight estimation split is independent of logical / physical reads of course, and the 6x difference was against data already in the cache - CHECKPOINT; DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE somewhere that isn't production for the example and you can see the the non-hinted query perform better ( adhering to the optimizer's decision! ) – Avarkx Nov 10 '15 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.