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

Tempdb Contention - sys.dm_os_waiting_tasks Expand / Collapse
Author
Message
Posted Sunday, January 30, 2011 8:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 14, 2013 1:23 PM
Points: 2, Visits: 234
Hello All,

I am trying to analyze the tempdb contention in SQL Server 2005. I am running the below script against SQL Server 2005 instance using SSIS package every 10 seconds. I go this information from Microsoft white paper

-- get the current timestamp
declare @now datetime
select @now = getdate()

-- insert data into a table for later analysis
insert into analysis..waiting_tasks
select
session_id,
wait_duration_ms,
resource_description,
@now
from sys.dm_os_waiting_tasks
where wait_type like ‘PAGE%LATCH_%’ and
resource_description like ‘2:%’

After running the above query for more than 4 days I noticed that when DBCC CheckDB runs on the server, the value wait_duration_ms column is close to 20 Sec on some servers. Is it a normal thing to happen during the DBCC CheckDb run ?

There are some sporadic instances during the day time when the value wait_duration_ms column is recorded close to 15 ms. What is the acceptable value of wait_duration_ms column?

Any help will be greatly appreciated.

Thanks in advance.
Post #1055953
Posted Monday, January 31, 2011 7:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 4,400, Visits: 6,261
dbcc checkdb is one of MANY things that started using tempdb in SQL 2005. You REALLY need a good tempdb on SQL systems.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1056201
Posted Monday, January 31, 2011 7:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 14, 2013 1:23 PM
Points: 2, Visits: 234
Hello Kevin,

Thanks for your reply.

I understand that DBCC CheckDB uses TempDB heavily. But is it a normal thing to observe the wait duration of 20 ms during the DBCC CheckDB run as I am experiencing?

Cheers
Post #1056559
Posted Tuesday, February 1, 2011 7:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 4,400, Visits: 6,261
It is quite common if/when your tempdb IO subsystem is poor - which 99% of the clients I help have.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1056797
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse