Find the longest running queries on MS SQL Server

The following SQL query will report the top 10 longest running queries.
The time is in milliseconds.

select distinct top 10
substring(t.text, (s.statement_start_offset/2)+1, (((case s.statement_end_offset when -1 then datalength(t.text) else s.statement_end_offset end) – s.statement_start_offset)/2) + 1) as SqlText
, (case when s.statement_start_offset > 0 then substring(t.text, 0, (s.statement_start_offset/2)+1) else ” end) as SqlParameters
, s.execution_count as ExecutionCount
, (s.max_elapsed_time/1000) as MaxElapsedTimeMs
, (s.min_elapsed_time/1000) as MinElapsedTimeMs
, isnull((s.total_elapsed_time/1000) / nullif(s.execution_count, 0), 0) as AvgElapsedTimeMs
, s.creation_time as LogCreatedOn
, s.last_execution_time as LastExecutionTime
, isnull(s.execution_count / nullif(datediff(s, s.creation_time, getdate()), 0), 0) as FrequencyPerSec
from
sys.dm_exec_query_stats as s
cross apply
sys.dm_exec_sql_text(s.sql_handle) as t
order by
isnull((s.total_elapsed_time/1000) / nullif(s.execution_count, 0), 0) desc

Reference:
dans.blog

Advertisements

21 thoughts on “Find the longest running queries on MS SQL Server

  1. An impressive share, I just given this onto a colleague who was doing just a little evaluation on this. And he the truth is purchased me breakfast as a result of I discovered it for him.. smile. So let me reword that: Thnx for the treat! But yeah Thnkx for spending the time to discuss this, I feel strongly about it and love reading extra on this topic. If doable, as you change into experience, would you mind updating your blog with more details? It is extremely helpful for me. Large thumb up for this blog post!

  2. Hello, I’m genuinely thankful I’ve found this data. At present web owners submit just about rumours as well as web and this will be irritating. A good web-site using useful subject material, this is exactly what I needed. Thanks for preserving this website, I’ll be checking out it all. Is the next step for newsletters? Are not able to believe that it is. Choice options

  3. Many thanks for your blog post post. Brown i happen to be conserving to obtain a innovative tutorial on this topic with your article has created us all to conserve all of our cash. Your notions extremely solved the whole problems. In actual fact, above might know about got recognized earlier than the amount of time most people ran to your impressive site. I actually no longer foster questions along with a troubled imagination because you seriously dealth with our desires in this article. With thanks

  4. They are drinking water resistant up to fifty meters and have Gucci crest and
    emblem engraved on the case again. Perhaps those that have style contacting
    but don’t feature like the common cellphone that you really keep for an ears.

  5. 専任の開発チームはまた完璧な
    シャネルやルイヴ
    ィトンのレプリカのハンドバッグ
    を作るために使
    用される非常に最高の素
    材やアクセサリー
    を選択し購入するために
    私達の調達部門と手をつないで動作
    します

  6. Non smokers experienced an increase in airway resistance from a mean average of 182% to 206%.
    ) That is actually the best part ‘ imagine not being able to experience all of those while trying to change to a healthier lifestyle. Are lives and lung health the real issue here or is nicotine addiction.

  7. Hey I know this is off topic but I was wondering if you knew of any widgets I could
    add to my blog that automatically tweet my newest twitter updates.
    I’ve been looking for a plug-in like this for quite some time and was hoping maybe you would have some experience with something like this. Please let me know if you run into anything. I truly enjoy reading your blog and I look forward to your new updates.

  8. When I initially commented I clicked the “Notify me when new comments are added” checkbox and now each time a
    comment is added I get four e-mails with the same comment.
    Is there any way you can remove me from that service?
    Bless you!

  9. Yesterday, while I was at wߋrκ, my sisteг stole my iphone and tested to see if it can survive a 40 foot drop,
    just so ѕhe can be a youtube seոsation. My iƤad is now broken aոd she has 83 ѵiews.

    I know this is completely off topic but I haɗ to share it with someone!

  10. Do you have a spam problem on this site; I also am a
    blogger, and I was curious about your situation; many of us have created some nice methods and we
    are looking to trade solutions with other folks, be sure to
    shoot me an e-mail if interested.

  11. First off I would like to say fantastic blog! I had a quick question which I’d like to
    ask if you don’t mind. I was curious to find out how you center yourself and clear your head before writing.

    I’ve had difficulty clearing my thoughts in getting my ideas out there.
    I truly do enjoy writing but it just seems like the first 10 to
    15 minutes are lost simply just trying to figure out how to begin. Any recommendations or hints?
    Kudos!

  12. Thank you, I have recently been searching for info about this topic for
    ages and yours is the greatest I’ve discovered till now.
    But, what about the conclusion? Are you positive about
    the source?

  13. We stumbled over here different page and thought I might check things out.
    I like what I see so now i am following you. Look forward to finding out about your web page for a second time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s