Recommendations for Tempdb on its own drive
-
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
-
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.
-
Some more tips on tempdb : http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=92&messageid=277442
Get busy living ....or get busy dying.... -
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/
-
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.
-
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...
-
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!
-
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?
-
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply