| Author |
Topic |
|
ranvir_2k
Posting Yak Master
180 Posts |
 Posted - 2011-01-31 : 13:38:40
|
| Does anyone know the best practices when configuring tempdb in sql 2008? I have heard that tempdb should have it's own dedicated drive and that the data files should be split across 9 different files.Can you please tell me why the data should span 9 files? How did someone arrive at this figure?Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2011-02-01 : 01:26:56
|
| The recommendation for TEMPDB is 1 data file per CPU. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-01 : 07:11:28
|
quote:
Originally posted by sql_server_dba
The recommendation for TEMPDB is 1 data file per CPU.
Per cpu core isn't it...?- LumbagoMy blog-> www.thefirstsql.com |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2011-02-01 : 10:38:09
|
| It should be equal to number of processors. Create it with Equal size.Reason:from KB Article:During object creation, two (2) pages must be allocated from a mixed extent and assigned to the new object. One page is for the Index Allocation Map (IAM), and the second is for the first page for the object. SQL Server tracks mixed extents by using the Shared Global Allocation Map (SGAM) page. Each SGAM page tracks about 4 gigabytes of data. As part of allocating a page from the mixed extent, SQL Server must scan the Page Free Space (PFS) page to find out which mixed page is free to be allocated. The PFS page keeps track of free space available on every page, and each PFS page tracks about 8000 pages. Appropriate synchronization is maintained to make changes to the PFS and SGAM pages; and that can stall other modifiers for short periods. When SQL Server searches for a mixed page to allocate, it always starts the scan on the same file and SGAM page. This results in intense contention on the SGAM page when several mixed page allocations are underway, which can cause the problems. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-02-04 : 13:04:11
|
| I am not to sure why does Microsoft recommends of having 1 data file per CPU in the first place.I mean I never could understand the reason for this benchmark.If you have no issues with allocation page contention then having multiple files wont be of much help.To monitor the allocation contention you can use the sys.dm_os_waitingtasks DMV and check for the columnname "wait_type" and value "PageLatch".Only go for multiple data files if you have issues with allocation contention.And if you do decide to go for multiple data files make sure that all the tempdb data files are of the same size.Also if a lot of work tables are created due spools or sort operations having multiple data file is only going to degrade the performance.Because these operations are sequential which will create multiple streams of sequential I/O and until you don't have a subsystem capable of handling concurrent streams it will hurt performance.Please take all this point into consideration and then only go ahead with multiple files for tempdb.PBUH |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-02-04 : 13:31:34
|
| Also Microsoft documented there wont be any performance benefits if you exceed 8 data files.Do you have any idea why Microsoft only recommends the no of datafiles based on the physical CPU cores and not the logical cores also?PBUH |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Configuring tempdb