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.

CREATE OR ALTER PROCEDURE KeepRunning
AS
DECLARE @i INT=1
 
WHILE (@i <1000)
BEGIN
select @@servername
WAITFOR DELAY '00:00:30'
select @i=@i+1
END

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.

DBCC INPUTBUFFER (93)

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.

Summary

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.