Tempdb Contention - sys.dm_os_waiting

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Tempdb Contention - sys.dm_os_waiting_tasks

Tempdb Contention - sys.dm_os_waiting_tasks

Rate Topic

Display Mode

Topic Options

Author

Message

vivaciousvikas

Posted Sunday, January 30, 2011 8:03 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 9:33 AM
Points: 2, Visits: 232

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

TheSQLGuru

Posted Monday, January 31, 2011 7:47 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:15 PM
Points: 3,672, Visits: 5,171

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

vivaciousvikas

Posted Monday, January 31, 2011 7:24 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 9:33 AM
Points: 2, Visits: 232

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

TheSQLGuru

Posted Tuesday, February 01, 2011 7:16 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:15 PM
Points: 3,672, Visits: 5,171

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 »

Permissions