February 2, 2017 at 10:00 am
HI Experts,
Can any one explain the pros and cons of adding the tempdb datafiles based on processors in production environment.
No of processors: 8
Regards
Narine M
February 2, 2017 at 12:01 pm
Narine M - Thursday, February 2, 2017 10:00 AMHI Experts,Can any one explain the pros and cons of adding the tempdb datafiles based on processors in production environment.
No of processors: 8
Regards
Narine M
Pros would be it can alleviate tempdb contention. The cons...you could add too many.
There are debates, conflicting information around the "based on processors" and how many should actually be added. I'd probably just read up on all of those and do some monitoring for contention as I don't think there is any one size fits all solution or recommendations.
Here are some articles on this that you may want to check - sorry can't paste them in correctly as it just won't accept links for some reason:
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/
http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/
https://support.microsoft.com/en-us/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-database
https://blogs.msdn.microsoft.com/psssql/2009/06/04/sql-server-tempdb-number-of-files-the-raw-truth/
Sue
February 23, 2017 at 4:04 am
Sue_H - Thursday, February 2, 2017 12:01 PMNarine M - Thursday, February 2, 2017 10:00 AMHI Experts,Can any one explain the pros and cons of adding the tempdb datafiles based on processors in production environment.
No of processors: 8
Regards
Narine M
Pros would be it can alleviate tempdb contention. The cons...you could add too many.
There are debates, conflicting information around the "based on processors" and how many should actually be added. I'd probably just read up on all of those and do some monitoring for contention as I don't think there is any one size fits all solution or recommendations.
Here are some articles on this that you may want to check - sorry can't paste them in correctly as it just won't accept links for some reason:http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/
http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/
https://support.microsoft.com/en-us/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-database
https://blogs.msdn.microsoft.com/psssql/2009/06/04/sql-server-tempdb-number-of-files-the-raw-truth/Sue
you want no more than 8, ideally check you have allocation contention first.
Start with 2 files and raise to 4, there are myths around the number of files stemming from sql server 2000. Paul Randal details it perfectly in his article which is linked above
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply