SQL Server Performance

New Article Series : Benchmarking

Discussion in 'Forum Announcements' started by judehk, Apr 11, 2007.

  1. judehk New Member

    We have just commenced a new article series which will focus on providing hard quantitative evaluations on various sql server server topics.

    The first in the series has been written by Dinesh on Benchmarking Covering Indices.

    Comments and suggestions most welcome.
  2. FrankKalis Moderator

    Good idea!

    You know, one thing in all these benchmarking stuff I believe to be extremely useful is providing a repro script for download so that I can test in my own environment with the same data and benchmark against the article to see if that holds true for me. And make up my own mind.

    One note to the article as such. I believe it to be mainly a question of terminoly, but Bookmark Lookups are not used anymore:
    http://msdn2.microsoft.com/en-us/library/ms180920.aspx
    Quote from the link.

    quote:
    Bookmark Lookup is not used in SQL Server 2005. Instead, Clustered Index Seek and RID Lookup provide bookmark lookup functionality. In SQL Server 2005 Service Pack 2, the Key Lookup operator also provides this functionality
    Now, my understanding is that this is essentially the same under another name, but nonetheless it should be made clear(er).

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  3. bradmcgehee New Member

    Frank, I myself just learned that in 2005 that they are going away from the bookmark lookup language, instead talking about specific types of bookmark looksups. But I have found the language usage to vary widely, even from within Microsoft. So I am a little confused on what the proper terms should be. But I think most DBAs will understand what a bookmark lookup is, even if we now see them with new names.

    --------------------------------
    Brad M. McGehee, SQL Server MVP
    http://www.sqlbrad.com
  4. FrankKalis Moderator

    Yes, I think you're right, Brad. Nonetheless I also think it is important to express things precisely. Especially since Dinesh used SSMS in his article and therefore adressed SQL Server 2005. And the "new official" parlance for SQL Server 2005 is that one quoted from BOL. SQL Server 2005 added and/or changed so many things that it is even more important to get things straight right from the start or it will add to confusion.<br /><br />Btw, just realized that it might be useful to say some words about the specs of the machine used in the testings. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  5. Roji. P. Thomas New Member

    Good Effort. But I am surprised why index intersection and inluded columns are not mentioned.

    For eg. In the following case

    CREATE NONCLUSTERED INDEX IX_Order_Details_Coverindex ON OrderDetails (
    OrderNo,
    ItemCode,
    Qty,
    Price)

    It is ideal to have the last two columns as included columns instead of making them index columns, for the query

    SELECT OrderNo,
    ItemCode,
    Qty,
    Price
    FROM db:confused:rderDetails
    WHERE ItemCode = 'A2-K137-FF1931'
    AND (OrderNo BETWEEN 250000 and 300000)



    Roji. P. Thomas
    SQL Server MVP
    http://toponewithties.blogspot.com

Share This Page