Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Enabling Trace Flag T1118


Enabling Trace Flag T1118

Author
Message
IT researcher
 IT researcher
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 1798
Hi,
We are using Sql server 2008 R2 express. We heard enabling T1118 is good for tempdb contention.Somewhere it said its not much help sql server 2005 aftwrwards. We are thinking about enabling it. Is there any disadvantages of enabling T1118?
Thank You
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44391
Have you tested and seen that the traceflag benefits you and your workload?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


IT researcher
 IT researcher
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 1798
GilaMonster (3/26/2013)
Have you tested and seen that the traceflag benefits you and your workload?

No not yet. I want to know if it has disadvantages before enabling
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44391
Small tables would take slightly more space, looking for disadvantages and any effective gains is why you test before enabling in production.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5963 Visits: 8312
There are at least 2 things you should do before even considering that trace flag.

1) look for unnecessary temp object creations. There could be lots of causes for these, from missing indexes leading to hash joins to unnecessary sorts (VERY popular with devs it seems) to just really poor coding (I once had a client that had at least one table variable in about 80% of their 2000+ sprocs!!)

2) after the above if you are still having temp object allocation issues move to more files in tempdb. Start with 1/4 number of physical cores and work up from there to 1/2, 3/4 and 1:1. Note that you can cause IO to actually be SLOWER in some scenarios by doing this.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3146 Visits: 3816
If you want to enable the T1118 flag in SQL Express there is nothing stopping you from doing so.

However, because SQL Express (SQL2008 upwards) has a maximum database size of 10GB, can only use 4 CPU cores and a maximum of 1GB RAM, it is unlikely you will ever have the issues that T1118 is designed to overcome.

You would then have the (small) disk space disadvantage inherent in T1118 without the ability to take advantage of the concurrency improvements that T1118 gives.

As Gail says, you should test to see if this is beneficial in your environment

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara
IT researcher
 IT researcher
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 1798
TheSQLGuru (3/27/2013)
There are at least 2 things you should do before even considering that trace flag.

1) look for unnecessary temp object creations. There could be lots of causes for these, from missing indexes leading to hash joins to unnecessary sorts (VERY popular with devs it seems) to just really poor coding (I once had a client that had at least one table variable in about 80% of their 2000+ sprocs!!)

2) after the above if you are still having temp object allocation issues move to more files in tempdb. Start with 1/4 number of physical cores and work up from there to 1/2, 3/4 and 1:1. Note that you can cause IO to actually be SLOWER in some scenarios by doing this.


Thanks for reply
But how to find if we have problem with tempdb?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44391
Monitor for allocation contention. That's all that multiple files and traceflag 1118 are for, if you don't have allocation contention then there's no point in adding the traceflag.

Ed's right, with Express limited to a single core it's incredibly unlikely that you can generate the concurrent load required to cause allocation contention in TempDB.

Are you seeing allocation contention? If not, why are you considering traceflag 1118?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


IT researcher
 IT researcher
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 1798
GilaMonster (4/6/2013)
Monitor for allocation contention. That's all that multiple files and traceflag 1118 are for, if you don't have allocation contention then there's no point in adding the traceflag.

Ed's right, with Express limited to a single core it's incredibly unlikely that you can generate the concurrent load required to cause allocation contention in TempDB.

Are you seeing allocation contention? If not, why are you considering traceflag 1118?

Our server slows down abnormally sometimes... But how to check whether its tempdb problem only?How to monitor allocation contension?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44391
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

As for how you identify corruption, I answered that in the thread you started on tuning TempDB.

p.s. Please in future don't start multiple threads for the same problem, you just end up with exactly this, answers spread across several places.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search