Post reply

Shiny new server - split temp db files question

  • August 1, 2016 at 9:59 am

    #320209

    Hi,

    We have a brand new server. As part of that server we now have the power/space to split out our Tempdb from one file to 12.

    Question:

    After I split out the tempdb files (12 x 10G files) what happens if there is a transaction that goes over my 10G limit for that tempdb file? Does it automatically use one of the other available tempdb files?

  • August 1, 2016 at 10:21 am

    #1893001

    krypto69 (8/1/2016)

    As part of that server we now have the power/space to split out our Tempdb from one file to 12.

    Why?

    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

  • August 1, 2016 at 11:20 am

    #1893013

    Quick question, have you set the TFs 1117 & 1118?

    😎

  • August 1, 2016 at 5:06 pm

    #1893090

    Eirikur Eiriksson (8/1/2016)

    Quick question, have you set the TFs 1117 & 1118?

    😎

    Setting TF1118 is cool but setting TF1117 comes with some serious problems if you have partitioned tables that use 1 FileGroup per file per partition. You don't necessarily want THOSE files to all be the same size but TF1117 is server wide.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 2, 2016 at 12:06 am

    #1893108

    Jeff Moden (8/1/2016)

    Eirikur Eiriksson (8/1/2016)

    Quick question, have you set the TFs 1117 & 1118?

    😎

    Setting TF1118 is cool but setting TF1117 comes with some serious problems if you have partitioned tables that use 1 FileGroup per file per partition. You don't necessarily want THOSE files to all be the same size but TF1117 is server wide.

    1117 won't make them the same size in that case. It causes all files in a filegroup to grow simultaneously, not all files in a database.

    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

  • August 2, 2016 at 1:12 am

    #1893125

    Without the TF 1117 the splitting of the tempdb into multiple files is likely to be an utterly useless exercise as the load will more or less end up in the one file that grows largest. As it only affects the files in the same file group, it normally has little or no consequences if taken into the account from the beginning i.e. only one file and for Primary etc.

    😎

  • August 2, 2016 at 5:49 am

    #1893186

    Without the TF 1117 the splitting of the tempdb into multiple files is likely to be an utterly useless exercise as the load will more or less end up in the one file that grows largest. As it only affects the files in the same file group, it normally has little or no consequences if taken into the account from the beginning i.e. only one file and for Primary etc.

    Okay now I'm confused. I am seeing some contention in our one tempdb file. So I thought it wold be best to split it out. Not sure how many files, yet.

    I thought this would reduce contention as tempdb is my most heavily on the I/O file. Thought was to split out the I/O.

    Is the use of TF 1117 also needed?

  • August 2, 2016 at 8:13 am

    #1893235

    krypto69 (8/2/2016)

    Without the TF 1117 the splitting of the tempdb into multiple files is likely to be an utterly useless exercise as the load will more or less end up in the one file that grows largest. As it only affects the files in the same file group, it normally has little or no consequences if taken into the account from the beginning i.e. only one file and for Primary etc.

    Okay now I'm confused. I am seeing some contention in our one tempdb file. So I thought it wold be best to split it out. Not sure how many files, yet.

    I thought this would reduce contention as tempdb is my most heavily on the I/O file. Thought was to split out the I/O.

    Is the use of TF 1117 also needed?

    Have you enabled the local policy "perform volume maintenance" for the SQL server service account?

    How many CPUs does the server have?

    What is the current size of the tempdb file(s)?

    The object of multiple files is to alleviate file contention. If you're splitting I\O then the files will need to be placed on separate arrays\drives.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 2, 2016 at 8:42 am

    #1893243

    krypto69 (8/2/2016)

    Without the TF 1117 the splitting of the tempdb into multiple files is likely to be an utterly useless exercise as the load will more or less end up in the one file that grows largest. As it only affects the files in the same file group, it normally has little or no consequences if taken into the account from the beginning i.e. only one file and for Primary etc.

    Okay now I'm confused. I am seeing some contention in our one tempdb file.

    What kind of contention? How are you measuring and what exactly are you seeing?

    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 9 posts - 1 through 8 (of 8 total)

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