Tempdb database will depend on the number of CPUs (logical or physical)

Question

Tempdb database will depend on the number of CPUs (logical or physical)

ravisamigo

SSCertifiable

Points: 7440
More actions
September 21, 2010 at 3:05 am

#99358

Dear All,
Can someone advise/suggest on the below? Please
“The number of data files you should allocate in the tempdb database will depend on the number of CPUs (logical or physical) present in the machine. When SQL Server accesses a database, it starts a scheduler thread for each data file present. So if you have eight CPUs in your physical server, the tempdb database should have eight data files to “load balance” the threads
Also, data files in a database are used in a “proportional fill” algorithm. This means that if you have two data files in a database – one 4 GB and another 8 GB, SQL will try to stripe the data across the two files in a 1:2 ratio. For each stripe of data written to the 4 GB file, SQL will write two stripes in the 8GB file – hence the term “proportional fill”. Now if you configure tempdb to have the same number of data files as the number of CPUs present and specify each data file to have the same size and growth, you are effectively ensuring the CPU load is evenly distributed across tempdb. Once you have completed the tempdb configuration, you will need to restart the SQL instance for the changes to take effect.”
You can find Step 5: Configure tempdb Database point from the below link for your reference.
http://www.sql-server-performance.com/articles/dba/post_installation_steps_p1.aspx
Thanks and Regards,
Ravichandra.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Essentially what this is saying is that, for optimal performance, you could add data files of the same size to tempdb up to the # of processors on your server. So a 4 processor server should have 4 same-sized physical files for tempdb for optimal tempdb performance. The reason you want the files to be the same size is because SQL Server tries to keep the files filled to the same proportion. So if you had a 100MB file and a 200MB file, SQL Server will try to keep them at the same fill level, for example 50%, which means that the 200MB file will be written to approximately twice as much as the 100MB file which reduces the performance gained by having multiply files.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ravisamigo SSCertifiable Points: 7440 More actions · Answer 2

Thanks for the reply.

Sorry for my late response.

Have you implimented ever before or if you have any document how to set it? please advise.

Thanks in advance.

Regards,

Ravichandra.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Before you consider implementing that, read this: http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

steveb. SSC-Forever Points: 46733 More actions · Answer 4

That advice has been floating around for a while and is really only applicable when you have identified bottlenecks in the tempdb.

some good advice about this here

http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx"> http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx