Add DBCC INPUTBUFFER to Your Performance Tuning Toolbox

A command I like to use when performance tuning is DBCC INPUTBUFFER. If you have ever run sp_whoisactive or sp_who2 to find out what sessions are executing when CPU is high for instance this can be a real quick life saver. At times, for me, those two options do not return enough information for what I’m looking for which is the associated stored procedure or object. Using this little helper along with the session id can easily get you that information.

Let’s take a look.

First, I will create a simple procedure to generate a workload.

WHILE (@i <1000)
select @@servername
WAITFOR DELAY '00:00:30'
select @i=@i+1

Now I will execute the procedure and capture what it looks like using first sp_who2 and then sp_whoisactive. Looking at the Sp_who2 screen shot all you can see is the session information including command and status that is being run but have no idea from what procedure it is being run from.

Exec KeepRunning

Now take it a step further and let’s run sp_whoisactive. Here we get more information such as the sql_text being run.

Take note of the session id, displayed with either tool, which in this case is 93. Now run DBCC INPUTBUFFER for that session.


BINGO! We’ve now got what we needed which is the name of the store procedure that the statement is associated with.

Now let’s try one last thing. Remember I said sp_whoisactive does not give us the store procedure name, well that wasn’t 100% true. There are fantastic parameter options we can us that can get us even more information. Let’s run sp_whoisactive using the parameter @get_outer_command = 1. Shown in the screenshot you can see here it essentially the same thing as DBCC INPUTBUFFER giving you the sql_command i.e. the store procedure name.


Quickly knowing the stored procedure associated with the query that is causing issues allows us to easily follow the breadcrumbs to identify the root cause of an issue. If you cannot run third party or community tools like sp_whoisactive, dbcc inputbuffer is an alternative for you. Therefore, I wanted to introduce DBCC INPUTBUFFER. Adding this little tidbit to your performance tuning toolbox can be a real time saver,, you may have other useful ways to use it as well. Be sure to have a look.

The post Add DBCC INPUTBUFFER to Your Performance Tuning Toolbox appeared first on A Shot of SQLEspresso.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?