Page Contention on TempDB needs an identified allocation wait and server version. I check both before recommending legacy trace flags.
The original email asked about trace flag 1118 on SQL Server 2000 and 2005 and demanded a one-line answer. My short response said not to use it after 2000. That was too broad for the later legacy releases.
Keep the historical mechanism clear
Trace flag 1118 changes mixed-extent allocation behavior and can reduce related SGAM contention in older releases. It does not repair every TempDB wait or substitute for investigating workload and allocation patterns.
SQL Server 2016 and later include TempDB allocation improvements that make the old 1118/1117 configuration unnecessary for that default behavior. Review the version-specific guidance before considering a legacy server change.
A better one-line response
I first identify the TempDB allocation contention and check guidance for that SQL Server version. Then I test file sizing and workload changes. I consider a legacy trace flag only for the relevant behavior.
What would your one-line answer be? Share the version, allocation evidence, and reasoning behind it.
A legacy trace flag is not a diagnosis, it is a version-specific response to a particular allocation problem.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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