I have recently received following email asking about how to reduce page contention on TempDB.
“We are using Trace Flag 1118 to reduce the tempDB contention on our servers (2000 and 2005). What is your opinion?
We have read lots of material, would you please answer me in single line.”
Just a day ago, while installing SQL Server on our development machine Jr. DBA asked me what should be kept file growth of the TempDB. I really have not thought about this till moment and I looked at MS site.
Two days ago I wrote an article about SQL SERVER – TempDB Restrictions – Temp Database Restrictions. Since then I have received few emails asking details about Temp DB. I use following T-SQL Script to know details about my TempDB. This script is a pretty old script but it does work great most of the time. I strongly encourage all of you to use a script to check your TempDB Information.
While conducting Interview for my outsourcing project, I asked one question to interviewer that what are the restrictions on TempDB? The candidate was not able to answer the question. I thought it would be good for all my readers to know the answer to this question so if you face this question in an interview or if you meet me in the interview you will be able to answer this question.