Post reply

Use two temp DBs

  • April 23, 2008 at 12:23 pm

    #189409

    Hello all,

    I thought I read a forum post a while ago that expressed an advantage in SQL Server 2000 to having two temp databases.

    1) Would that help with a system that did a lot of calculations?

    2) If so where can I find sources for setting that up?

    Thanks,

    David

  • April 23, 2008 at 12:30 pm

    #806233

    There is only one tempdb. You can use multiple files, which could potentially provide advantages if there are separate physical devices under each.

  • April 23, 2008 at 12:47 pm

    #806252

    Thanks for the quick reply. Do you mean separate file directories across a raid or SAN?

  • April 23, 2008 at 12:56 pm

    #806255

    Usually its enough to have 2 files on the same drive. If you're seeing IO bottlenecks on that drive, you may want to separate the data files onto different drives.

    It depends on your reasons for splitting tempDB. If you're splitting because of high IO load, then different drives. If you're splitting because you're seeing contention on the allocation pages (which is more common on multi-proc servers with lots of temp table usage) then you just need separate files.

    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

  • April 23, 2008 at 1:19 pm

    #806273

    Thanks Steve and Gail, for the replies,

    David

Viewing 5 posts - 1 through 4 (of 4 total)

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