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

    Unanswered: Random high cpu in profiler

    Hi,

    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
    Begin Transaction
    exec random_stored_procedure (sp with a delete, update, etc... nothing too fancy)
    Commit transaction
    End


    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
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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
  •