March 25, 2013 at 11:17 pm
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
March 26, 2013 at 2:12 am
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
March 26, 2013 at 3:11 am
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
March 26, 2013 at 4:36 am
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
March 27, 2013 at 8:35 am
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 on googles mail service
March 28, 2013 at 3:45 am
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
April 6, 2013 at 1:57 am
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?
April 6, 2013 at 3:10 am
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
April 6, 2013 at 3:17 am
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?
April 6, 2013 at 3:27 am
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
April 6, 2013 at 4:22 am
I did run this query on server
SELECT session_id AS SessionID,
wait_duration_ms AS Wait_Time_In_Milliseconds,
resource_description AS Type_of_Allocation_Contention
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH_%'
AND (resource_description LIKE '2:%:1'
OR resource_description LIKE '2:%:2'
OR resource_description LIKE '2:%:3')
I got no rows. Is that means we don't have allocation contention on tempdb?
April 6, 2013 at 5:56 am
winmansoft (4/6/2013)
I did run this query on server
SELECT session_id AS SessionID,wait_duration_ms AS Wait_Time_In_Milliseconds,
resource_description AS Type_of_Allocation_Contention
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH_%'
AND (resource_description LIKE '2:%:1'
OR resource_description LIKE '2:%:2'
OR resource_description LIKE '2:%:3')
I got no rows. Is that means we don't have allocation contention on tempdb?
Yes, but only for the instant you ran that query.
Again I will say that the odds that tempdb contention is the PRIMARY cause of your slowness is VERY LOW compared to the myriad other causes.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 6, 2013 at 6:15 am
TheSQLGuru (4/6/2013)
Again I will say that the odds that tempdb contention is the PRIMARY cause of your slowness is VERY LOW compared to the myriad other causes.
Especially since this is SQL express, can only use a single core and hence is exceedingly unlikely to be capable of having allocation contention at all.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply