Trace Flag

Question

Trace Flag

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Hany Helmy SSChampion Points: 13921 More actions · Answer 1

From the explanation: "SQL Server 2008 optimized mixed extent allocation behavior reducing the need for trace flag 1118 and the contention on SGAM(s)."

So I guess there is no need to enable that trace, correct me if I am wrong.

Thanx

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

TomThomson SSC Guru Points: 104773 More actions · Answer 2

Hany Helmy (12/6/2014)
From the explanation: "SQL Server 2008 optimized mixed extent allocation behavior reducing the need for trace flag 1118 and the contention on SGAM(s)."
So I guess there is no need to enable that trace, correct me if I am wrong.
Thanx

The caching in 2005 and later releases reduces contention, it doesn't eliminate it. Anyway, the cached table information will, when there is any, avoid the SGAM contention for the initial allocation. But if at some point in time temp tables are being created faster than they are being deleted that cache can become empty, so that contention starts to happen again. So if you have periods where lots of temp tables are allocated you may still want to use the flag.

Tom

Hany Helmy SSChampion Points: 13921 More actions · Answer 3

TomThomson (12/7/2014)
Hany Helmy (12/6/2014)
From the explanation: "SQL Server 2008 optimized mixed extent allocation behavior reducing the need for trace flag 1118 and the contention on SGAM(s)."
So I guess there is no need to enable that trace, correct me if I am wrong.
Thanx
The caching in 2005 and later releases reduces contention, it doesn't eliminate it. Anyway, the cached table information will, when there is any, avoid the SGAM contention for the initial allocation. But if at some point in time temp tables are being created faster than they are being deleted that cache can become empty, so that contention starts to happen again. So if you have periods where lots of temp tables are allocated you may still want to use the flag.

Thanx 4 the info.

Hany

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

robbegoetmaeckers (12/4/2014)

There's a very nice blog post from Kendra Little about this: http://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/%5B/quote%5D Thanks for the help.