Post reply

Problem with UDF(urgent)

  • January 19, 2007 at 9:49 am

    #117072

    I have an inline user defined function which consists of select statement.This function used to take 5 mins to execute but from last couple of days its taking 4 hrs to execute.I don't see any blocking and deadlocks.I have applied reindexing and update statiscs on the table in select statement and still same result.I restored same database on UAT box and executed the UDF and its taking just 5 minutes.Cpu,memory looks good.

     

    Could you please anybody help me with this.Its very urgent.

     

    Thanks,

    SR

    Thanks,
    SR

  • January 22, 2007 at 2:47 am

    #684632

    Have you recompiled the PROC.

    Also looking into getting the actual execution plan.

    And finally, you may be experiencing "Parameter Sniffing"...if you do a search of this site (and SQLTeam.com) you will find descriptions + solutions to same.

  • January 22, 2007 at 9:05 am

    #684728

    Thanks for your response Murphy.Its not a stored procedure its a user defined function.I have seen the excution plan.Its almost same in both QA and Prod server.

    I will look into Parameter Sniffing.

     

    Thanks,

    SR

     

     

    Thanks,
    SR

  • January 23, 2007 at 8:13 am

    #684949

    Hi Andrew,

    Is Parameter sniffing is applicable for functions also?

    Thanks,

    Sree

     

     

    Thanks,
    SR

  • January 24, 2007 at 6:42 am

    #685180

    Don't know.....but if you can locate some links on SQLTeam.com....try asking the people who provided the answers.  They have more direct experience of the problem and the solution.

    Also....have you tried activating PROFILER to monitor the problem code.  That should give more detailed info on the cause of the problem.

  • January 25, 2007 at 8:05 am

    #685568

    Thanks Andrew.I will check it in sqlteam.com.

    I turned on trace but I didn't find anything unusual.

     

    Thanks,

    SR

    Thanks,
    SR

  • January 29, 2007 at 9:41 pm

    #686362

    pls check ur log file sometimes the log file may be heavy

    if so truncate the same

     

  • January 31, 2007 at 1:55 pm

    #686960

    Log file looks good Bose.

     

    Thanks,

    SR

    Thanks,
    SR

Viewing 8 posts - 1 through 7 (of 7 total)

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