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.
