SP_Who & SP_Who2

In an earlier lesson we looked at the sys.sysprocesses view and how we can use that to see what's occurring on our server. It works, but sometimes it's nice to get a slightly more filtered view and that's where we find value in sp_who and sp_who2. They provide slightly different output but do about the same thing, returning a result set that shows us what's going on and who is doing it, things that I think you'll agree are key questions for any DBA!

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

Team FAB on 1/6/2011

I didn't understand why there were no results returned from spid 52, as that was the spid of the current session, and clearly it was active as we were watching commands being run.

Jamshid Nouri on 1/6/2011

Great demo! Thanks Andy!

Ali on 1/6/2011

Thank you very much

Christopher Samulski on 1/6/2011

I wasn't aware you could filter sp_who and sp_who2 very nice!

kishore76 on 1/6/2011

Pl's add more videos like this.

joze on 1/6/2011

Thank you for sharing

Dean Dodson on 1/6/2011

Would've been neato if there'd been some blocking examples to show how sp_who sees locks and blocks.

Ed Secoges on 1/6/2011

Very good

TTC Tech User on 1/6/2011

Excellent as always! I ran into a situation the other day where I needed to do a count of a field and needed it grouped by day (I happened to have another field that was a date/time stamp of the other field to be counted). I've seen some really ugly code where someone had used cast/convert and parsed out the date as nvarchar and then sorted (I thought there should be a better a way). I found this little trick: DATEADD(day, DATEDIFF(day, 0, MyDB.MyDateField), 0) AS Mydate I've tested this and it seems to work well for just about any sort of date/time grouping one could want. Thing is, I dont fully understand how datediff and dateadd are being used here. Could you do a video on grouping by date/time? Is there a better method than what I found? Thanks! Eric pittse@co.kern.ca.us

Marcelo Marim on 1/6/2011

Again, very well done presentation. Thanks

Syed Arshad on 1/6/2011

very bad sound quality plus if you could have spent few extra min. to walk the way to kill this spids, that would have been very nice

Julie Bloomquist on 1/6/2011

Very clear and consise

Mark Hathaway on 1/6/2011

I especially appreciated the comment at the end about sp_helptext

Krishna Singaraju on 1/6/2011

Nice work

Mat on 1/6/2011

simple good example of a useful command.

Murali krishna MV on 1/6/2011

Great video

ravi on 1/6/2011

Its Good

ravi on 1/6/2011

Its Good

Eric on 1/7/2011

Fantastic subject matter, thank you very much. I would like to see another on these same procedures, but delving more into the meaning of the results. In addition, a separate video delving more into the meaning of the results of the system SPID's (if they're important; fact is, I've never concerned myself with system SPID's).

Olu on 1/10/2011

I have been using sp_who & sp_who2 for a long time now but I still learned something new from the video. Well done.

Ludwig on 1/12/2011

execellent

WChaster on 1/12/2011

How is it that when passing the parameters you do not have to say which column they apply to, ie: sp_who 'active'; does it compare the value to every column in every row?

chandrasekhar on 9/26/2011

useful video

trr on 9/30/2011

nice

se on 10/31/2011

good

wcrabb on 4/17/2012

good stuff, great for troubleshooting