SQL Server Performance

Clustered Index Seek vs. Clustered Index Scan

Discussion in 'Performance Tuning for DBAs' started by NeilA, Oct 3, 2004.

  1. NeilA New Member

    Can someone explain the difference between a clustered index seek and a clustered index can? Which is faster, why is one chose over the other etc.

    thanks in advance.

    Neil.
  2. Twan New Member

    A clustered indexed seek won't read the bottom level pages of the tree structure, it will only traverse the intermediate pages itself. All of the info that SQL needs has to either be in the index columns, or the number of rows retrieved has to be so small that SQL regards a clustered index seek followed by a bookmark lookup a better bet than a clustered index scan.

    a clustered index scan is essentially a table scan.

    Cheers
    Twan
  3. chopeen Member

    quote:Originally posted by Twan

    a clustered index scan is essentially a table scan.
    Yes, but a clustered index scan is faster than a table scan, because there are usually more rows (OK, I know they are not exactly rows, but you get the idea) stored in one page of index data that in one page of table data.

    --

    Marek Grzenkowicz
  4. Twan New Member

    Hi Marek,

    I think that an index scan reads the bottom level pages only, for a clustered index this is a table scan, the intermediary pages (which only have the indexed columns) are not read. The bottom level pages have all of the table data and are the same structure as the data pages of a table without a clustered index

    Cheers
    Twan
  5. satya Moderator

    In general, the seek (direct access to qualifying pages) will be done on a multi-column condition for which the initial columns are equalities, and the last is non-equality.

    The more information on this topic can get from Kalen's book Inside SQL Server 2000.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. FrankKalis Moderator

    quote:Originally posted by chopeen


    quote:Originally posted by Twan

    a clustered index scan is essentially a table scan.

    Yes, but a clustered index scan is faster than a table scan, because there are usually more rows (OK, I know they are not exactly rows, but you get the idea) stored in one page of index data that in one page of table data.
    Keep in mind that the leaf level of a clustered index is the data itself!
    A clustered index scan is a table scan in disguise. It might only be faster when you retrieve the rows in sort order of the clustered index keys.

    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------
  7. chopeen Member

    quote:Originally posted by FrankKalis

    Keep in mind that the leaf level of a clustered index is the data itself!
    A clustered index scan is a table scan in disguise. It might only be faster when you retrieve the rows in sort order of the clustered index keys.

    Yes, you're right. All this time I was thinking about 'index scan' (nonclustered index scan to be precise) and writing about 'clustered index scan'.

    This Q&A is interesting:
    http://www.sql-server-performance.com/q&a91.asp

    --

    Marek Grzenkowicz
  8. anilksharma99 New Member

    Hi Chopeen,
    Could you provide/forward any link for more information, how SQL server decides between Index Scan instead of Index Seek(Non Clustered) and how I can tune my queries to use Index Seek.

    Thanks
  9. chopeen Member

    quote:Originally posted by anilksharma99

    Could you provide/forward any link for more information, how SQL server decides between Index Scan instead of Index Seek(Non Clustered) and how I can tune my queries to use Index Seek.

    Take a look at this articles. They are rather about query tuning than SQL Server optimizer engine.

    http://www.sql-server-performance.com/query_execution_plan_analysis.asp
    http://www.sql-server-performance.com/indexes_not_equal.asp
    http://www.sqlservercentral.com/columnists/lPeysakhovich/indexcreationguidelines.asp
    http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=93
    http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=94
    http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=95

    --

    Marek Grzenkowicz
  10. FrankKalis Moderator

  11. anilksharma99 New Member

    Thanks Chopeen and Frank.

    Anil

Share This Page