What’s going on, on my server, right now?

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1252

    Comments posted to this topic are about the item What’s going on, on my server, right now?

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

  • ianstirk

    Ten Centuries

    Points: 1310

    Nice article!

    You might also want to look at "What SQL Statements Are Currently Executing?"

    http://www.sqlservercentral.com/articles/DMV/64425/

    Thanks

    Ian

  • sqlpanther

    Ten Centuries

    Points: 1204

    what version's of SS will support you did not mention I am getting 2 errors...

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near '90'. ( I changed it to 90 as we are using SS2k5 Sp1)

    Msg 102, Level 15, State 6, Line 1

    Incorrect syntax near 'HONOR_BROKER_PRIORITY'.

  • Grant Fritchey

    SSC Guru

    Points: 395417

    Nicely done. Just curious why you aren't using the Dynamic Management Views (DMV) to retrieve a lot of that information?

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Geoff A

    SSChampion

    Points: 11407

    what are you using to detect CPU is at 90%? the alert you create does not appear to have the required component of

    @performance_condition=N'Resource Pool Stats|CPU usage %|default|>|90',

    also, this code is bad;

    SELECT

    convert(varchar(50), program_name),

    count(*),

    sum(cpu),

    sum(datediff(second, login_time, getdate()))

    FROM master..sysprocesses

    WHERE spid > 50

    GROUP BY convert(varchar(50), program_name)

    ORDER BY 7 DESC

    you only have 4 columns.... you can't ORDER BY the 7th column.

    It's an interesting approach overall. I am fan of email alerting on certain conditions occuring on the server.

  • Anil Maharjan

    SSC Veteran

    Points: 221

    Nice post, I think there is an issue in code line PROGRAM_NAME not in (''Microsoft SQL Server Management Studio '') but it's an simple mistake :).

    Really glad to see such a nice post.

    Thanks,

    Anil Maharjan

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1252

    Sorry, my bad! SQL Server 2008 R2 is what I have it running on.

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1252

    I believe I said in my article that you can attach this to any alert you wanted, however good catch, i'll update my article.

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1252

    You could definately use the DMV for this info as well. I just like to have an alternative. I always set up a batch of reports for my clients and these just fit in well with my personal solution.

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1252

    @Ian - great addition! I will add this to my daily use. Thanks.

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

  • SQLQuest29

    SSCrazy Eights

    Points: 8203

    Kim Killian-SiteDataView (7/22/2013)


    Comments posted to this topic are about the item <A HREF="/articles/SQL+Monitoring/100615/">What’s going on, on my server, right now?</A>

    Have you looked into sp_whoisActive .. it is much more powerful than sp_who2 ?

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • SQLQuest29

    SSCrazy Eights

    Points: 8203

    Kim Killian-SiteDataView (7/22/2013)


    Comments posted to this topic are about the item <A HREF="/articles/SQL+Monitoring/100615/">What’s going on, on my server, right now?</A>

    Have you looked into sp_whoisActive .. it is much more powerful than sp_who2 ?

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • Robert.Sterbal

    SSCrazy

    Points: 2855

    Have a conscious strategy to deal with table locking on the system tables. You are probably better off getting an inconsistent view of the data then holding up the server with a lock.

  • The Toddibear

    Old Hand

    Points: 384

    Okay, I've been meaning to do something like this for a while, but to have it laid out as a copy/paste job! Must say I am impressed. Intend to use this!

  • james.of.rivendell

    Old Hand

    Points: 375

    Great article. However, personallly I wouldn't use column numbers in the order by clause. It's a bit of a bad habit in my opinion and can cause issues later on down the track.

Viewing 15 posts - 1 through 15 (of 34 total)

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