Post reply

Placing tempdb files on a diffrent drive.

  • January 27, 2011 at 8:08 am

    #388723

    Hello,

    I just recently start managing a SQL SERVER 2005 box and they are only are using Tempdb one MDF and offcourse one log file.

    I am asked to create multiple tempdb files ( same as the number of the CPU , which we have 4).

    Right now the files are on E drive.

    I want to put one more mdf/ndf file on e drive and other two files another drive lets say G.

    can I do that ??

    Thanks in advance.

  • January 27, 2011 at 8:15 am

    #1279101

    You can, but it's not necessarily going to benefit you. The 1-file-per-CPU thing is more of an urban legend than a valid solution, in many/most cases.

    Check this for details: http://www.sqlskills.com/blogs/paul/CommonSQLServerMyths.pdf

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 27, 2011 at 8:19 am

    #1279105

    thanks for the fast response,

    Even if it doesnt help( 1 file per core) , but as far as what I know or have read.... placing the files on a seperate drive will help. whats your take on this?

    Thanks

  • January 27, 2011 at 8:23 am

    #1279107

    Maybe. Are you seeing IO bottlenecks on your tempDB? If not, adding files and placing them on multiple drives is not going to help.

    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

  • January 27, 2011 at 8:33 am

    #1279120

    there was a IO bottleneck, and thats especially when tempdb is getting used.. also we had a alert recently that tempdb was reaching its limit.

  • January 27, 2011 at 8:39 am

    #1279125

    What's the RAID level of the drive that TempDB is currently on? What's it sharing with?

    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

  • January 27, 2011 at 9:04 am

    #1279141

    we are using SSD here.

  • January 27, 2011 at 9:19 am

    #1279148

    TempDB is on an SSD drive and you still have IO bottlenecks? What are you doing to it?

    What lead you to diagnose IO bottlenecks?

    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

  • January 27, 2011 at 11:06 am

    #1279248

    We are still trying to figure out what is the reason ,

    increasing the Tempdb files are a way to clear the alert that tempdb is filling up.

    Thanks

  • January 27, 2011 at 11:15 am

    #1279255

    qur7 (1/27/2011)

    increasing the Tempdb files are a way to clear the alert that tempdb is filling up.

    No need, just increase the size of the file. That's just saying that the size of tempDB is not sufficient. (unless there's no more space on the drive it's currently on)

    Again, what is leading to the conclusion that you have an IO bottleneck?

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

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