SQL Server Performance

Does one long running thread/SPID cause others to run longer as well?

Discussion in 'SQL Server 2008 General DBA Questions' started by Faraz Beg, Aug 15, 2011.

  1. Faraz Beg New Member

    Greetings,

    I've looked around the forums but haven't been able to find the thread i was looking for, in all likelihood i'm not choosing correct keywords but anyway.

    Please have a look at this scenario,

    [IMG]

    As you can see, the chgAc SP has taken 21 secs to run. The other apps running during this time also took a lot of time. considering the fact that the server wasn't under any load and that none of the other SPs usually takes more than a few milliseconds, we've singled out the first statement as the cause of the slowness. However, i've been asked this question by my PM, why would one thread cause SQL server to serve other requests poorly? I think this is a valid question... I've seen threads on blocking issues, is it the same thing?

    In any case, is it normal or am i missing something?

    Thanks and awaiting answers.

    Faraz

    Attached Files:

  2. satya Moderator

    Welcome to the forums.
    As you have collected the trace information on amount of execution time, did you look at the system resource usage such as DISK, IO and Memory during this behaviour?

    The key element is LOCK resources by one SPID that may cause delay or wait for other resources that wants to work with the data. Further the delay of processes possible causes include:

    • Blocking.
    • System resource contention.
    • Application design problems.
    • A particular set of queries or stored procedures with long execution times.

    The documentation clearly defines that in the ideal situation when you see performance delay or loss it is best to capture TRACE (Profiler) and PERFMON (SYSMON) to capture the blocking output along with timeframe of resource usages of system
    which must encompass a time when application performance goes from good to bad. The combination of this information will help you to get a clearer picture of where the performance slowdown is occurring. Further if there are frequent changes to the data RECOMPILES process will cause much more problems.
    FYI
  3. Faraz Beg New Member

    Satya,

    Thanks for the quick response. We have an alert service that sends us notification in case of high disk/cpu usage and there wasn't such a notification at that time so we're pretty sure these metrics weren't too high. Furthermore, the ChgAcc Sp does have a tendency to run longer depending upon input so i'm sure it ran that long without any external factor.

    The factors you mentioned above, do I have to trace them as well or are they logged automatically by sql server?

    Thanks and awaiting response.

    Faraz
  4. satya Moderator

    Faraz
    you have to setup the PERFMON and any other monitoring tool that you may use to collect the information. Further if you feel that a specific SP is taking longer time than expected best to go through the Execution plan for that code to see if you can optimize it.

Share This Page