Francisco Tapia
fhtapia at gmail.com
Sat Jul 26 10:08:55 CDT 2008
This was an excellent explanation of the different concepts for indexes thanks Asger! Sent from my iPod On Jul 26, 2008, at 4:42 AM, "Asger Blond" <ab-mi at post3.tele.dk> wrote: > 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 Arth > ur > 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 Na >> ncy >> 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 > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com >