Post reply

Out of ideas

  • May 16, 2008 at 8:03 am

    #75215

    I have some performance issues which I can not explain; The database it is running on on a dedicated SQL server box with 8 cpu’s 4 GB memory. Users are connecting with a fat client.

    There is hardly on users using it but they are getting long delays. So I kicked of a profiler trace and sp_blocker. I noticed some queries taking over 30 sec to complete in profiler but when I run them in query analyzer they run in under a second.

    I check the output from the blocker and there was no blocking over that time period and it was the only processes actually running.

    How can it be so slow I have run out of ideas please any ideas would be welcome

    Many thanks

  • May 16, 2008 at 9:01 am

    #816567

    What kind of connection do they have to the database server? Bandwidth can be a killer, especially depending on the size of the resultset being returned.

    -- You can't be late until you show up.

  • May 16, 2008 at 9:10 am

    #816577

    how could band width effect my profile trace?

  • May 16, 2008 at 9:16 am

    #816580

    Maybe I misunderstood something here. I thought you were profiling your user community as when you run these same queries in QA, there are no issues. What exactly are you tracing, which events? Can you post the results as well?

    -- You can't be late until you show up.

  • May 16, 2008 at 9:27 am

    #816587

    It would be good to know if you're tracing the completed events, not sure if they require the final ACK from the client.

    Are there stored procs or just T-SQL in the trace. QA can have different plans, which is incredibly annoying.

    Any more details you can give us? If you reboot are things slow from the get-go?

  • May 16, 2008 at 10:09 am

    #816620

    I am just doing a standard duration trace. I am unable to reboot as it is a live system. I will try flushing the procedure cache its mainly T-SQL rather than SP's.

    How many plans can there be? Can I force QA to use diffrent plans to see if I can incease the duration?

    Many thanks

  • May 16, 2008 at 10:20 pm

    #816827

    Edward (5/16/2008)

    I am just doing a standard duration trace. I am unable to reboot as it is a live system. I will try flushing the procedure cache its mainly T-SQL rather than SP's.

    How many plans can there be? Can I force QA to use diffrent plans to see if I can incease the duration?

    Many thanks

    You mean it's embedded SQL in GUI code or ???

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 19, 2008 at 10:23 am

    #817295

    Jeff Moden (5/16/2008)

    Edward (5/16/2008)

    I am just doing a standard duration trace. I am unable to reboot as it is a live system. I will try flushing the procedure cache its mainly T-SQL rather than SP's.

    How many plans can there be? Can I force QA to use diffrent plans to see if I can incease the duration?

    Many thanks

    You mean it's embedded SQL in GUI code or ???

    yes the code is genrated from GUI apllication

  • May 19, 2008 at 6:17 pm

    #817532

    In that case, it may very well be that it's remembering a "bad" plan based on a previous query. That's why I use stored procedures instead of embedded SQL so I can tweek it with thing like WITH RECOMPILE or trick it by getting around "parameter sniffing", etc.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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