Trace Flag

Question

Post reply

Trace Flag

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
December 3, 2014 at 11:05 pm

#293133

Comments posted to this topic are about the item Trace Flag
Thanks.

Viewing 15 posts - 1 through 15 (of 17 total)

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

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 1

I had to Google this, because I did not understand at least 9 words from that question.

Nice question, definately learned something.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

robbegoetmaeckers SSC Veteran Points: 271 More actions · Answer 2

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/

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Nice question, thanks. And a good explanation of why it's not needed so much these days.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

That was a good question. I love starting out the day by learning something, so thank you.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Another really good source / article for this topic is from Paul Randal.

http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

RK Mandava SSCarpal Tunnel Points: 4153 More actions · Answer 6

RK Mandava

SSCarpal Tunnel

Points: 4153

December 4, 2014 at 7:31 am

#1762712

Nice question, And a good explanation thanks.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 7

Koen Verbeeck (12/4/2014)
I had to Google this, because I did not understand at least 9 words from that question.

I had to Google as well, but for a different reason. I know that there is a tracce flag and I know when to use it - but I simply refuse to memorize meaningless four-digit numbers that are easily found.

DBAs should know that this trace flag exists and should know when (not) to use it, so that makes this a good question. But the requirement to give the exact number is a bit of a downer, because it forces people to use a search engine.

(Everyone who annswered this without doing a search should be ashamed for wasting brain capacity that can be used to memorize things that are not as easily found in a search engine)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Hugo Kornelis (12/4/2014)
(Everyone who annswered this without doing a search should be ashamed for wasting brain capacity that can be used to memorize things that are not as easily found in a search engine)

+1

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gazareth One Orange Chip Points: 27737 More actions · Answer 9

Then colour me ashamed

It is one of only a handful I actually remember though!

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 10

Thank you for the post, really good one.

(How come the complete list of trace flags is not available in the msdn? Also found this link by Victor Isakov http://victorisakov.files.wordpress.com/2011/10/sql_pass_summit_2011-important_trace_flags_that_every_dba_should_know-victor_isakov.pdf, very informative document.)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Hugo Kornelis (12/4/2014)
Koen Verbeeck (12/4/2014)
I had to Google this, because I did not understand at least 9 words from that question.
I had to Google as well, but for a different reason. I know that there is a tracce flag and I know when to use it - but I simply refuse to memorize meaningless four-digit numbers that are easily found.
DBAs should know that this trace flag exists and should know when (not) to use it, so that makes this a good question. But the requirement to give the exact number is a bit of a downer, because it forces people to use a search engine.
(Everyone who annswered this without doing a search should be ashamed for wasting brain capacity that can be used to memorize things that are not as easily found in a search engine)

I tend to agree with this - but I think the search engine built in to http://msdn.microsoft.com/en-us/library is Bing, not Google.

Tom

Hany Helmy SSChampion Points: 13921 More actions · Answer 12

Complicated staff but @ least learned somthin new 2day, thanx

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 13

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 14

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

Trace Flag

Cookies on SQLServerCentral