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

Question

Post reply

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

Kim Killian-SiteDataView

Ten Centuries

Points: 1309
More actions
July 22, 2013 at 9:45 pm

#275557

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

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

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

ianstirk Ten Centuries Points: 1310 More actions · Answer 1

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 More actions · Answer 2

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: 398690 More actions · Answer 3

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Geoff A SSChampion Points: 11511 More actions · Answer 4

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 More actions · Answer 5

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: 1309 More actions · Answer 6

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: 1309 More actions · Answer 7

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: 1309 More actions · Answer 8

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: 1309 More actions · Answer 9

@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: 8264 More actions · Answer 10

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: 8264 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

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 SSC-Addicted Points: 425 More actions · Answer 14

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.