none
DPM SQL server - Heavy resource usage RRS feed

  • Question

  • Hi,

    Every morning for the past couple of weeks I've noticed that our MSDPM2010 SQL instance is eating pretty much every resource it can (1 CPU core, all available RAM and heavy I/O). Investigating what's runing I've found the culprit to be dbo.prc_ARM_Pruning_PrunePathTable

    the table it's trying to delete from (dbo.tbl_ARM_Path) currently contains just above 1.8m records.

    The below query shows how heavy this is:

     

    SPID Status BlkBy DBName CommandType ObjectName ElapsedMS CPUTime IOReads IOWrites LastWaitType StartTime Protocol
    63 running 0 DPMDB DELETE dbo.prc_ARM_Pruning_PrunePathTable 2803691 2132298 2113776704 2255238 SOS_SCHEDULER_YIELD 02/06/2011 09:08 Shared memory

    DELETE

     FROM dbo.tbl_ARM_Path

     WHERE DatasourceSequenceNumber = @DatasourceSequenceNumber

     AND PathId NOT IN (SELECT PathId FROM #PathIdList)

    Could anyone confirm if this is expected behaviour and if not is there anyway to improve performance for this sproc?

    Thanks
    Martin

    Thursday, June 2, 2011 9:04 AM

All replies

  • May have found the answer to my own question - found index pk__ARM_Path__DatasourceSeqNum_Path1_SequenceNumber to be heavily fragmented,so I've run a rebuild against it. Will monitor and report back on if this has any effect.
    Thursday, June 2, 2011 10:23 AM
  • Checked this morning and if anything this is now even worse than before, as it's eating so much resource that protection jobs are failing.

    Any help would be appreciated.

    Thanks,
    Martin

    Friday, June 3, 2011 8:43 AM
  • Hi Martin, 

    What did you do with this issue in the end? We have the same problem. I'm inclined to follow suggestions from the Management Console on adding indexes but am unsure.

    thanks

    Matthew

    Sunday, March 4, 2012 10:05 AM