SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

sp_what

By Nicholas Williams,

Greetings! 
Do you hate scrolling unnessarily? Do you tire of looking through loads of blocking chains to find the head/root of the block? Do you have to deal with negative spids? Do you want to see the sql text of sessions activity? Do you want to filter to particular logins or see all connections through a particular database ? *
*Without having to switch paramaters, cuz you are lazy? (Efficient*) 
If you enjoy being lazy *Efficient*, and like looking like a SQL Badass and confusing your developers?  Do you want to capture sessions over a period of time?
..But mostly, Do you like memes? 

Well, then this is for you!

Description
Displays helpful info on what is currently happening, without the pain of searching for the blocking root/head and dbcc inputbuffer combined.
Also allows the filtering of sessions to either a spid, or a login, or a database name. (Active or inactive.)
Limitations: If a process is a job from another server, the call to search for the job id will fail - hiding this session. Will fix.
Also... prob need to hard code collation to get around some potential issues.
Maybe include a min version as standard (with minimal columns returned?) and then a "max" version with more info if required.
Output:
Be Aware that if there are no blocks/blocking - then the blocking column will not show up - minimalism being a design goal. (Want only info that I commonly use in troubleshooting/understanding sp_whats happening.)
So the columns  and data that show on the final result will change depending upon what activity is actually happening on the instance.
If there are any negative spids, then it will pull info about the source transaction and provide the kill command, for convenience. 
How to use:
Can be called on its own, without input and will display the active sessions, with any blocks.
Other inputs for the first parameter include:
  • Any valid login
  • Any Valid SPID
  • Any valid database
And the results will filter onto those sessions. (AND I really enjoy using conjunctions as the starting point of sentences. AND NO ONE CARES ANYMORE, Mrs Kazinsky!**)
  • EXEC sp_what --will be default return active sessions on the instance.
  • EXEC sp_what 'domain\login' --includes data on all active sessions from this login.
  • EXEC sp_what 'domain\login',0 --includes data on all sessions (inactive and active) from this login.
  • EXEC sp_what 115 --includes data on the session id 115
  • EXEC sp_what 'master' --includes all active sessions that are connected to the msdb database.
  • EXEC sp_what 'msdb', 0 --includes all sessions (inactive and active) that are connected to the msdb database.
  • EXEC sp_what 'msdb', 0, 5 --Executes the search 5 times, with a 0.5 delay per search, then reports on all data captured. (in this case all session for the msdb database.)
Included is the option to include or exclude only active sessions - and the option to run it multiple times and collect the results over a 
period of time.
Confusing your Developers
I like to save sp_what to my keyboard shortcuts of ctrl+3.
Its fun to highlight a string with a login name, or a spid and hit ctrl+3... and watch the developers faces as they try to see how a string or a spid can be sent to the same input. lol.*
*yes, i know this is sad. I get my laughs where i can.
**Finally, relief from english class.

Total article views: 591 | Views in the last 30 days: 104
 
Related Articles
FORUM
FORUM

Session blocking performance

SQL 2005 Performance drop due to blocking session

SCRIPT

TempDB usage per active session

Lists the TempDB usage per each active session.

FORUM

blocking

blocking

SCRIPT

Handling with currently running and blocking sessions

Checks currently running sessions and blocking queries with session information

Tags
-2    
active    
activity    
block    
blocking    
blocks    
database    
login    
logins    
negative spid    
sp_what    
sp_who    
sp_who2    
spid    
tracing    
ur mom    
 
Contribute