Results 1 to 2 of 2
  1. #1
    Join Date
    May 2010

    Unanswered: Random high cpu in profiler


    i've been doing some test and monitoring with profiler, because we have some random timeout occuring.

    To replicate the problem i execute something similar to this to simluate a load on the server:

    while 1=1
    Begin Transaction
    exec random_stored_procedure (sp with a delete, update, etc... nothing too fancy)
    Commit transaction

    Everything run smooth with ~12 instance running non-stop of the previous code. The table affected by the stored procedure have about 12 rows and they have good indexes and PK to support millions of rows anyway.

    The problem i had is that sometime, we have random high cpu duration(millisecond) appearing in the profiler for either the "commit transaction", the "begin transaction" or even the stored procedure. We may have 60 of them for a 10 min duration, and cpu ranging from 300ms to 3000ms.

    Except these random occurence, everything is running smoothly, with cpu/duration under 16ms and read/write under 10. Also data/log file auto-grow is disabled since it was causing a slow down sometime, now the file are set with a very large initial value.

    anyone might have a slight idea why this random cpu peak happen? We have an application that need more than 800 tps and it's causing lag and timeout issues

    Thanks for your time

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 17
    Could be a recompilation of the stored procedure. Monitor for the sp:Recompile event.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts