Misconceptions around –T1118


There has been a lot on debate on whether having the same number of tempdb data files as the number of physical cores help a SQL instance in case you are facing Latch Contention issues on tempdb. This is typically seen with the wait-resource in the sysprocesses or sys.dm_exec_requests (depending on what you use to monitor blocking) output with values like 2:1:X or 2:2:X with having non-zero waittimes.

An article was published for this particular issue which asked you to enable –T1118 and split the tempdb to have multiple data files:

328551    Concurrency enhancements for the tempdb database
http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551

This trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load of small temp table creation and deletion.

Furthermore, with the advent of new monster systems having 64 processors (16 quad cores) and 128GB of RAM, would it make sense to split the tempdb into multiple files for SQL Server 2005 and 2008. One of the things that is not commonly known as mentioned by Paul in his post below is that on SQL Server 2005 and 2008, there is a temp table cache that is maintained for temp tables created on the instance. In case, your temp table creation workload is very high, enabling –TF1118 wouldn’t benefit you.

A quote from that post:

As far as data files go though, the number has changed. Instead of a 1-1 mapping between processor cores and tempdb data files (*IF* there’s latch contention), now you don’t need so many – so the recommendation from the SQL team is the number of data files should be 1/4 to 1/2 the number of processor cores (again, only *IF* you have latch contention). The SQL CAT team has also found that in 2005 and 2008, there’s usually no gain from having more than 8 tempdb data files, even for systems with larger numbers of processor cores. Warning: generalization – your mileage may vary – don’t post a comment saying this is wrong because your system benefits from 12 data files. It’s a generalization, to which there are always exceptions.

Yet again, Paul Randall has published an informative blog post around the usage of -TF1118:

http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-TF-1118.aspx 

Advertisements

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s