Sql-server – SQL Query to find the location of the running query

activity-monitorperformancequery-performancesql server

I am trying to get the information about the running queries for query performance monitoring purpose.

Is there a way to get the location of the running query in SQL-server?
For instance, if I have a stored procedure running in a server and if I'm getting all the activity monitoring data for the running query, I want to fetch the path of the stored procedure in which the query is running.

Is there a query to do this or any other way to get this path?

Example Path/hierarchy : sever/db/stored_procedure_name/query

Best Answer

I would use sp_whoisactive with @get_full_inner_text and @get_outer_command :

--- download it from http://whoisactive.com
EXEC sp_WhoIsActive 
    @filter = '', 
    @filter_type = 'session', 
    @not_filter = '', 
    @not_filter_type = 'session', 
    @show_own_spid = 0, 
    @show_system_spids = 0, 
    @show_sleeping_spids = 1, 
    @get_full_inner_text = 1, 
    @get_plans = 1, 
    @get_outer_command = 1, 
    @get_transaction_info = 0, 
    @get_task_info = 1, 
    @get_locks = 0, 
    @get_avg_time = 0, 
    @get_additional_info = 0, 
    @find_block_leaders = 1, 
    @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
Related Question