A command I like to make use of when efficiency tuning is DBCC INPUTBUFFER. When you have ever run sp_whoisactive or sp_who2 to search out out what periods are executing when CPU is excessive for example this is usually a actual fast life saver. At occasions, for me, these two choices don’t return sufficient info for what I’m in search of which is the related saved process or object. Utilizing this little helper together with the session id can simply get you that info.
Let’s have a look.
First, I’ll create a easy process to generate a workload.
CREATE OR ALTER PROCEDURE KeepRunning
AS
DECLARE @i INT=1
WHILE (@i <1000)
BEGIN
choose @@servername
WAITFOR DELAY ’00:00:30′
choose @i=@i+1
END
Now I’ll execute the process and seize what it appears to be like like utilizing first sp_who2 after which sp_whoisactive. Trying on the Sp_who2 display screen shot all you’ll be able to see is the session info together with command and standing that’s being run however do not know from what process it’s being run from.
Exec KeepRunning
Now take it a step additional and let’s run sp_whoisactive. Right here we get extra info such because the sql_text being run.
Be aware of the session id, displayed with both software, which on this case is 93. Now run DBCC INPUTBUFFER for that session.
DBCC INPUTBUFFER (93)
BINGO! We’ve now received what we would have liked which is the title of the shop process that the assertion is related to.
Now let’s strive one last item. Bear in mind I mentioned sp_whoisactive doesn’t give us the shop process title, properly that wasn’t 100% true. There are incredible parameter choices we are able to us that may get us much more info. Let’s run sp_whoisactive utilizing the parameter @get_outer_command = 1. Proven within the screenshot you’ll be able to see right here it primarily the identical factor as DBCC INPUTBUFFER supplying you with the sql_command i.e. the shop process title.
Abstract
Rapidly realizing the saved process related to the question that’s inflicting points permits us to simply comply with the breadcrumbs to establish the basis explanation for a difficulty. For those who can’t run third get together or group instruments like sp_whoisactive, dbcc inputbuffer is another for you. Subsequently, I needed to introduce DBCC INPUTBUFFER. Including this little tidbit to your efficiency tuning toolbox is usually a actual time saver,, you will have different helpful methods to make use of it as properly. Make certain to take a look.
