Tuning new SQL Server

Last Post 14 Jul 2010 02:04 PM by J013-B. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Andy.L
New Member
New Member

--
17 Jun 2010 10:14 PM
Hi
I'm about to begin deployment of a new SQL Server 2008 R2 cluster. This is our main production cluster, and will host approx. 200 different databases. But, as I've learned, SQL Server 2008 uses the tempdb different than earlier versions of SQL Server. I've read BOL regarding distributing tempdb over numerous files for each CPU. (our new cluster will have 32 CPUs on each node). Any one with any pointers how I can monitor tempdb usage and scale the files correctly?

I'm also looking into to have configure the cluster in an active/active configuration, with prod databases on one node, and test on the second node. Any one with thoughts about that kind of configuration?

/Andy.l
rm
New Member
New Member

--
18 Jun 2010 04:40 AM
Don't need spread tempdb data file if there's no contention, several KB articles there for this topic. Putting 2 instances are ok as long as they run on separate node, may have performance impact when run both on same node.
gunneyk
New Member
New Member

--
18 Jun 2010 06:22 AM
Check the waitstats and look for PAGELATCH_UP. If you get wait types of that for Tempdb then you can probably use more files. But certainly not 32. Try 2 to 4 files to begin with and monitor the waits for PAGELATCH_UP. Just make sure they are all the same size. Tempdb is not used any differently than 2005 but it is slightly from 2000.
J013-B
New Member
New Member

--
14 Jul 2010 02:04 PM
I agree with gunneyk. The one file per processor/core was more a rule of thumb than a hard rule, and from talking about this at PASS groups, was created back when 4-8 cores was the norm for servers. I would say unless you are seeing an extraordinaryt amount of tempdb activity, then you will not see much benefit out of having that many separate files.


Acceptable Use Policy
---