October 29, 2010 at 10:01 am
I've been reading and hearing several different takes on whether you should have 1 single tempdb regardless of cpu's or 1 tempdb per cpu. We have a 16 cpu system and 1 tempdb file. The recommendation from Best Practice analyzer is to have a tempdb per cpu. What is the concensus and how do I meausre this to see if this is a potential problem.
October 29, 2010 at 10:31 am
http://www.sqlskills.com/BLOGS/PAUL/category/Misconceptions.aspx
download the pdf from there and read point number 12/30
you cant have more authentic answer.
April 10, 2012 at 5:13 am
» Tempdb configuration:
• Multiple data files pre-sized equally to avoid auto-growth
-- Use 1 data file per CPU if you are not comfortable with any part of the
alternative
-- Or start with 1 data file per 2 or 4 CPUs, monitor for tempdb contention,
and adjust the number of data files as needed
• Pre-size the data and log files to use 90% of the available disk space
• The log file should be twice the size of a single data file
• Disable auto-growth of the data files
• Set auto-growth of the log file to a hard value such as 512 MB
»» Provide plenty of RAM for tempdb to use
»» Make sure queries are optimized to avoid tempdb spills
»» Monitor for tempdb contention
»» Monitor the version store for long running transactions
»» Baseline the version store to get a functional range for your server and monitor
for changes outside of this range
--Ron
April 11, 2012 at 12:43 pm
Some 'practical' experience in this particular subject says:
- no more than 8 files
definitely no more than 1 file per socket as opposed to CPU
- identical sized files with no growth
- trace flag 1118
At my site we actually had to go to 16 files of 30GB each - that's 480 GB
(our tempdb transaction log is 256 GB - and yeah, we do use 50-60% of it almost 24x7)
We just recently moved from 16 socket dual core HW to 8 socket 8 core HW.
Yeah, we tried backing off to 8 files, but 16 was still needed in our case due to the heavy SGAM usage.
We are definitely the exception to the rule.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 12, 2012 at 2:19 pm
rudy komacsar - Doctor "X" (4/11/2012)
Some 'practical' experience in this particular subject says:- no more than 8 files
definitely no more than 1 file per socket as opposed to CPU
- identical sized files with no growth
- trace flag 1118
At my site we actually had to go to 16 files of 30GB each - that's 480 GB
(our tempdb transaction log is 256 GB - and yeah, we do use 50-60% of it almost 24x7)
We just recently moved from 16 socket dual core HW to 8 socket 8 core HW.
Yeah, we tried backing off to 8 files, but 16 was still needed in our case due to the heavy SGAM usage.
We are definitely the exception to the rule.
Wow - I would LOVE to get to play with a box like that! Let me know if you need some consulting help! 😎
Exception to the rule is right though. In 15 years of consulting I have only come across 2 clients that had SGAM/PFS latch contention and one of them was purely self-inflicted.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 13, 2012 at 2:21 pm
Play eh ? Well, now I am going to tease you a bit Kevin ...
There is not just one, but 4 Active/Passive clusters and 2 Single node clusters - each of the 6 environments are all DL-980-G7 HW with 1 TB of RAM and 8 - 8 gb HBAs. Each server has its own dedicated RAID10 SAN (SAN max throughput is 5.5 GB/sec) Oh, I forgot, the database environments are 2.5 TB each. 2 of the 6 servers are 24x7x365 high volume OLTP and batch mixed together.
I am having way too much fun playing with my toys !!!
(hence the long time absence on the SSC forums)
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 13, 2012 at 2:28 pm
rudy komacsar - Doctor "X" (4/13/2012)
Play eh ? Well, now I am going to tease you a bit Kevin ...There is not just one, but 4 Active/Passive clusters and 2 Single node clusters - each of the 6 environments are all DL-980-G7 HW with 1 TB of RAM and 8 - 8 gb HBAs. Each server has its own dedicated RAID10 SAN (SAN max throughput is 5.5 GB/sec) Oh, I forgot, the database environments are 2.5 TB each. 2 of the 6 servers are 24x7x365 high volume OLTP and batch mixed together.
I am having way too much fun playing with my toys !!!
(hence the long time absence on the SSC forums)
Slobbering now.
Only thing better would if all of the disk was FUSION IO like SAN.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply