January 30, 2011 at 8:03 pm
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.
January 31, 2011 at 7:48 am
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 on googles mail service
January 31, 2011 at 7:24 pm
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
February 1, 2011 at 7:16 am
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 on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply