SQL Server Data Files and Threads

585 pts.
Tags:
Exam 70-432
Open IT Forum
SQL Database
SQL Server
SQL Server 2008
I am reading a Microsoft Press Book MCTS Exam 70-432 and in chapter 2 it talks about Data Files and it says "SQL Server creates a thread for each file underneath a database. As you increase the number of files underneath a database, SQL Server creates more threads that can be used to read and write data." (Page 41). I read this article on the internet and that is not true, I am confused! Here is the article I read. http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

Software/Hardware used:
SQL SERVER 2008

Answer Wiki

Thanks. We'll let you know when a new response is added.

Not to take anything away from Mike Hotek’s excellent book, but you will find that there are many controversial opinions and factual mistakes throughout. You can find the official errata here, which currently lists 10 items in the “Serious Technical Mistake” category.

It would be great if all technical books were error free from the start but unfortunately they are written typically by writers with some technical skills and then reviewed by a handful of technical folks for accuracy. Inevitably, some errors will sneak through.

In this age of everybody with access to publish just about anything, you will always want to keep a healthy bit of skepticism. In this particular case, you have the official product support teams blog in conflict with the “official” (but written by a third party) book. Not only is the blog to me a more reliable source, for this item, it matches what I have anecdotal observed in my own experience.

————

I believe that the confusion in the book comes from the fact that tempdb will appear to work differently from other databases and therefor we want to have more data files for tempdb than for other databases. The reason for this is that with tempdb you’ve got objects being created and dropped all the time (temp tables, table variables, internal objects, etc.) which creates contention on the GAM pages within the database files. By adding more physical files we add more GAM pages so that the allocations are spread out which will remove most or all of the blocking which happens against the GAM page writes. Typically tests show that 8 tempdb pages is enough for most systems (edge cases have needed hundreds of files).

People tend to push this same config setup to user databases as well, but the user databases don’t have this same problem as MOST (probably 99% or more) of user databases don’t do a lot of object creation. Multiple database files for user databases is great for spreading the load across multiple disk arrays.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following