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 Saturday, April 6, 2013 4:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:34 PM
Points: 265, Visits: 1,714
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?
Post #1439567
Posted Saturday, April 6, 2013 5:56 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
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 at GMail
Post #1439574
Posted Saturday, April 6, 2013 6:15 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: Today @ 4:58 PM
Points: 42,466, Visits: 35,532
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 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 #1439576
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse