Post reply

Procedure Executionn Time Problem

  • January 26, 2011 at 1:41 am

    #230813

    Hi All,

    I have a interesting problem and I dont understand cause of problem.

    Please help me =)

    On production system,I have a sp which calculate price ,after restarting the sql server , I run this sp it is execution time is 3 sec.

    After a period of time,exec time is longer than 30 sec.

    If I recompile procedure,running time drops again 3 sec..

    Do you have any idea about this problem?

    Thanks a lot..

  • January 26, 2011 at 3:20 am

    #1278363

    Could be a lot of things. Parameter sniffing? Statistics out of date?

    Have you tried profiling the procedure at statement level, including the actual execution plan?

    -- Gianluca Sartori

  • January 26, 2011 at 3:39 am

    #1278376

    Yes,the execution plans are the same all time..

    but some of table statistics are not run..

  • January 26, 2011 at 4:18 am

    #1278391

    Are you running with a different parameter set? It could be parameter sniffing.

    -- Gianluca Sartori

  • January 26, 2011 at 4:31 am

    #1278396

    No,I run the same parameters

  • January 26, 2011 at 6:02 am

    #1278426

    It doesn't make any sense that the time would vary, a recompile fixes the time, but you're looking at identical execution plans. Based on the situation you describe, the exec plans must be different. Look closer at them, at some of the details. It must be varying.

    The only other thing I can think of is resource contention. Have you looked to see if the process is being blocked when it's running long?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 26, 2011 at 6:02 am

    #1278427

    Can you post the execution plan?

    -- Gianluca Sartori

  • January 26, 2011 at 6:18 am

    #1278430

    Sorry,I cant post execution plan.is the problem related with plan cache?

  • January 26, 2011 at 6:41 am

    #1278437

    pektas.isil (1/26/2011)

    Sorry,I cant post execution plan.is the problem related with plan cache?

    I don't think so, since you said the plan is correct.

    I suggest that you identify with a trace which statement of the procedure takes longer and try to investigate that part alone.

    -- Gianluca Sartori

  • January 26, 2011 at 7:15 am

    #1278456

    Ok,thank you.I will try

Viewing 10 posts - 1 through 9 (of 9 total)

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