Post reply

spreading nc index over more than 1 file

  • December 12, 2011 at 8:25 am

    #249589

    If I have a filegroup with 2 files on different LUNS on a SAN and assign a NC index to that filegroup, would that index exist on both? What would be the advantage(s)?

    There is an exception to every rule, except this one...

  • December 12, 2011 at 8:36 am

    #1420109

    It would be spread across both.

    There may be a performance benefit (or may not)

    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

  • December 12, 2011 at 8:42 am

    #1420116

    GilaMonster (12/12/2011)

    It would be spread across both.

    There may be a performance benefit (or may not)

    Can you be more specific as to why or why not better performance please.

    There is an exception to every rule, except this one...

  • December 12, 2011 at 8:52 am

    #1420127

    Not without writing half a book. There are a lot of factors involved including:

    Whether the DB is IO bottlenecked (and if so, where in the IO subsystem the bottleneck is)

    Access patterns of the index

    Amount of memory

    Likelyhood of the index being in cache

    Layout of the drives on the SAN

    Academic curiosity or planning a design?

    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

  • December 12, 2011 at 9:00 am

    #1420134

    Interview question I hadn't heard before. Thanks.

    There is an exception to every rule, except this one...

  • December 12, 2011 at 9:26 am

    #1420150

    1 more question: would the nc index fully exist in both files or equal/unequal portion on each?

    There is an exception to every rule, except this one...

  • December 12, 2011 at 9:34 am

    #1420153

    Portion in one, portions in the other. If you want the details, read up on proportional fill (it's how SQL allocates pages when there's more than one file in a filegroup)

    Edit: fixed terminology.

    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

  • December 12, 2011 at 9:47 am

    #1420164

    For more information: Using Files and Filegroups. The algorithm for distributing data across files is commonly called proportional fill, and is as Gail stated based on round robin.


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

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

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