Slow Server MSDB high disk activity

• Shahzadi

  Default port

  Points: 1479

  More actions

  October 30, 2014 at 4:57 am

  #300529

  Hi,

  I am managing a High Availability production server and on that server i got around 25 databases which are log shipped to another server. recently i have issue with high disk usages and can't able to figure out what is causing the problem. under sp_who2 it show's that msdb is using high disk with update sysjobactivity task. i got around 300 sql jobs schedule which runs on different times.

  i am wondering what could it cause. i have recently run this and try to capture data

  DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

  then i run this

  WITH Waits AS

  (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,

  100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

  ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

  FROM sys.dm_os_wait_stats

  WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'

  ,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'

  ,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP'

  ,'CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE'

  , 'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))

  SELECT W1.wait_type,

  CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

  CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

  CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

  FROM Waits AS W1

  INNER JOIN Waits AS W2

  ON W2.rn <= W1.rn

  GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

  HAVING SUM(W2.pct) - W1.pct < 95

  and here is the resutls

  wait_type wait_time_s pct running_pct

  SQLTRACE_INCREMENTAL_FLUSH_SLEEP 600.03 74.02 74.02

  WRITELOG 57.59 7.10 81.13

  PAGEIOLATCH_SH 57.29 7.07 88.20

  PREEMPTIVE_OS_AUTHENTICATIONOPS 51.44 6.35 94.54

  SOS_SCHEDULER_YIELD

  Can anyone help me with that please.