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.”
Wow, this was a very interesting question. What intrigued me was the second last where I am asked to answer in a single line. There is something about this strong email, I feel like blogging it here.
I think I can talk over this subject forever – well, there is no clear answer. There are so many caveats about everything. Again, I must stay honest to the request about answering in single line. I also do not like to answer which is YES/NO. What should I do?
Let me ask this question to community today? What will you answer to this email?
Let me start this by answering it myself in one line and taking one side.
“I enable this trace flag in SQL Server 2000 without hot patch or service pack and not in later versions (2005+) onwards as code is improved”.
What do you do in this case? The best answer will feature in this blog with due credit.
Regarding further read and hint here is a Microsoft KB which I think is very helpful.
In quick summary: (Read KB for accuracy)
When any page is allocated first 8 pages are allocated in mixed extended. This trace flag allocates uniform extended at the time, reducing contention. You can enable this trace flag at startup.
Reference: Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
If database developers solved every third or fourth problem by using temporary tables, cursors or inefficient joins strategy, DBA must think seriously about TraceFlag 1118 or other solutions to resolve contentions problem.
Hi Pinal
My question seems to be very silly but i dont know how it raised in my mind.
When we create the table how the sqlserver creates columns i mean in which sort order? suppose if i alter the table for adding a new column it will append at the end. but i want to add it some where in between of the existing columns. is it possible?
Do it via SSMS