Post reply

sp_who2

  • February 25, 2014 at 7:34 am

    #290346

    This question is a little vague, but I'm hoping to understand a little more about the results of sp_who2. There are times when I either compile a stored procedure, or right now as I am creating indexes on tables, when sp_who2 shows a SPID assigned to my name that I do not currently have opened in SSMS. When I check the statement with dbcc inputbuffer it shows a query hitting many of the system views. Earlier this week, someone tried killing one of these SPIDS and SQL Server generated a new SPID with the same type of query.

    I'm curious if someone may be able to shed some light on what's going on behind the scenes.

  • February 25, 2014 at 7:57 am

    #1691898

    If I were you I would look into sp_whoisactive, much better than sp_who2

  • February 25, 2014 at 8:02 am

    #1691903

    stormsentinelcammy (2/25/2014)

    If I were you I would look into sp_whoisactive, much better than sp_who2

    +100!! sp_whoisactive @help = 1

    Adam Machanic has a 30-day blog post series on sqlblog.com too.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • February 25, 2014 at 8:07 am

    #1691908

    And if you don't want to or can't run sp_whoisactive, I would suggest getting familiar with the Dynamic Management Objects and run queries against them rather than using sp_who2. That's a very old school, pre-2005, method for gathering information about the system. I never use it any more.

    "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

  • February 25, 2014 at 9:13 am

    #1691945

    Thanks for the tips. I grabbed a copy of WhoIsActive, really nice stuff.

    I have started to transition to use the system views and functions, but everyone I work with still uses sp_who2. I'd like to be able to give some type of explanation of what that SPID was doing if anyone has an idea. I'd also like to know for myself what SQL Server is doing in the background when you run these commands.

  • February 25, 2014 at 9:29 am

    #1691952

    I can't say for sure without looking at the exact statement it is running, but SSMS will initiate background checks for different operations. If you go to the index rebuild, it will run DBCC to check fragmentation. Other tasks might check statistics, etc.

Viewing 6 posts - 1 through 5 (of 5 total)

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