Regarding tempdb usage

Question

Post reply

Regarding tempdb usage

nagasai88

Grasshopper

Points: 16
More actions
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.

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

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

tim_harkin SSCrazy Points: 2399 More actions · Answer 1

Generally, you want to isolate tempdb from everything else.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

nagasai88 Grasshopper Points: 16 More actions · Answer 3

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

nagasai88 Grasshopper Points: 16 More actions · Answer 5

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

Knight SSCommitted Points: 1856 More actions · Answer 6

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.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 7

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.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 8

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)

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 9

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

kevaburg SSCoach Points: 18131 More actions · Answer 10

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.

kevaburg SSCoach Points: 18131 More actions · Answer 11

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.

Knight SSCommitted Points: 1856 More actions · Answer 12

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

kevaburg SSCoach Points: 18131 More actions · Answer 13

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.

Knight SSCommitted Points: 1856 More actions · Answer 14

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