SQL Query to find the location of the running query?

Question

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

Answer 1

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

Answer 2

One method using the older Profiler application would be to enable a trace where you are capturing the SP:StmtStarting and SP:StmtCompleted event classes. That way, when a sproc is initiated, you can see the individual T-SQL statements being executed. The stored procedure being called should be identifiable with the SQL:StmtStarting and SQL:BatchStarting event classes. As for the hierarchy, you can include the server/instance name and current DB in the output to aid in rendering the output. The stored procedure and T-SQL query being executed will be in the TextData attribute so you'll need to do some text parsing to extract those elements from the output.

Answer 3

I would recommend to utilize extended events if you are able to capture the information you need.

There is code on MS SQL Tips written by Svetlana Golovko which will probably resolve the problem for you. I have included a copy of her code below, but please follow the link above for the full explanation. Her information on the extended events is at the very bottom.

Creating the extended event session.

CREATE EVENT SESSION [EXEC_SP] ON SERVER 
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1), 
     collect_statement=(0)
    ACTION(sqlserver.client_app_name, 
     sqlserver.client_hostname,
     sqlserver.database_id,
     sqlserver.database_name,
     sqlserver.username)
    WHERE (([object_type]=(8272)) 
     AND ([source_database_id]=(9)))) -- replace with your database ID
 ADD TARGET package0.asynchronous_file_target
  (
    SET FILENAME = N'E:\DBA_Audit\SP_Exec.xel',
    METADATAFILE = N'E:\DBA_Audit\SP_Exec.xem'
  );
GO

ALTER EVENT SESSION [EXEC_SP] ON SERVER
  STATE = START;
GO
--https://www.mssqltips.com/sqlservertip/3259/several-methods-to-collect-sql-server-stored-procedure-execution-history/ By Svetlana Golovko

Viewing data from the extended event session.

;WITH ee_data AS 
(
  SELECT data = CONVERT(XML, event_data)
    FROM sys.fn_xe_file_target_read_file(
   'E:\DBA_Audit\SP_Exec*.xel', 
   'E:\DBA_Audit\SP_Exec*.xem', 
   NULL, NULL
 )
),
tab AS
(
 SELECT 
  [host] = data.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(400)'),
  app_name = data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(400)'),
  username = data.value('(event/action[@name="username"]/value)[1]','nvarchar(400)'),
  [object_name] = data.value('(event/data[@name="object_name"]/value)[1]','nvarchar(250)'),
  [timestamp] = data.value('(event/@timestamp)[1]','datetime2')
 FROM ee_data
)
SELECT DISTINCT [host], app_name, username, MAX([timestamp]) as last_executed, 
  COUNT([object_name]) as number_of_executions, [object_name]
  FROM tab 
  GROUP BY [host], app_name, username, [object_name] ;
--https://www.mssqltips.com/sqlservertip/3259/several-methods-to-collect-sql-server-stored-procedure-execution-history/ By Svetlana Golovko