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

Recommendations for Tempdb on its own drive Expand / Collapse
Author
Message
Posted Saturday, May 6, 2006 6:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 2007 12:20 AM
Points: 6, Visits: 1
Hi All,

I was posed with the question of placing the tempdb on a seperate drive. For this i need to know how heavily tempdb is used. I have placed some counters like worktables/sec, workfiles/sec etc and i have found that on one of the servers the value has exceeded the threshold value of 20.

Is this the only way on which i can decide the tempdb is used heavily and needs a seperate drive.

Can anyone suggest are there any other ways on which i can decide tempdb is used heavily and need to go for seperate drive

Any thoughts.

Thanks

Post #278154
Posted Saturday, May 6, 2006 10:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
Hard to say.  There could be a lot of factors affecting your server performance.  Putting TempDB on its own drive might help, but I'd there are usually other things that can be done to a server configuration that would help much more than messing with TempDB.  Without knowing more about your configuration it's hard to recommend specifics.
Post #278165
Posted Sunday, May 7, 2006 4:19 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, May 3, 2008 12:33 AM
Points: 95, Visits: 24
Some more tips on tempdb : http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=92&messageid=277442






Get busy living ....or get busy dying....
Post #278186
Posted Sunday, May 7, 2006 9:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

To move TempDB to another device, first put TempDB in RAM, then move it over.  Here's an article:  http://support.microsoft.com/kb/q187824/

Like I said, this would be one of the last things I'd look to.  IMHO, there are a lot of other things that affect performance a lot more than TempDB being on the master device.  If you're going to go through all that, why not consider just putting TempDB in RAM?  http://support.microsoft.com/kb/q115050/

 

Post #278189
Posted Monday, May 8, 2006 7:04 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 3, 2009 6:26 AM
Points: 666, Visits: 26

The OP doesn't state what version of SQL this is for, but as far as I recall, tempdb in RAM hasn't been an option since SQL 6.5.  SQL 2000 BOL states this about the tempdb in RAM option: "No longer supported because SQL Server 2000 has been optimized for maximum performance. "

As far as a separate drive for tempdb, I've had our production environment setup both with a dedicated drive for tempdb and having tempdb on the same partition as our data files.  We didn't see any significant difference either way.

Post #278262
Posted Monday, May 8, 2006 7:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

Oops, brain fart.  Sorry about that.  If you're using SQL 2000 use ALTER DATABASE to move TempDB.  Sorry about that, I had a 6.5 flashback.

But the better option for the OP would probably be to look at other optimizations, like putting the log file on its own physical drive, upgrading hardware, index tuning, etc.  The OP doesn't really state a specific issue he's having or give any details about current specs/configuration, so it's really hard to guess at what's causing him performance issues...  if he's even having performance issues...

Post #278274
Posted Monday, May 8, 2006 8:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:28 PM
Points: 38, Visits: 84

I agree that there are other factors to consider before getting overly worried about TempDB; however, in SQL Server 2005, TempDB is more heavily used than in the past.  This is especially true if new features such as Snapshot Isolation are being used.  There is a Technet webcast entitiled "SQL Server 2005 heavily uses TempDB" or something similar to that which may help.

Hope this helps!




Post #278321
Posted Monday, May 8, 2006 4:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, June 22, 2014 7:57 PM
Points: 39, Visits: 67

I've been told that using a RAID is better as you have more disk heads doing the io and have the fall over safety. We've had the TempDB on a separate drive as well for a while, even put the log files on a separate drive, but it's much esier to manage and safer if they are on the RAID. After a hard drive failure it's a simple recovery.

This is off the plot of how to measure the TempDB usage.

What platform is this on?

Post #278459
Posted Monday, May 8, 2006 5:51 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

Some RAID levels that implement striping can improve performance.  Choosing a RAID level usually involves a trade-off between performance and reliability though.

Putting the log file on a separate drive helps because the log file requires sequential access, where the data file requires random access.  An optimum RAID (performance-wise) solution might be to put your log file on a RAID 1+0 separate from the data files.  You get the performance boost of separating the log files out which reduces head-thrashing, plus a boost from striping of the RAID 1+0 and of course RAID mirroring.  Of course RAIDing out everything costs $$$ which is usually the final limiting factor

Post #278463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse