SQL Server Execution Plans - Grant Fritchey

  • bpw

    Old Hand

    Points: 342

    As a .NET web-developer, I find myself producing lots of stored procedures and functions (SQL Server 2012). I know my understanding of query performance issues could/should be better (we don't have a DBA), so I decided to work through the above book. I confess to finding some of the concepts tricky, so I thought I might ask for clarification on issues as I come across them.

    On page 71, the following simple example is given (on the AdventureWorks database - 2012 in my case).

    SELECT ct.ContactTypeId

    FROM Person.ContactType AS ct

    WHERE Name LIKE 'Own%'

    Primary key, clustered - ContactTypeId

    Unique, non-clustered - Name

    The above query resulted in a non-clustered index seek, which I understand. As a way of learning, I play around with the query and try to predict what will happen. I removed the WHERE clause from the above and predicted a clustered index scan, as the clustered index would be the only index referenced. It produced a non-clustered index scan, which I don't understand.

    Can anyone shed any light?

    Paul

  • Bhushan Kulkarni

    SSCrazy

    Points: 2829

    As non clustered index contain Index Search key (Name) and clustered index key (ContactTypeId) in its leaf node.

    SQL Server will scan Non clustered index.

    Please refer to http://www.sqlservercentral.com/stairway/72399/

    to learn about clustered and non clustered indexes

  • bpw

    Old Hand

    Points: 342

    I understand clustered and non-clustered indexes.

    The Name column is not being referenced in the query, so why would SQL Server reference the non-clustered index on the Name column?

  • ChrisM@Work

    SSC Guru

    Points: 186040

    bpw (2/12/2015)


    I understand clustered and non-clustered indexes.

    The Name column is not being referenced in the query, so why would SQL Server reference the non-clustered index on the Name column?

    Imagine the table contains 200 wide columns. What would be quicker, reading the index - which contains the two referenced columns, or the clustered index?

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • bpw

    Old Hand

    Points: 342

    But there aren't two referenced columns, the modified query is only referencing the primary-key column - hence my question.

  • ChrisM@Work

    SSC Guru

    Points: 186040

    bpw (2/12/2015)


    But there aren't two referenced columns, the modified query is only referencing the primary-key column - hence my question.

    Point taken, sorry.

    Ok try this: what two columns does the ordinary index contain?

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • bpw

    Old Hand

    Points: 342

    I'm not sure I understand the question. By 'ordinary' do you mean non-clustered?

    I understand that all items in a non-clustered index will include a reference to the related item in the clustered index (assuming one exists).

    I was assuming that the quickest route with this simple query would be to return the clustered index, rather than use the non-clustered index to return the related keys.

    I'm sure the issue is not crucial, but I wondered whether I was missing something fundamental.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    All nonclustered indexes contain the clustered index key.

    Hence, that nonclustered index on Name contains both Name and ContactTypeId

    Your query has no where clause, hence there's no possibility for anything other than a scan. The only column it needs is ContactTypeId, which is present both in the clustered index and in the nonclustered index (because it's the clustered index key)

    Which is more efficient, scan the table (the clustered index), which contains about 8 columns iirc, or scan the nonclustered index which contains two columns only?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 8 (of 8 total)

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