SQL Server Performance

diagnose resource monitor and high cpu cycles

Discussion in 'SQL Server 2005 General DBA Questions' started by colinr, Jun 24, 2008.

  1. colinr New Member

    Sql2005 server running on vmware.
    We have an intermittent performance problem on our virtual server CPU suddenly jumps from its normal 25% to 75% at which point the main application becomes very slow the cpu usage doesnt seem to come down even after several hours. If we run Activity monitor from management studio the highest consumer of cpu time is the ResourceMonitor and the CPU usage is escalating rapidly.If we stop and restart the sql service performance returns to normal but obviously this is no solution.
    Does anyone have any information on diagnosing what the Resource monitor is actually doing at the time ?
    It appears the current Version is RTM and we are aware this needs an urgent Service Pack 2 installation but that schedule is two weeks away unless we can verify this as a known issue ...
  2. techbabu303 New Member

    Can you do quick check with following query listed below , probabaly schedule it for every 4 minutes for 24 hours
    select top 10 pro.program_name,pro.cpu,pro.spid,
    pro.memusage,pro.physical_io,db.name,pro.status,
    pro.last_batch
    from sysprocesses pro,sysdatabases db
    where
    spid > 50
    and cpu > 50
    and memusage > 50
    and pro.dbid = db.dbid
    and pro.status !='sleeping'
    Also check perfmon trace for counters processor time , Processor Queue length and Buffer Cache hit Ratio.
    -Sat
  3. colinr New Member

    thanks,
    that shows me more or less what I can see in Activity Monitor filtered for running processes.
    Server has been up for about 10 hours so far and SP_who2 shows resource manager spid 1 as:
    1 BACKGROUND sa . . NULL RESOURCE MONITOR 2796 380 06/24 15:05:48 1 0
    which appears normal at some point usually late afternoon(GMT) this number for CPU (2796) Escalates rapidly becomming a six digit number within minutes but the Disk IO number remains static. Im looking to identify why the resource manager is using so much CPU at this Time. The Application is a web app that only has about a dozen connections at any one time and its unusual to see more than a couple in use concurrently.
    Dbcc inputbuffer() doesnt show anything unusual from these connections while the resource manager counter is escalating
  4. techbabu303 New Member

    How long does it sustain peak CPU usage ?
    If it occurs say for interval 30-40sec and 4 to 10 times a day , I would probably ignore it.
    -Sat
  5. techbabu303 New Member

    Also check the excerpt from the article
    The SQL Server Lazy Writer (as it's called in SQL Server 2000) or theResource Monitor (as it's called in SQL Server 2005) is another area tomonitor when CPU utilization is high. Flushing the buffer and procedurecaches can add to CPU time via the resource thread called the ResourceMonitor. The Resource Monitor is a SQL Server process that determineswhich pages to keep and which pages need to be flushed from the bufferpool to disk. Each page in the buffer and procedure caches areoriginally assigned a cost representing the resources that are consumedwhen that page is placed into the cache. This cost value is decrementedeach time the Resource Monitor scans it. When a request requires cachespace, the pages are flushed from memory based upon the cost associatedto each page; pages with the lowest values are the first to be flushed.The Resource Monitor's activity can be tracked through the LazyWrites/sec performance counter under the SQL Server: Buffer Managerobject within PerfMon. You should track how this value trends todetermine what threshold is typical on your system. This counter isusually reviewed along with the Page Life Expectancy andCheckpoints/sec counters to determine whether there is memory pressure.
    Reference
    http://technet.microsoft.com/en-us/magazine/cc137784(TechNet.10).aspx
    Hope this helps
    -Sat
  6. satya Moderator

  7. HI,

    Thanks for the above query.

    But on my production machine I frequently get processes that time CPU cycles in thousands, my question for you is that as perfmon gives us the value in terms of percentage used, similarly can we have this in terms of percentage to get a more realistic picture.

    Likewise can we have some methodology for deciding the threshold or maximum allowed values for column [CPU],[MEMUSAGE], which we can use to catch processes that are really taking up lot of resources.

    Thanks,
    Mani

Share This Page