Post reply

GAM (Sql Server)

  • July 17, 2009 at 4:51 pm

    #136363

    Hi....

    Can you explain about how allocation algorithm for GAM select the extent and allocate if i have 4 data files with same size ?

    Thnks in Advance..

  • July 18, 2009 at 6:02 am

    #1026535

    GAM pages are per file. Each file will have its own GAM pages tracking extent allocation within that file.

    Why are you asking?

    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

  • July 18, 2009 at 6:15 am

    #1026539

    GilaMonster (7/18/2009)

    GAM pages are per file. Each file will have its own GAM pages tracking extent allocation within that file.

    Why are you asking?

    thks for your reply ..

    Yes ..i knew...Reason behind my question is , i am going to create one data file for each cpu processor with equal size for tempdb so how it will chose the data file to allocate extend and improve perfomance ?

  • July 18, 2009 at 6:26 am

    #1026542

    sudhakara (7/18/2009)

    Reason behind my question is , i am going to create one data file for each cpu processor with equal size for tempdb

    what is the relation between number of cpus and datafiles? ALso, what is the relation between your database file size and tempdb db size?

    so how it will chose the data file to allocate extend and improve perfomance ?

    Depends on the table. If all your datafiles belong to same filegroup, the datafiles will be grown proportionally. If they belong to different filegroups, then the table which is growing, its associated file will grow.

    you generally keep ample amount of free space within your data and log files, so allocation of extents doesnt happen all the time so i'm unsure how will it affect performance(except the time your db is seeking for additional space and growing).


    Pradeep Singh

  • July 18, 2009 at 8:12 am

    #1026555

    ps (7/18/2009)

    sudhakara (7/18/2009)

    Reason behind my question is , i am going to create one data file for each cpu processor with equal size for tempdb

    what is the relation between number of cpus and datafiles? ALso, what is the relation between your database file size and tempdb db size?

    so how it will chose the data file to allocate extend and improve perfomance ?

    Depends on the table. If all your datafiles belong to same filegroup, the datafiles will be grown proportionally. If they belong to different filegroups, then the table which is growing, its associated file will grow.

    you generally keep ample amount of free space within your data and log files, so allocation of extents doesnt happen all the time so i'm unsure how will it affect performance(except the time your db is seeking for additional space and growing).

    what is the relation between number of cpus and datafiles?

    here is the answer for your question .....

    http://sql-server-performance.com/Community/forums/p/27832/149917.aspx

    ALso, what is the relation between your database file size and tempdb db size?

    http://msdn.microsoft.com/en-us/library/ms175527.aspx

  • July 18, 2009 at 8:40 am

    #1026559

    sudhakara (7/18/2009)

    i am going to create one data file for each cpu processor with equal size for tempdb so how it will chose the data file to allocate extend and improve perfomance ?

    That's a recommendation for TempDB only and it's only necessary if you're seeing contention on the allocation pages (often shown as a page latch on 2:1:3). It's also not always necessary to create the full number, often 1 file per 2 CPUs is adequate and no more than 8 should be created

    It's not the GAM pages that are the problem if you have contention, it's the SGAM pages.

    For tempDB and only tempDB, the schedulers can have affinity to certain files, so if you have 8 processor cores and 4 files, the first two cores will create temp objects in the first file, the 3rd and 4th schedulers in the second file, etc. This does not happen with user databases.

    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

  • July 18, 2009 at 1:51 pm

    #1026604

    GilaMonster (7/18/2009)

    sudhakara (7/18/2009)

    i am going to create one data file for each cpu processor with equal size for tempdb so how it will chose the data file to allocate extend and improve perfomance ?

    That's a recommendation for TempDB only and it's only necessary if you're seeing contention on the allocation pages (often shown as a page latch on 2:1:3). It's also not always necessary to create the full number, often 1 file per 2 CPUs is adequate and no more than 8 should be created

    It's not the GAM pages that are the problem if you have contention, it's the SGAM pages.

    For tempDB and only tempDB, the schedulers can have affinity to certain files, so if you have 8 processor cores and 4 files, the first two cores will create temp objects in the first file, the 3rd and 4th schedulers in the second file, etc. This does not happen with user databases.

    1) Do you think creating data files equalent to Cpu Processor will create problem ? or will not readuce the contention ?

    2)Can you explain the problem that occur on the SGAM pages (Contention point of view) ?

    3) I am going to implent this for only tempdb not for use data bases.

  • July 18, 2009 at 2:33 pm

    #1026606

    sudhakara (7/18/2009)

    1) Do you think creating data files equalent to Cpu Processor will create problem ? or will not readuce the contention ?

    It'll help, but more than 8 files is overkill. SQL Customer Advisory team did tests. No improvement in anything after 8 files

    2)Can you explain the problem that occur on the SGAM pages (Contention point of view) ?

    When a mixed extent is allocated, an exclusive latch is needed on the SGAM page. When multiple allocations are donw rapidly from mixed extents (which happens in tempDB when there's lots of temp table usage), there can be blocking relating to that latch.

    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

  • July 18, 2009 at 3:02 pm

    #1026609

    GilaMonster (7/18/2009)

    sudhakara (7/18/2009)

    1) Do you think creating data files equalent to Cpu Processor will create problem ? or will not readuce the contention ?

    It'll help, but more than 8 files is overkill. SQL Customer Advisory team did tests. No improvement in anything after 8 files

    2)Can you explain the problem that occur on the SGAM pages (Contention point of view) ?

    When a mixed extent is allocated, an exclusive latch is needed on the SGAM page. When multiple allocations are donw rapidly from mixed extents (which happens in tempDB when there's lots of temp table usage), there can be blocking relating to that latch.

    thanks for your reply ....

    1) what will happen if i does not create all the tempdb data file with same size ?

  • July 20, 2009 at 1:12 pm

    #1027252

    sudhakara (7/18/2009)

    GilaMonster (7/18/2009)

    sudhakara (7/18/2009)

    1) Do you think creating data files equalent to Cpu Processor will create problem ? or will not readuce the contention ?

    It'll help, but more than 8 files is overkill. SQL Customer Advisory team did tests. No improvement in anything after 8 files

    2)Can you explain the problem that occur on the SGAM pages (Contention point of view) ?

    When a mixed extent is allocated, an exclusive latch is needed on the SGAM page. When multiple allocations are donw rapidly from mixed extents (which happens in tempDB when there's lots of temp table usage), there can be blocking relating to that latch.

    thanks for your reply ....

    1) what will happen if i does not create all the tempdb data file with same size ?

    can any one give detail expalantion on this ?

Viewing 10 posts - 1 through 9 (of 9 total)

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