SQL Server – Index Skip Scan

Background

Writing another post and ran into comments made by Joe Chang.  And, when Joe Chang talks I tend to listen.  He is an engineer’s engineer.

Here is what Joe has to say about Storage:

Storage Performance for SQL Server
Link

There is a perplexing lack of material on overall storage performance strategy for database servers. Too much of the content is narrowly focused on tuning techniques to improve performance over an unknown base. First, storage system IO performance is well understood from the science and engineering perspective. Second there is sufficient information on SQL Server IO patterns. It follows that the overall storage performance objective is then to achieve the proper balance between capability and cost. Storage performance is achieved by distributing load across several disk controllers, and a (very) large number of disk drives. After which, the storage capacity is likely to be many times larger than the database. Any document that only discusses sizing storage to meet the database space requirement is written by someone who should not be giving advice on database performance. A key element in this storage performance strategy is keeping the amortized cost per disk reasonable. Testing and tuning is the final step to verifying that the storage system performance meets the design specification and requirements.

 

To me engineers tend to like straight forward problems.  Once problems start getting a bit muddled, beyond 1’s and 0’s, engineers intuitively turn off.

And, so though writing another post, was more satisfied reading Joe’s.

Joe Chan on Index Skip Scan

Index Skip Scan
Link

There is a feature, called index skip scan that has been in Oracle since version 9i. When I came across this, it seemed like a very clever trick, but not a critical capability. More recently, I have been advocating DW on SSD in appropriate situations, and I am thinking this is now a valuable feature in keeping the number of nonclustered indexes to a minimum.

Briefly, suppose we have an index with key columns: Col1, Col2, in that order. Obviously, a query with a search argument (SARG) on Col1 can use this index, assuming the data distribution is favorable. However, a query with the SARG on Col2 but not Col1 cannot use the index in a seek operation.

Now suppose that the cardinality of Col1, (the number of distinct values of Col1), is relatively low. The database engine could seek each distinct first value of Col1 and the specified SARG on Col2. Microsoft SQL Server currently does not have the Oracle Index Skip-Scan feature.

ORACLE

Here is Oracle’s Take:

Oracle 9i – Database Performance Tuning Guide
Link

Index Skip Scans

Index skip scans improve index scans by non-prefix columns. Often, scanning index blocks is faster than scanning table data blocks.

Skip scanning lets a composite index be split logically into smaller sub-indexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.

The number of logical sub-indexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the non-leading key of the index.

 

Microsoft Connect

As of March 2013 (  Microsoft SQL Server 2012 ), Microsoft does not generally support this feature.

There is connect item:

Implement Index Skip Scan (ID : 695044)
Link

I posted a comment on Joe’s blog:

I like your (Joe Chang) frustration about certain things.  And, how you take the minimal, engineering approach to problem isolation and solving.

And, turn around and ask each of us, have you thought about this problem, as well.  And, how did you solve it — By adding another index?

… Please vote for the connect item

References

  1. Oracle Index Skip Scan
    Link
  2. Oracle Database Performance Tuning Guide
    Link
  3. Oracle-Base –> Index Skip Scanning
    Link
  4. Implement Index Skip Scan
    Link
  5. Does SQL Server jump when using a composite index
    Link
  6. Partitioned Indexes in SQL Server 2008
    Link
  7. Query Processing Enhancements on Partitioned Tables and Indexes
    Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s