sp_who5

I wanted to share a procedure that I wrote that I use every day for checking what is active on the server.   This is not anywhere near as thorough as something like Adam Machanic’s fantastic sp_whoisactive or Brent Ozar’s sp_askBrent but to me it works better for me than sp_who or  the undocumented sp_who2.  I find it to be a very useful way to see what is running, what specific statement is running if it is a multistatement procedure.  I also find this same procedure useful for capturing data on a regular basis (every minute or so) to get data on your server with regularity and without much overhead at all.   Obviously you might be able to get some of the same things from the System Health Session or from a handful of other ways, but this will quickly show you processes that are hitting the system hard, how many threads they are using, what their wait type is, who they are blocking, and what specific statement is running.

I am betting that there are some enhancements that would make this better, so please share them if you have thoughts and feel free to use this wherever you would like.

 

USE master;
GO
IF OBJECT_ID('master..sp_who5') IS NOT NULL
	DROP PROCEDURE dbo.sp_who5;
GO
CREATE procedure sp_who5
as
SELECT er.session_Id
	, blocked
	, DB_NAME(sp.dbid) as database_name
	, er.percent_complete
	, ecid as thread
	, nt_username as [user]	, er.status as status
	, case when er.statement_start_offset > 1 and er.statement_end_offset> 1
		THEN SUBSTRING (qt.text, er.statement_start_offset/2, 
							(CASE WHEN er.statement_end_offset = -1 
									THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
									ELSE er.statement_end_offset END - er.statement_start_offset)/2) 
		ELSE qt.text end as CurrentQuery
	, er.command
	, qt.text as ParentQuery
	, program_name 
	, Hostname
	, start_time
	, wait_type 
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE er.session_id > 50    -- no system spids.
AND er.session_Id NOT IN (@@SPID)  -- remove current statements
ORDER BY er.session_id
		, blocked;

My Favorite SQL Bloggers

Since I am doing my best to be humble here, I figure for my first post I may as well point out a very clear reason why…all of the great bloggers in the SQL community.   I would like to list and bunch of my favorites, as I think these are all phenomenal and have just a world of information between them.  In no particular order:

 

Kimberly Tripp I am not sure there is anyone that knows performance tuning, indexing, statistics, etc… anywhere close to as well as Kimberly.   It was one of her all day pre-cons at Connections (before Intersection came to be) that really got me excited about SQL Server and her blog contains a world of phenomenal information.

Paul Randal – He wrote DBCC CheckDB, he knows the internals of the storage engine as well as anyone and he explains them in ways to make all of us understand how we can apply that information to our daily lives.

The Brent Ozar Team – Brent, Kendra, Jeremiah, Doug and Eric (as of this writing…they can always add more) all have a style of writing that brings a humor alongside of tremendous technical detail in a way that really no other SQL blogger does.   Their blogs and videos share insight into a number of aspects of SQL Server in ways that are easily adapted to our environments.

Midnight DBA – They show you how to be an Enterprise DBA and they do it with a humor that is like no other in the SQL community.   Go here, be a groupie.

SQL Performance – This is a site that is supported by SQL Sentry, that contains a number of the other bloggers on my list.   It has information at all levels and is full of great knowledge.

Grant Fritchey – The Scary DBA tells us a tremendous amount about a variety of things but some of the things that he speaks/blogs about that I have found incredibly insightful have been things dealing with Microsoft Azure SQL Database and some of the inner workings of the PASS board (as he is currently on that Board).  Great personality to go with that content.

Devin Knight – Most of the Pragmatic Works crew can give some great insights into BI in general, and what I have found great about Devin, in particular, are his insights into the Power Stack and its integration with Excel.   Easy to follow, step-by-step walkthroughs into using these tools are what I have found the absolute best about Devin.

Glenn Berry – No other blogger in the SQL Community writes about hardware anywhere close to the perspective that Glenn brings to us.    Questions about new processors and how they might work with a SQL Server?  This is your guy.   Couple that with his Diagnostic queries and this is one of the more valuable blogs out there, that I go to with regularity.

Jonathan Kehayias – He does a lot of things really well, but I am constantly going here when I am looking for information on Extended Events.   If he isn’t number 1 in that area of SQL Server, I can’t think of anyone who puts out that level of content.

Pinal Dave – If you have been working with SQL Server for any time at all, you have probably ended up on this page.  Pinal has a ton of content, is easy to digest and covers real world examples as well as any.   I find this page better than Books Online for a lot of things.

Thomas LaRock – A ton of great information about a variety of Enterprise DBA topics.  A fun person to read with great information to go with it.

Kevin Kline – Kevin has great content about SQL Server but what I appreciate about him the most are his insights into Professional Development.

Michael J. Swart –  Great content, but I think what makes him stand out the most is his artistic additions to his blog.   Obviously a multi-talented individual and fun to read because of it.

Rob Farley – Excellent content that is usually at a very high level.   He covers a variety of SQL topics and has a great perspective on professional development.   If you get a chance to watch him present, there are not many like him.

Brian Hansen – A close friend and a big help in inspiring me to get out there and blog as well.   He goes into great detail about a few topics but I think his Hekaton Concurrent Updates post is one of the better detailed Hekaton posts I have ever read.

Paul White – If you want to have your mind blown reading about the Optimizer, this is where you go.   No one covers it like Paul White.  He also writes for the SQL Performance site I noted above and he is a big part of why I love it as much as I do.

Aaron Bertrand – He has written for MSSQLTips and is another on the SQL Performance site and he covers a ton of things from performance to Service Broker.  One of my favorite posts of his that I still reference all the time is his take on the Merge Statement. And if you have ever asked a question on DBA Stackexchange there is a great chance that he has either answered or assisted with the answer of that question.

 

There are a ton more, and I may continue to modify this over time but these are all ones that have had a big impact on my career over the years.