Post reply

standard deviation

  • January 7, 2005 at 10:59 am

    #85045

    I'm posting this in administration because the end result is administration related, but the actual question is more of a trans-sql question:

    I have a pile of trace data that I've dumped into a table.

    I can take the following:

    SELECT sum(duration) as [total execution time], sum(duration)/count(*) as [avg execution time],  procedure_name from trace_output

    WHERE eventClass=10

    GROUP BY procedure_name

    This is helpful in that it tells me which queries have a high [avg execution time], but I want to probe deeper into those queries and check out their standard deviation. I figure if there is not much deviation, then it is a slow query, if there is deviation, then it is being blocked. Are there any built in functions or does anybody have any code that would pull standard dev from a column in a table?

    Thanks,

    -jmr

  • January 9, 2005 at 1:30 pm

    #536586

    Heya,

    SQL server has the built in function StDev for calculating the sdtd deviation (in BOL there is also StDevp...it uses the biased population formula- not sure which is more appropriate!):

    SELECT StDev(Col1)

    FROM Table1

    Hope this helps!

    Ben

Viewing 2 posts - 1 through 1 (of 1 total)

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