I wanted to give out some thoughts about issues related to SQL server TempDB, but it appeared that most of the useful information are found in the below KB article.
https://support.microsoft.com/en-us/kb/2154845
However, I have explained some of them which could also have been explained in the above KB article.
https://support.microsoft.com/en-us/kb/2154845
However, I have explained some of them which could also have been explained in the above KB article.
- One of the main reasons for contention issues in TempDB is that TempDB is shared across all databases in the instance.
- PageIOLatch (Thread waiting to be read from the disk) and PageLatch (Multiple threads competing access for a in-memory page) wait types pointing to TempDB usually notifies us about this problem.
- PageIOLatch wait types can be avoided by having multiple datafiles as per the recommendations in the KB article, But remember if you see memory spills to tempdb which causes performance issues and if you are having large number of datafiles YOU MIGHT BE HAVING TOO MANY Datafiles!!
- PageLatch waits which occured mostly in SQL Server 2000 are now tweaked from SQL Server 2005 & onwards. However, if you think you are still suffering from them you can go with the trace flag T1118 which is explained in the kb article below. It turns off the use of mixed extents in TEMPDB ONLY.
Good one.
ReplyDeleteI heard that Microsoft may be considering to restructure tempdb to have multiple tempdbs to an instance. May be for different workloads, type of work separate tempdb. That way tempdb throughput can be increased. With every new version of SQL Server, tempdb comes with increased workload.
Thanks Susantha,
ReplyDeleteBelow are some of the enhancements to tempdb in SQLServer 2016.
There are several enhancements to TempDB:
Trace Flags 1117 and 1118 are not required for tempdb anymore. If there are multiple tempdb database files all files will grow at the same time depending on growth settings. In addition, all allocations in tempdb will use uniform extents.
By default, setup adds as many tempdb files as the CPU count or 8, whichever is lower.
During setup, you can configure the number of tempdb database files, initial size, autogrowth and directory placement using the new UI input control on the Database Engine Configuration - TempDB section of SQL Server Installation Wizard.
The default initial size is 8MB and the default autogrowth is 64MB.
You can specify multiple volumes for tempdb database files. If multiple directories are specified tempdb data files will be spread across the directories in a round-robin fashion.
Source: https://msdn.microsoft.com/en-us/library/bb510411.aspx