Microsoft – SQL Server – Query Optimization – Index Skip Scan
Writing another post and ran into some comments about 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
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.
Index Skip Scan
There is a feature, called index skip scan that has been in Oracle since version 9i. When I 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.
Here is Oracle’s Take:
Oracle 9i – Database Performance Tuning Guide http://docs.oracle.com/cd/B10501_01/server.920/a96533/optimops.htm#51553
Index Skip Scans
Index skip scans improve index scans by nonprefix columns. Often, scanning index blocks is faster than scanning table data blocks.
Skip scanning lets a composite index be split logically into smaller subindexes. 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 subindexes 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 nonleading key of the index.
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)
As 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
- Oracle Index Skip Scanhttp://sqlblog.com/blogs/joe_chang/archive/2011/06/13/oracle-index-skip-scan.aspx
- Oracle Database Performance Tuning Guidehttp://docs.oracle.com/cd/B10501_01/server.920/a96533/optimops.htm#51553
- Oracle-Base –> Index Skip Scanninghttp://www.oracle-base.com/articles/9i/index-skip-scanning.php
- Implement Index Skip Scan
- Does SQL Server jump when using a composite index
- Partitioned Indexes in SQL Server 2008
- Query Processing Enhancements on Partitioned Tables and Indexes