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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » Tempdb database will depend on the number of...

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

Rate Topic

Display Mode

Topic Options

Author

Message

ravisamigo

Posted Tuesday, September 21, 2010 3:05 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 22, 2013 11:11 PM
Points: 250, Visits: 706

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.

Post #990062

Jack Corbett

Posted Tuesday, September 21, 2010 6:16 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:22 PM
Points: 10,571, Visits: 11,871

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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #990177

ravisamigo

Posted Monday, November 15, 2010 6:16 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 22, 2013 11:11 PM
Points: 250, Visits: 706

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.

Post #1020693

GilaMonster

Posted Monday, November 15, 2010 6:53 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640, Visits: 29,895

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 2008, MVP
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

Post #1020735

steveb.

Posted Monday, November 15, 2010 6:54 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:42 AM
Points: 2,802, Visits: 7,103

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

Post #1020736

« Prev Topic | Next Topic »

Permissions