September 26, 2006 at 12:46 pm
Here's our server, Windows 2003, SQL Server 2000 SP4, 12gb Ram. As far as our "SAN" goes, we have several arrays with our production databases and logs located seperately.
We have an app that was written utilizing a ton of UDFs, consequently, TempDB is heavily used because the UDFs are returning result sets in Table Variables. One issue we encountered was a DEFWAKUP status on some of our processes, Microsoft's recommendation was to split TempDB out onto it's own drive away from the other databases. So we did that. Another recommendation was this article, http://support.microsoft.com/kb/328551. We've done this, creating a total of 8 TempDB files as well as enabling the -T1118 trace in the startup parameters. We also encounter extensive blocking on the last day of the month, that day for us is a very busy business day.
What I would like to know is would it help to put half of our TEMPDB files onto a seperate drive, having four files on one drive and four on the other.
Thanks for the opinions.
Mark
September 27, 2006 at 8:32 am
Of course, the answer is "it depends". You say "SAN" - if this is something like an Equalogic PS series array, you may be able to put everything there because it continually analyses access and rebalances the blocks.
That's also why it costs 6 times more than an otherwise equivalant Promise.
Take a look at perfmon - and filemon. But if the app is being nasty in using tempdb, certainly try moving tempdb to one or more directly attached SCSI drives. That helped me quite a lot while waiting for my Equalogic. Just benchmark before and after. Also, can you add memory? Might help.
It also sounds like it would be great to have the app tuned, but in this day of ISVs and legacy, I know what it means to be stuck with it.
BTW on 2005 you'd have great information to see if you can add or drop an index to improve performance, even if it is a proprietary app. And 2005 does indeed massively improve tempdb performance, and if you move it to 64 bit you'll be sitting pretty - it's amazing what capactity that has!
Roger L Reid
September 27, 2006 at 8:42 am
RL,
I said "SAN" because it really is more of a NAS, it's an
EMC CX 300 sold through Dell
Thanks for your advice.
Mark
September 27, 2006 at 9:15 am
Yiou could try using Meta-LUNs instead of LUNs. Also, have you maxed out the cache on your SAN yet ???
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 27, 2006 at 9:23 am
yep - we do more or less the same thing - directly connect the "SAN" to the server and avoid the network. Doesn't seem to be a good idea to share them with others.
I'd love to have more physically seperate target devices, but to keep the hardware expense reasonable I do it this way (everything is RAID 10 BTW)
internal scsi: OS, software, system databases, swap
different internal scsi: tempdb (in 4 files)
Equallogic 200E (expensive, self balancing iSCSI SAN (SATA disks)) - data and logs
Promise 500i (not so expensive, non-self balancing iSCSI SAN (SATA disks)) - dumps and XOsoft spool
I kind of came up with the general plan by reading Seibold documentation. We don't run the software, but their online manuals gave a LOT of attention to optimal disk usage for various sizes and budgets. Unfortunatly they just got bought by Oracle and the great docs aren't there any more....
roger reid
Roger L Reid
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply