SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recommendations for Tempdb on its own drive


Recommendations for Tempdb on its own drive

Author
Message
Ashwin Reddy
Ashwin Reddy
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 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


Mike C
Mike C
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13760 Visits: 1173
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.
ARUL-285958
ARUL-285958
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 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....
Mike C
Mike C
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13760 Visits: 1173

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/


Christopher House
Christopher House
Say Hey Kid
Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)

Group: General Forum Members
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.


Mike C
Mike C
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13760 Visits: 1173

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


Rick Heiges
Rick Heiges
Old Hand
Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)

Group: General Forum Members
Points: 374 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!





Fred McConville
Fred McConville
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 74

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?


Mike C
Mike C
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13760 Visits: 1173

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


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