Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-04-06 : 08:46:26
|
| Steve writes "Hi Folks; We're currently planning a new install of SQL2K within a Win2003 clustered environment. The instance will hold a number of databases.., some which will be supporting applications that are extremely process intensive. My question is.., is it possible to have two (or more) Tempdb's within the same instance of SQL and assign which Tempdb is used by a particular database?Thank you very much for your help;Steve." |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-04-06 : 08:56:37
|
| You should probably look at multiple instances. You cannot have multiple tempdb databases. You can have multiple tempdb files though. There's no way to control what uses which file though.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
bakerjon
Posting Yak Master
145 Posts |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-04-06 : 13:13:27
|
| That being said, how does one determine that TempDB is getting hit hard enough to warrent splitting it out onto it's own disk array or several arrays?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-04-06 : 13:21:36
|
| Excerpt from the above KB...From the sysprocesses system table output, the waitresource may show up as "2:1:1" (PFS Page) or "2:1:3" (SGAM Page). Depending on the degree of contention, this may also lead to SQL Server appearing unresponsive for short periods.andAn excerpt from http://support.microsoft.com/servicedesks/webcasts/en/wc011502/wct011502.aspA lot of tempdb activity can produce latch waittypes...Looks like good ol' Wait Types and Wait Resources will give a clue for cold hard numbers.For us, it was just knowing our workload. Our developers love Temp Tables, Order Bys, et al. We recently implemented this for the Data Warehouse server and it reduced the nightly ETL job 20%-ish!Jon |
|
|
|
|
|
|
Multiple Tempdb's in clustered env...