Post reply

SQL Server Execution Plans

  • November 11, 2011 at 6:35 am

    #1406210

    Hi Grant,

    One question about the book:

    You refer a couple of times to indexed views that "only refresh once per day". My understanding was that indexed views are updated instantly when base table data is updated, and I can only find references to Oracle materialised views having this "refresh" function. Is there something I am missing?

    Thanks

  • November 11, 2011 at 6:42 am

    #1406222

    Laurence Neville (11/11/2011)

    Hi Grant,

    One question about the book:

    You refer a couple of times to indexed views that "only refresh once per day". My understanding was that indexed views are updated instantly when base table data is updated, and I can only find references to Oracle materialised views having this "refresh" function. Is there something I am missing?

    Thanks

    It's probably something like a temp reporting table loaded from a view.

    Your understanding of the indexed views is correct.

  • November 14, 2011 at 2:33 am

    #1406999

    Grant, I confused for a question when I'm reading <Dissecting SQL Server

    Execution Plans>:

    Following two T-SQL generate two different plans, but I don't know why:

    --Clustered index scan

    SELECT ContactID,LastName,EmailAddress FROM person.Contact WHERE EmailAddress LIKE 'sa%'

    --NonClustered index seek and Key Lookup

    SELECT ContactID,LastName,EmailAddress FROM person.Contact WHERE EmailAddress LIKE 'sab%'

    Would you give me some suggestion please?

    Thanks a lot.

    s.pig.snow@gmail.com

  • November 14, 2011 at 4:21 am

    #1407052

    Sab must return less rows.

    An index seek + bookmark lookup is a very costly operation. It takes very little data returned to make a table scan more efficient than a seek + lookup.

  • November 14, 2011 at 4:27 am

    #1407054

    This: http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/

    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

  • November 14, 2011 at 4:33 am

    #1407057

    GilaMonster (11/14/2011)

    This: http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/

    And make sure to read this excellent comment at the end!

    http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Tipping-Point-Query-Answers.aspx

    :hehe:

  • November 15, 2011 at 5:58 am

    #1407782

    Sounds like you guys covered it all better than I would have. Thanks!

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 7 posts - 16 through 21 (of 21 total)

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