How efficient is your covered index?

  • SimonLiew

    SSCertifiable

    Points: 7233

    Comments posted to this topic are about the item How efficient is your covered index?

    Simon Liew
    Microsoft Certified Master: SQL Server 2008

  • gfey

    Mr or Mrs. 500

    Points: 510

    Hi,

    here a remark to the first solution. All thoughts concer the case of setting @carrier to null.

    Since isnull creates an identity for carriertrackingnumber, the original query will return all rows where the other two criterias match.

    On the other hand the resultset of first solution depends on the option ANSI_NULLS. If the option is set to ON the query returns only rows where carriertrackingnumber is null, otherwise it returns an empty resultset.

    So in case of setting @carrier to null the solution never returns the same result as the original query.

    Now the question is how to change the first solution to get the same behaviour?

    Regargs

    Gregor

  • sulaphen88

    SSC Rookie

    Points: 26

    How efficient is your covered index?

  • curious_sqldba

    SSC-Dedicated

    Points: 36266

    Thank you for sharing this article. Actually i had this question posted in forums also, so far no response, would like to know your thoughts please.

    I have a view which is performing a order by on three columns a/b/c, a/b are indexed and is from table 1, c is from table2. How are the indices used in this case? How does sql use two different indices on two different tables? I am trying not to use indexed views or is indexing the view only way?

  • james.of.rivendell

    Old Hand

    Points: 375

    Pretty sure it's called 'covering index' or less commonly 'cover index', but not 'covered index'. The index is not the one being covered.

  • SimonLiew

    SSCertifiable

    Points: 7233

    gfey (8/12/2013)


    Hi,

    ... On the other hand the resultset of first solution depends on the option ANSI_NULLS. If the option is set to ON the query returns only rows where carriertrackingnumber is null, otherwise it returns an empty resultset. ..

    Regargs

    Gregor

    Hi Gregor,

    Thanks for your comment.

    Microsoft has announced ANSI_NULLS deprecation in SQL 2012. ANSI_NULLS OFF will not be supported in future version of SQL. Hence, I havenโ€™t evaluated this as an option.

    Simon Liew
    Microsoft Certified Master: SQL Server 2008

  • SimonLiew

    SSCertifiable

    Points: 7233

    hi curious_sqldba,

    Its hard to describe your situation because query optimizer chooses a good enough plan depending on circumstance. For example, the number of records returned will have influence whether the query plan should utilize an index or might choose to do a table scan instead.

    If the view is just a standard view containing just the tsql, then it would work very similarly to executing the query directly, except you can't have an ORDER BY in the view unless TOP, OFFSET or FOR XML is also specified.

    I can only describe a hypothetical query below

    select a.SalesOrderID, a.CustomerID, b.CarrierTrackingNumber

    from [Sales].[SalesOrderHeader] a join [Sales].[SalesOrderDetail] b

    on a.SalesOrderID = b.SalesOrderID

    where a.SalesOrderID = 43659

    order by a.SalesOrderID, a.CustomerID, b.CarrierTrackingNumber

    1 record returned from SalesOrderHeader and 12 records returned from SalesOrderDetail. In this case, it would be efficient to perform an index seek on SalesOrderHeader.PK_SalesOrderHeader_SalesOrderID, get SalesOrderID and CustomerID and use this table as a "base". In another word, the "base" table will provide input to the other join table SalesOrderDetail using a physical operation Nested Loops. SalesOrderDetail will use the provided SalesOrderID as a join condition to perform an index seeks on SalesOrderDetail.PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID to get the 12 CarrierTrackingNumber. CarrierTrackingNumber needs to be sorted before the result is combined to the main resultset. Sort is not required for SalesOrderHeader because there's only 1 record returned.

    This is an over-simplified explanation of such operation. I don't think I can cover in more detail in a discussion thread. Hope it helps.

    Simon Liew
    Microsoft Certified Master: SQL Server 2008

  • shankar.k

    Old Hand

    Points: 329

    Hi Simon,

    Thanks for the article. I have a basic question. My understanding of the indexes was to

    > Create index on columns appearing in the WHERE clause

    > If index has to be covering, add the columns appearing in the SELECT clause in the INCLUDED columns.

    Is my understanding correct? Can you please eloborate?

  • Jeff Moden

    SSC Guru

    Points: 994287

    curious_sqldba (8/12/2013)


    Thank you for sharing this article. Actually i had this question posted in forums also, so far no response, would like to know your thoughts please.

    I have a view which is performing a order by on three columns a/b/c, a/b are indexed and is from table 1, c is from table2. How are the indices used in this case? How does sql use two different indices on two different tables? I am trying not to use indexed views or is indexing the view only way?

    I don't want to hi-jack this thread by answering a forum post. I'll take a look at your original post and answer there.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. ๐Ÿ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • SimonLiew

    SSCertifiable

    Points: 7233

    shankar.k (8/12/2013)


    Hi Simon,

    Thanks for the article. I have a basic question. My understanding of the indexes was to

    > Create index on columns appearing in the WHERE clause

    > If index has to be covering, add the columns appearing in the SELECT clause in the INCLUDED columns.

    Is my understanding correct? Can you please eloborate?

    Hi Shankar,

    Your understanding is correct. Covered (or covering) index is a term decribing a certain technique that is used to improve query performance. It is not an index structure. That's why you might come across the term being used to describe numerous scenario as long as it implements the technique.

    Technet article below has explaination on covering index. I have taken a snippet of the explaination

    http://technet.microsoft.com/en-us/library/aa964133(v=sql.90).aspx

    > Create index on columns appearing in the WHERE clause

    - Covering indexes can improve query performance because all the data needed to meet the requirements of the query exists within the index itself. That is, only the index pages, and not the data pages of the table or clustered index, are required to retrieve the requested data; therefore, reducing overall disk I/O. For example, a query of columns a and b on a table that has a composite index created on columns a, b, and c can retrieve the specified data from the index alone.

    http://technet.microsoft.com/en-us/library/jj835095.aspx

    > If index has to be covering, add the columns appearing in the SELECT clause in the INCLUDED columns.

    Performance gains are achieved when the index contains all columns in the query. The query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations. Use index with included columns to add covering columns instead of creating a wide index key.

    Simon Liew
    Microsoft Certified Master: SQL Server 2008

  • shankar.k

    Old Hand

    Points: 329

    Hi Simon,

    Thanks for the reply. This clarifies.

    Cheers,

    Shankar

  • Divine Flame

    SSCoach

    Points: 15941

    Thanks for the article.


    Sujeet Singh

  • SimonLiew

    SSCertifiable

    Points: 7233

    Divine Flame (8/13/2013)


    Thanks for the article.

    You are welcome ๐Ÿ™‚

    Simon Liew
    Microsoft Certified Master: SQL Server 2008

  • romanilic

    Newbie

    Points: 5

    Interesting article.

    I have index with 3 columns.

    I get Index Seek with 2 columns in Seek Predicates.

    And one column in Predicates: [T_Encounter].[sysDeleted] as [e].[sysDeleted]='N'

    There are no hidden implicit conversions. sysDeleted and 'N' are both same type.

    No mater what I do, I can't get sysDeleted column into Seek Predicates?

  • SimonLiew

    SSCertifiable

    Points: 7233

    romanilic (8/13/2013)


    Interesting article.

    I have index with 3 columns.

    I get Index Seek with 2 columns in Seek Predicates.

    And one column in Predicates: [T_Encounter].[sysDeleted] as [e].[sysDeleted]='N'

    There are no hidden implicit conversions. sysDeleted and 'N' are both same type.

    No mater what I do, I can't get sysDeleted column into Seek Predicates?

    Hi romanilic,

    Are you able to post the table+index definition, sample data and the query here for testing?

    Simon Liew
    Microsoft Certified Master: SQL Server 2008

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply