Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Regarding tempdb usage


Regarding tempdb usage

Author
Message
nagasai88
nagasai88
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 324
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.
tim_harkin
tim_harkin
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 898
Generally, you want to isolate tempdb from everything else.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47299 Visits: 44392
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
nagasai88
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 324
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47299 Visits: 44392
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
nagasai88
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 324
Thanks, Will update you once he implement this tempdb usage.
Knight
Knight
Right there with Babe
Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)

Group: General Forum Members
Points: 737 Visits: 535
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
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45173 Visits: 39925
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 "GBigGrinrive 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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Kurt W. Zimmerman
Kurt W. Zimmerman
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1062 Visits: 1396
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search