Post reply

Adding ndf files to tempdb and shrinking the mdf

  • November 17, 2015 at 7:23 am

    #322670

    Hi

    I have a tempdb with 130 GB mdf and 6 GB ldf file size. Now because of PAGEIOLATCH_XX wait types, I have to a make the number of data files as 8.

    So my plan is to add another 7 data files , each with 17 GB size and then shrink the original mdf file from 130 GB to 17 GB.

    Is this a good way to do it? This is a live production server, so Do I need a restart of SQL Service?

    What other precautions should I take while playing with tempdb?

    thanks in advance

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • November 17, 2015 at 8:25 am

    #1840361

    Just adding data files is not going to fix PageIOLatch waits, unless the files are spread across multiple independent drives/IO channels.

    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 17, 2015 at 9:56 am

    #1840400

    S_Kumar_S (11/17/2015)

    Hi

    I have a tempdb with 130 GB mdf and 6 GB ldf file size. Now because of PAGEIOLATCH_XX wait types, I have to a make the number of data files as 8.

    So my plan is to add another 7 data files , each with 17 GB size and then shrink the original mdf file from 130 GB to 17 GB.

    Is this a good way to do it? This is a live production server, so Do I need a restart of SQL Service?

    What other precautions should I take while playing with tempdb?

    thanks in advance

    resize the file and restart the instance, but as Gail has said to help the latch waits you need multiple drives

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

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

  • November 17, 2015 at 5:23 pm

    #1840505

    S_Kumar_S (11/17/2015)

    Hi

    I have a tempdb with 130 GB mdf and 6 GB ldf file size. Now because of PAGEIOLATCH_XX wait types, I have to a make the number of data files as 8.

    So my plan is to add another 7 data files , each with 17 GB size and then shrink the original mdf file from 130 GB to 17 GB.

    Is this a good way to do it? This is a live production server, so Do I need a restart of SQL Service?

    What other precautions should I take while playing with tempdb?

    thanks in advance

    With a 130GB TempDB and a bunch of PageIOLatch_XX wait types, it sounds to me like you're trying to fix the wrong problem. You should be trying to find the code that does that as well as expanding TempDB and fix that, instead.

    --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)

  • November 18, 2015 at 5:58 am

    #1840619

    Hi Gail

    Thanks for your reply. But Microsoft suggests that having multiple files is good without the mention that they need to be on different drives. I even read a post from Brent, which also doesn't specifically mention that it needs to be on different drives. Here is the link for reference:

    http://www.brentozar.com/blitz/tempdb-data-files/

    And to be more specific, I have both PAFELETCHIO as well as PAGELATCH waits .

    Thanks

    GilaMonster (11/17/2015)

    Just adding data files is not going to fix PageIOLatch waits, unless the files are spread across multiple independent drives/IO channels.

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • November 18, 2015 at 6:13 am

    #1840623

    Having multiple files on the same drive will alleviate PageLatch waits on the allocation structures in TempDB. That's why it's recommended. It will NOT help with PageIOLatch waits, as those are IO waits. Multiple files on the same drive are still on the same drive and hence it's still one drive's IO throughput.

    If you are IO bottlenecked (lots of PageIOLatch_XX waits), you need to either reduce the load on TempDB or increase the IO bandwidth. That could be by moving TempDB to an SSD or creating additional files on *different* physical drives (depending on the IO subsystem and where the bottleneck is)

    You need to make sure you understand why recommendations, like the multiple files, exist before implementing them, or you could end up wasting time and not fixing your problem

    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 6 posts - 1 through 5 (of 5 total)

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