Post reply

Simple SQL (SET) statements taking time in our recent trace file

  • November 11, 2011 at 3:28 pm

    #144507

    Hi All,

    Please find the attached screen grab to see the simple SET statements in our production trace taking close to one second. Could any one of you guys please help me to understand why these statements could be taking time?

    Thanks,

    Suresh

    Regards,
    Suresh Arumugam

  • November 14, 2011 at 1:21 pm

    #1407500

    This is just a wild guess, but if the variable you are trying to set is a decimal type perhaps a numeric(18,6) or something like that you should actually set it to 0.0 instead of 0. Otherwise you can get a performance hit on trying to convert an integer "0" to decimal "0.0"

  • November 14, 2011 at 1:25 pm

    #1407501

    Did you catch the wait types when they were running?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 14, 2011 at 1:34 pm

    #1407502

    My guess would be that there is more code on other lines within the TextData column that is not showing in the grid view.

    Try looking at the TextData in a text view mode.

  • November 14, 2011 at 1:35 pm

    #1407503

    Thanks for the suggestions.

    Could you please clarify how to check for the wait types? Sorry i am not sure of that.

    Thanks,

    Suresh

    Regards,
    Suresh Arumugam

  • November 14, 2011 at 2:12 pm

    #1407528

    I'd start with, is there more code here?

    1 sec for this is extreme.

  • November 14, 2011 at 2:32 pm

    #1407538

    Ninja's_RGR'us (11/14/2011)

    I'd start with, is there more code here?

    1 sec for this is extreme.

    However note that the CPU time is 0.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 14, 2011 at 2:32 pm

    #1407539

    Suresh Kumar-284278 (11/14/2011)

    Thanks for the suggestions.

    Could you please clarify how to check for the wait types? Sorry i am not sure of that.

    Thanks,

    Suresh

    Check sys.dm_exec_requests while they are running.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 14, 2011 at 3:23 pm

    #1407558

    GilaMonster (11/14/2011)

    Ninja's_RGR'us (11/14/2011)

    I'd start with, is there more code here?

    1 sec for this is extreme.

    However note that the CPU time is 0.

    Wow, I need to find myself a busy bank to learn that stuff. :Whistling:

    Tx, again.

  • November 14, 2011 at 3:40 pm

    #1407566

    GilaMonster (11/14/2011)

    Ninja's_RGR'us (11/14/2011)

    I'd start with, is there more code here?

    1 sec for this is extreme.

    However note that the CPU time is 0.

    Maybe the hidden code is:

    waitfor delay '00:00:01.000'

    🙂

  • November 14, 2011 at 4:13 pm

    #1407575

    Michael Valentine Jones (11/14/2011)

    GilaMonster (11/14/2011)

    Ninja's_RGR'us (11/14/2011)

    I'd start with, is there more code here?

    1 sec for this is extreme.

    However note that the CPU time is 0.

    Maybe the hidden code is:

    waitfor delay '00:00:01.000'

    🙂

    Not the most obvious option, but sure :hehe:.

    And AFAIK, waitfor is not 100% precise to the ms so that could be possible (unless there's an unknown factor I don't know of on an dev machine with single user.)

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

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