Asger Blond
ab-mi at post3.tele.dk
Sat Jul 26 06:42:23 CDT 2008
Actually you have tree distinct choices: a composite index containing multiple columns, several separate one-column indexes, and an index with included columns. The choice you make have different impact on read versus write operations. First let's look at the index-physics: In an index with included columns the included columns are not stored on the non-leaf level pages of the index, they are only stored on the leaf level index pages. Therefore the query engine can't use included columns for search operations, but it certainly can use them to avoid bookmark lookups. Avoidance of bookmark lookups is what the term "covering index" is about: If all the data you are referencing in your query are present in the index, then the engine can fetch the data from the index itself and don't need to go to data pages outside the index. In both a one-column index and a multi-column index the indexed column-values are present on the leaf level as well as on the non-leaf level. Therefore the query engine can use the values both for its search and for its data fetch operations. The difference between using multiple one-column indexes and using a composite index with multiple columns is this: In a composite index column order is crucial, whereas this per definition isn't the case for one-column indexes. Suppose you have a composite index on column a, b, and c (in that order) and you then make a query with either 1) WHERE colA = @colA and colB = @colB and colC = @colC or 2) WHERE colC = @colC and colB = @colB and colA = @colA. The order of the columns in your condition is of no importance here: the query engine can effectively make use of the composite index in both cases. But now suppose that you make a query with a condition not using colA, i.e. 1) WHERE colB = @colB or 2) WHERE colC = @colC or 3) WHERE colB = @colB and colC = @colC. For these conditions the query engine can't use the composite index for its search operation. If instead you had created tree separate one-column indexes for column a, b, and c, then the engine could use the relevant indexes. Then what are the pros for a composite index? If the multi-column condition in your query adhere to the column order of the composite index, then a composite index is faster than tree separate indexes, because the engine only have to traverse one index three, whereas tree separate indexes would require tree traverses and a subsequent combination of the results. Also if you do an ORDER BY colA, ColB, colC - then the composite index is by far the most efficient. And last: what are the pros for an index with included columns? If some columns in your query are only used in the SELECT part, never or seldom in the WHERE or ORDER BY part, then an index including these columns could be the best choice: the storage engine wouldn't have to write the values on the index's non-leaf level pages, making the index smaller and not so exposed to page-splits. HTH Asger PS: I'm just leaving for vacation, visiting my daughter in Geneva, and won't be reading mails for the next week. -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Arthur Fuller Sendt: 26. juli 2008 10:19 Til: Discussion concerning MS SQL Server Emne: Re: [dba-SQLServer] Covering Indexes Perhaps you can help me understand the distinction between columns indexed and columns included. Frankly I don't understand the difference. I could for example create an index on PK column, and decide to include columns a, b, c. What does that actually mean? Am I better off creating separate indexes for a, b and c? Or should I include them? Let's suppose that a typical query does Where colA = @colA and colb = @colb and colC = @colC. Some of the reading I've done suggests that the index ought to be ordered in terms of frequency of hits. (i.e. a column called IsActive which is a bit column should be the last examined) -- this says look for the least frequency first (Portfolo_ID for example) and then include IsActive. I dont' yet understand the difference between declaring a column as part of an index and including a column. If you have any insights into this distinction they would be most appreciated. Thanks! A. On Fri, Jul 25, 2008 at 8:32 PM, Asger Blond <ab-mi at post3.tele.dk> wrote: > Agree. > Elaborating this: > Planning an index you have to distinguish two separate operations performed > by the query engine: criteria search and data capture. > For criteria search, column order is essential: if you create a compound > index then always place the supposed search-column in front. > For data capture, covering index is essential: a covering index means that > the wanted data can be captured inside the index, not forcing the engine to > make a bookmark lookup to the data pages outside the index. > To make the scenario more fascinating Arthur also mentioned an > index-creature called "included column". This is new to SQL Server 2005: an > included column is added to the leaf level of the index. This means that > the > included column will aid the possibility of our index being a "covering > index" for queries, but it will lend no assistance in the search operation. > > Conclusion: For compound indexes, always place the supposed search-column > in > front. For includes columns, don't expect a performance gain for search > operations, but enjoy a performance plus for data capture. > > Asger > -----Oprindelig meddelelse----- > Fra: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Nancy > Lytle > Sendt: 23. juli 2008 20:14 > Til: Discussion concerning MS SQL Server > Emne: Re: [dba-SQLServer] Covering Indexes > > Yes, my understaning is that column order does matter.Nancy Lytle > N_Lytle at terpalum.umd.edu > > > > EMAILING FOR THE GREATER GOODJoin me > > > Date: Wed, 23 Jul 2008 14:32:32 -0300> From: fuller.artful at gmail.com> > To: > dba-sqlserver at databaseadvisors.com> Subject: [dba-SQLServer] Covering > Indexes> > I have always thought that you should have an index for the PK > and every FK,> and perhaps also one or more compound indexes for frequent > searches. But> recent reading has given me pause, in particular the topic > of > covering> indexes to avoid bookmark lookups, and also of the order of > columns in an> index. For example, here are the existing indexes on one of > my tables:> > Index Columns Included Columns PK_BESTTrade BESTTrade_ID> > IX_BESTTrade__BSID_PID_Act_BIID_BA_ID BESTSecurity_ID TransactionStatus_ID> > Portfolio_ID EstimatedShares> IsActive QuantityExecuted> BESTIdea_ID> > > BESTAction_ID> IX_BESTTrade__PID_Act_BAID_BSID_BIID Portfolio_ID > TransactionStatus_ID> IsActive SizeEuro> BESTAction_ID EstimatedShares> > BESTSecurity_ID QuantityExecuted> BESTIdea_ID> > IX_BESTTrade__PID_BAID_BIID_Act_TSID Portfolio_ID Currency_ID> BESTAc! > tion_ID SizeEuro> BESTIdea_ID Consideration> IsActive> > > TransactionStatus_ID> > Given the redundancies (columns repeated in a > different order) in the> second, third and fourth indexes listed, I'm > wondering whether it might make> more sense to have just one index besides > the PK that contains and includes> one instance of each column mentioned. > Another way to ask the question is,> does column order in an index matter? > Won't the cost analyzer figure out> that it can make use of the index > anyway?> > Arthur> _______________________________________________> > dba-SQLServer mailing list> dba-SQLServer at databaseadvisors.com> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver> > http://www.databaseadvisors.com> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com