tempdb, memory, and cpu waits - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

tempdb, memory, and cpu waits

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2011-11-23 : 14:29:34

I have 64 cores on my production server so I tried setting tempdb to use one log file and 8 data files but wait times have gone way up according to my monitoring tool, a tiny bit for disk but a factor of 100 for CPU. Some minor schema/sproc changes were made at the same time but I highly doubt they were responsible. What's really weird is that I don't notice a difference at all in response times for my queries. Should I try changing to 4 files and see what happens? I can't make changes very often as this is a 24/7 server...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-23 : 15:45:24

Wait times with what wait type?

--
Gail Shaw
SQL Server MVP

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2011-11-23 : 15:52:16

I can't tell from the tool I'm using, should I use a system view to check?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-23 : 16:25:21

Yeah. sys.dm_os_waiting_tasks, but you may need to monitor for a while.

Just saying that the wait times overall have increased doesn't give any indication what could be wrong

--
Gail Shaw
SQL Server MVP

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2011-11-23 : 17:53:17

This is what I see right now using "select wait_type, sum(wait_duration_ms) from sys.dm_os_waiting_tasks group by wait_type order by 2 desc". Does it tell us anything interesting without watching it over time? The server was restarted yesterday afternoon.

ONDEMAND_TASK_QUEUE 84975662
KSOURCE_WAKEUP 84421552
CLR_AUTO_EVENT 2075028
SQLTRACE_WAIT_ENTRIES 1430549
DISPATCHER_QUEUE_SEMAPHORE 852133
BROKER_TRANSMITTER 28124
BROKER_TASK_STOP 17636
FT_IFTS_SCHEDULER_IDLE_WAIT 13378
BROKER_EVENTHANDLER 8214
XE_TIMER_EVENT 7185
XE_DISPATCHER_WAIT 7184
WAITFOR 6151
LAZYWRITER_SLEEP 3839
SQLTRACE_INCREMENTAL_FLUSH_SLEEP 1553
SLEEP_TASK 1006
CHECKPOINT_QUEUE 909
REQUEST_FOR_DEADLOCK_SEARCH 647
LOGMGR_QUEUE 1

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2011-11-23 : 18:04:30

Also if I look at Resource Waits in Activity Monitor, the highest category by far in terms of Cumulative Wait Time is Other, followed very distantly by Lock and Logging. Other is generally at the top of Wait Time and Most Recent Wait Time as well.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-24 : 04:44:05

All of those waits are system waits and completely ignorable.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources