Post reply

Regarding tempdb usage

  • December 3, 2013 at 12:12 pm

    #279577

    Hi,

    I need some help in tempdb files usage by user databases, I have two tempdb files, one in F drive(.ndf file) and another in G drive(.mdf file) , and my databases are spreaded across F drive and G drive, So my question is there anyway I can setup like F drive user databases use F drive temdb file and G drive databases use G drive tempdb files.

    Thanks.

  • December 3, 2013 at 2:40 pm

    #1670547

    Generally, you want to isolate tempdb from everything else.

  • December 3, 2013 at 2:44 pm

    #1670549

    nagasai88 (12/3/2013)

    So my question is there anyway I can setup like F drive user databases use F drive temdb file and G drive databases use G drive tempdb files.

    No.

    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

  • December 3, 2013 at 3:04 pm

    #1670553

    Thanks,

    Even I know we cannot do like this, but Sr. SQL developer in our company said that he had seen this kind of implemenation in his previous company so now he want's to implement same for CRM databases.

  • December 3, 2013 at 10:08 pm

    #1670584

    nagasai88 (12/3/2013)

    but Sr. SQL developer in our company said that he had seen this kind of implemenation in his previous company so now he want's to implement same for CRM databases.

    If he's seen it done, then ask him how to do it.

    But you can't do it. TempDB is a shared 'scratch-space' for SQL Server, there's only one TempDB database and all processes use that one database. It should ideally be on a separate set of drives because it's often heavily used and so can impact user database performance.

    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

  • December 4, 2013 at 1:26 am

    #1670620

    Thanks, Will update you once he implement this tempdb usage.

  • December 4, 2013 at 1:51 am

    #1670625

    Are you sure this senior dev doesn't mean something like a work or staging database? i.e. something pre-production and isolated on a different drive? That I could understand.

    As Gail states, tempdb is a server resource db. You can't have more than one of them.

  • December 4, 2013 at 6:48 am

    #1670699

    As already stated, the tempdb is instance specific.

    The only way to do something similar is to have 2+ instances and isolate them onto their own drives. You would still be better off isolating Tempdb away from any userdbs.

  • December 4, 2013 at 8:24 am

    #1670742

    nagasai88 (12/4/2013)

    Thanks, Will update you once he implement this tempdb usage.

    Even if this could be done (and I'm sure it can't be done but will bow deeply if proven wrong), why would anyone want to cripple TempDB in such a fashion. If the file on F: isn't being used at a given moment, why shouldn't the "G:Drive Users" be allowed the performance benefit of using it?

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

  • December 4, 2013 at 8:57 am

    #1670759

    If I were you I'd poke around looking for an SOP for setting up tempdb. It is worth reading and you will learn a lot. I'd also suggest you share it with your SR. DB developer with the hope he can learn a thing or two.

    Rule of thumb here, which has been outlined by previous posts is to completely isolate tempdb from the rest of your database log and system drives. I say this because the default SQL install will set the default drive for tempdb to the C: drive.

    Since all of my SQL Servers are in a VM environment I request 2 separate LUNs for tempdb, one for data and one for logs. I also request they be set up as RAID 1/0 vs. RAID 5 to gain maximum performance. Finally I create multiple files based upon how many CPU cores there are in the box, preallocating space in each data file along with log file.

    I may over provision tempdb, but I have yet run into a problem. By the way, I've based my tempdb provisioning on various best practices & SOPs that I've acquired over time.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • December 6, 2013 at 3:58 am

    #1671436

    nagasai88 (12/3/2013)

    Thanks,

    Even I know we cannot do like this, but Sr. SQL developer in our company said that he had seen this kind of implemenation in his previous company so now he want's to implement same for CRM databases.

    I doubt that very much. More likely there were two instances each with their own TEMPDB on separate volumes.

  • December 6, 2013 at 3:59 am

    #1671438

    Knight (12/4/2013)

    Are you sure this senior dev doesn't mean something like a work or staging database? i.e. something pre-production and isolated on a different drive? That I could understand.

    As Gail states, tempdb is a server resource db. You can't have more than one of them.

    It isn't a server resource DB, rather an instance resource. One TEMPDB per instance.

  • December 6, 2013 at 4:00 am

    #1671439

    I know that 🙂 ok fussy, instance resource! happy now 🙂

  • December 6, 2013 at 4:05 am

    #1671443

    Knight (12/6/2013)

    I know that 🙂 ok fussy, instance resource! happy now 🙂

    Sorry......a little bit of pendantism coming through! The only reason I brought it up is to prevent the OP thinking the the entire server only uses one TEMPDB. Maybe that is where their confusion originally lay.

  • December 6, 2013 at 4:08 am

    #1671446

    haha no worries, I guess with the multi-national nature of this site it is well worth remembering to be absolutely clear on terminology 🙂

Viewing 15 posts - 1 through 14 (of 14 total)

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