Sql-server – When using DBCC INPUTBUFFER to find query associated with spid, is there a way to show variable values of that query

dbccsql servert-sql

I have been troubleshooting an issue within a database that has some serious blocking issues.

I found the command DBCC INPUTBUFFER a while back that allows me to see the query run for that spid. I have used this to see what is running and causing the issue.

What I am confused about is that the query that DBCC INPUTBUFFER shows does not include the variables associated with the query. For example, if the query joined on the @ID of a table, the provided query by DBCC INPUTBUFFER does not show the @ID value.

I am just wondering if there is a way to see what variable is associated with the query. And/or if maybe this is not the norm and normally DBCC INPUTBUFFER should show you the variable associated with the query.

If it is the latter, then my problem probably rests in the fact that a variable is not being provided and it is just querying a set of rather enormous tables and taking an eternity.

Best Answer

I have been troubleshooting an issue within a database that has some serious blocking issues. I am just wondering if there is a way to see what variable is associated with the query.

I would suggest, you should use either of below for troubleshooting blocking problems :

  1. You can use beta_lockinfo by Erland Sommarskog

    beta_lockinfo is a stored procedure that provides information about processes and the locks they hold as well their active transactions. beta_lockinfo is designed to gather as much information about a blocking situation as possible, so that you can instantly find the culprit and kill the blocking process if the situation is desperate. Then you can sit back and analyse the output from beta_lockinfo to understand how the blocking situation arose and figure out what actions to take to prevent the situation from reoccurring. The output from beta_lockinfo shows all active process as well as passive processes with locks, which objects they lock, what command they last submitted and which statement they are executing. You also get the query plans for the current statements. Normally, you run beta_lockinfo to look at the output directly, but there is also an archive mode where the data is saved to table. This is not the least useful, if you want someone to send you the output from beta_lockinfo at a site you don't have access to yourself.

  2. Another method is to use sp_whoIsActive by Adam Machanic with @get_locks = 1 and dump the data into a table for analysis

    EXEC sp_WhoIsActive 
@filter = '', 
@filter_type = 'session', 
@not_filter = '', 
@not_filter_type = 'session', 
@show_own_spid = 0, 
@show_system_spids = 0, 
@show_sleeping_spids = 0, 
@get_full_inner_text = 1, ----------> 1 will get full inner text of query
@get_plans = 1, 
@get_outer_command = 1,   ----------> 1 will get outer command of query
@get_transaction_info = 0, 
@get_task_info = 1, 
@get_locks = 1,   ----------> 1 = ON (get lock info); 0 = OFF
@get_avg_time = 0, 
@get_additional_info = 0, 
@find_block_leaders = 1,   --> 1 = will find Lead blockers
@delta_interval = 0, 
@output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]', 
@sort_order = '[start_time] ASC', 
@format_output = 1, 
@destination_table = '', 
@return_schema = 0, 
@schema = NULL, 
@help = 0

  3. There are other ways like Blocked Process Report Event Class or if you are using sql server 2008 and up, then Extended events, but above ones will give you the details that you are looking without much effort.

Related Question