Understanding sp_who2

Kendra Little explains what the sp_who2 procedure does and how to read its results:

Let’s talk about sp_who2

I started out using sp_who2, also! And I was often confused by sp_who2.

sp_who2 is a built-in stored procedure in SQL Server.

  • Shows a lot of sessions, even on an idle instance
  • Doesn’t tell you much about what it’s doing

Here’s what an idle SQL Server looks like in sp_who2

This is my dev SQL Server instance. I have a few sessions open, but the only one which was executing anything was the one you see, where I ran sp_who2. I don’t have any agent jobs scheduled on this thing. It’s just waiting for something to happen.

It’s hard to know what to look at, because we see so much. And only 19 of the 49 sessions is on the screen, too.

Kendra then goes on to explain that there are better ways of getting this information and plugs sp_whoisactive.  I’m 100% in agreement.

Related Posts

Managing Central Management Server

Chrissy LeMaire shows how you can use dbatools to manage Central Management Server and registered servers: It’s a super useful feature that not all DBAs know about. Since CMS data is stored in msdb and accessible via SMO, you can access it from SQL Server Management Studio or PowerShell modules like dbatools. Central Management Server’s essential […]

Read More

Checking File Sizes In SQL Server

Andy Mallon looks back at a contribution by Junior DBA Andy, this one on checking file sizes: This is every DBA’s favorite game. Figuring out what DMV contains the data you want. It turns out there are two places that database file size info is maintained. Each database has sys.database_files which has information for that database. The […]

Read More

Categories

February 2017
MTWTFSS
« Jan Mar »
 12345
6789101112
13141516171819
20212223242526
2728