Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Enabling Trace Flag T1118 Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 11:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:04 AM
Points: 265, Visits: 1,718
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
Post #1435261
Posted Tuesday, March 26, 2013 2:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 7:53 AM
Points: 42,822, Visits: 35,952
Have you tested and seen that the traceflag benefits you and your workload?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1435305
Posted Tuesday, March 26, 2013 3:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:04 AM
Points: 265, Visits: 1,718
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
Post #1435336
Posted Tuesday, March 26, 2013 4:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
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

Post #1435372
Posted Wednesday, March 27, 2013 8:35 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:53 AM
Points: 4,350, Visits: 6,162
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
Post #1435944
Posted Thursday, March 28, 2013 3:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:55 AM
Points: 2,868, Visits: 3,214
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 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1436339
Posted Saturday, April 6, 2013 1:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:04 AM
Points: 265, Visits: 1,718
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?
Post #1439550
Posted Saturday, April 6, 2013 3:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
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

Post #1439554
Posted Saturday, April 6, 2013 3:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:04 AM
Points: 265, Visits: 1,718
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?
Post #1439557
Posted Saturday, April 6, 2013 3:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
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

Post #1439561
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse