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
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.