Quick one today:

Frequently, need arises to check what is running in some of the SPID’s on your Sql Instances. As always, with Sql Server, there are several approaches to this.

Use case:

One necessary and unavoidable fallout of concurrency is locks and latches. To maintain data integrity, Sql Server employs some internal structures to serialize certain overlapping actions. This results in short lived locks where one process (SPID) is blocking other process (SPID). In some worst case scenarios, this blocking goes on for extended period of time resulting in appearance of application slowness. In these situations, you want to uncover the SPID that is blocking everything else (you could do that with sp_who2 or using Activity Monitor). Then you want to know what is that process running?

Following are some options:

Using InputBuffer

InputBuffer

Using DMF’s

SysProcesses

Using DMF’s

This is a depricated feature, so please fall back on using above options more.

Sql Text function