Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Regarding tempdb usage Expand / Collapse
Author
Message
Posted Tuesday, December 3, 2013 12:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:31 PM
Points: 4, Visits: 136
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.
Post #1519356
Posted Tuesday, December 3, 2013 2:40 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:43 AM
Points: 246, Visits: 616
Generally, you want to isolate tempdb from everything else.
Post #1519419
Posted Tuesday, December 3, 2013 2:44 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 2008, MVP
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

Post #1519421
Posted Tuesday, December 3, 2013 3:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:31 PM
Points: 4, Visits: 136
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.
Post #1519427
Posted Tuesday, December 3, 2013 10:08 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 2008, MVP
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

Post #1519468
Posted Wednesday, December 4, 2013 1:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:31 PM
Points: 4, Visits: 136
Thanks, Will update you once he implement this tempdb usage.
Post #1519514
Posted Wednesday, December 4, 2013 1:51 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, April 25, 2014 1:07 AM
Points: 699, Visits: 479
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.



Post #1519520
Posted Wednesday, December 4, 2013 6:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:33 AM
Points: 1,361, Visits: 15,264
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.
Post #1519608
Posted Wednesday, December 4, 2013 8:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1519657
Posted Wednesday, December 4, 2013 8:57 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:52 AM
Points: 980, Visits: 1,324
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
Post #1519677
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse