October 6, 2008 at 8:47 am
Reviewing our SQL Server configurations. We are a mid-size business (500 employees) with 10 SQL server databases. All but one our SQL Servers are Standard SQL 2005 SP2.
I am reviewing best practices and notice that it is suggested to breakup the files of tempdb to equal the number of processors.
Looking for a good reference links, any gothcas I should be aware
OR
Being small is it being proactive or overkill to do this?
October 6, 2008 at 9:54 am
Guess that depends. Are you having any performance issues? Is tempdb on separate drives from your other dbs?
Chris.
Chris.
October 6, 2008 at 10:05 am
I am not having performance problems at all. I am just trying see if this is something to be proactive in.
The data files are in the same place as the system in the default path of MSSQL.1/MSSQL/Data folder.
October 6, 2008 at 10:09 am
I think you'll get a much bigger boost by moving the tempdb onto its own drive than creating a few extra data files. I'd make that a higher priority.
Chris.
Chris.
October 6, 2008 at 11:11 am
Let me ask a follow-up...
This is our default configuration
C drive - SQL Binaries, System databases
D drive (SAN) - Data
E drive (SAN) - Log
D and E are just partitioned as they are separate but it is still the same SAN server.
Placing tempdb on a SAN storage unit is there any gotchas?
October 6, 2008 at 11:21 am
I've heard that you want one tempdb file per core that you have for SQL Server. Not sure if that helps, but I think that separation makes more of a difference.
You want to separate backups from data/logs first if you have 2 arrays.
If you get another one, separate logs from data
then separate tempdb.
October 6, 2008 at 11:30 am
Having one file for tempdb per CPU core allows for more efficient use of parallel processing. It allows each core to use a different file, which means it can use less expensive locks at the file level, which makes things faster.
This only matters if you use tempdb significantly. In most cases, you probably are using it, and probably more than you think. Run a trace on it, see what kind of hit it's taking.
Separate drives is good, but even if you can't do that, there's often a performance boost just from splitting the file up.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 6, 2008 at 2:52 pm
Thanks all for the help
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply