Post reply

Performance Monitoring

  • November 18, 2007 at 6:59 pm

    #122449

    Good day,

    I recently noticed that whenever the Disk Queue Length shoots up the CPU usage will be low and whenever the disk queue length drops the CPU usage will be higher. This has not been happening before.

    Need some advice on the possibilities of having such scenario and what are the actions need to be taken to rectify it?

    Thanks in advance!! 🙂

  • November 19, 2007 at 3:12 am

    #752180

    Hello,

    The latest articles recommend watching I/O latency performance counters instead of

    queue length.

    The high queue length can come of various reasons:

    datagrowth, recent software update.

    Have a look at

    http://www.sql-server-performance.com/articles/audit/main.aspx (performance audit)

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

    for interpreting performance counters.

    Sql server profiler is also handy for monitoring which queries are likely the cause of the I/O spike.

  • November 19, 2007 at 3:52 am

    #752188

    I agree with Jo. You do need to consider the growth patterns of your instance in relation to differing performance counters.

    As well as disk queue length and CPU, I'd also look at memory counters, number of reads/writes per second, batches per second to name a few.

    Additionally, it may be worth running a profiler trace to identify which queries are causing these IO spikes. It could be something as simple as out of date statistics which use a less than optimal query plan and cause a spike.

  • November 20, 2007 at 4:49 am

    #752596

    This actually makes sense if you think about it. Accessing hard drives for data (physical I/O) is SIX orders of magnitude slower than that same data flying through the CPUs (millisecond to nanosecond scales respectively). Thus if the cpus are saying "give me data" and that data has to come from hard drives and the hard drives are backed up for any reason the CPUs stand to have to wait a LOT to do their work -thus they show as idle.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • December 5, 2007 at 6:01 pm

    #757487

    thank you guys for providing such informative input..appreciate it 🙂

    I have added in the counters recommended.. hopefully it will give me a hint of what is going on..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply