SQL Server Performance

Active SQL Statements

Discussion in 'Performance Tuning for DBAs' started by Voltaire, Aug 31, 2005.

  1. Voltaire New Member

    Hi All,

    Can someone tell me what kind of statement can be run to see all active statements on the database, who is running them (which sid) and it's locks without using enterprise manager? I would like to run this directly out of SQL analyzer. sp_who only gives me the type of select statement. Not the actual statement.

    Thanks in advance!

  2. Luis Martin Moderator

    Profiler is the best tool for that.

    Luis Martin

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell

    All postings are provided “AS IS” with no warranties for accuracy.

  3. Chappy New Member

    sp_who2 will give a little more info, but still wont show the statement itself

    DBCC INPUTBUFFER(SPID) will show the most recent batch sent from a given connection (SPID), although this will not show whether that statement is still executing.

    I think in my experience it is rare to want to see each and every statement which is currently executing, without having at least some idea of the connections involved. Why do you need this info?

    If you have a performance problem that is unexpected and so you do not have profiler running beforehand, you can usually idenfity problematic connections by looking at the blocks in sp_who2 or the locks in sp_locks

Share This Page