Get username and/or IP address responsible for a query

I would say your best bet is either extended events or a trace. Unfortunately I don't know enough about extended events to give you any advice there but I can give you some about setting up a trace.

First of all a trace can pull, among other things an hostname, which is the server/workstation the query came from. Include it since sometimes the username doesn't really help and you can frequently track down who is doing something by what machine they are running from.

Second, There is a filter for "textdata" and you should be able to pull only those queries that start with the first however many characters of the query you are looking for. However you may find that to be a bit slow, and I'm not sure if carriage returns/line feeds or other formatting may throw it off. You can do a trace where you pull only those queries with x duration or higher, x reads or higher, x writes or higher etc. By using the information from your query above you should be able to keep the number of "false positive" responses fairly low. And using those types of restrictions should make your trace fairly low overhead.

EDIT: Here is an example of a script to create a trace you could use. I've commented everything I feel you need. Scripting to a file uses a great deal less overhead than running the GUI. You just need to make a point of processing the files created periodically and removing them. And once you are done with the trace make sure you shut it down.

I'll include a script under the trace create script to help you shut it down once you are done with it.

-- Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint set @maxfilesize = 5 -- Note that this will start a new trace file each time the old ones get to 5mb. -- This way you can process and remove the older files. Be careful to shut down your -- trace when you are done as a runaway trace can use up all the space on your drive. exec @rc = sp_trace_create @TraceID output, 0, N'C:\TraceFiles\QueryTrace', @maxfilesize, NULL if (@rc != 0) goto error -- Set the events declare @on bit set @on = 1 -- Here are the events the trace will look for along with what data -- will be pulled for each event. I've labeled the events by group -- and for the first event I've labeled each of the data points. -- Look up sp_trace_setevent in BOL for further descriptions. -- Remote procedure call completed exec sp_trace_setevent @TraceID, 10, 15, @on -- EndTime exec sp_trace_setevent @TraceID, 10, 8, @on -- HostName exec sp_trace_setevent @TraceID, 10, 16, @on -- Reads exec sp_trace_setevent @TraceID, 10, 1, @on -- TextData exec sp_trace_setevent @TraceID, 10, 9, @on -- ClientProcessId exec sp_trace_setevent @TraceID, 10, 17, @on -- Writes exec sp_trace_setevent @TraceID, 10, 10, @on -- ApplicationName exec sp_trace_setevent @TraceID, 10, 18, @on -- CPU exec sp_trace_setevent @TraceID, 10, 34, @on -- ObjectName exec sp_trace_setevent @TraceID, 10, 3, @on -- DatabaseId exec sp_trace_setevent @TraceID, 10, 11, @on -- LoginName exec sp_trace_setevent @TraceID, 10, 12, @on -- SPID exec sp_trace_setevent @TraceID, 10, 13, @on -- Duration exec sp_trace_setevent @TraceID, 10, 6, @on -- NTUserName exec sp_trace_setevent @TraceID, 10, 14, @on -- StartTime -- SQL Batch completed exec sp_trace_setevent @TraceID, 12, 15, @on exec sp_trace_setevent @TraceID, 12, 8, @on exec sp_trace_setevent @TraceID, 12, 16, @on exec sp_trace_setevent @TraceID, 12, 1, @on exec sp_trace_setevent @TraceID, 12, 9, @on exec sp_trace_setevent @TraceID, 12, 17, @on exec sp_trace_setevent @TraceID, 12, 6, @on exec sp_trace_setevent @TraceID, 12, 10, @on exec sp_trace_setevent @TraceID, 12, 14, @on exec sp_trace_setevent @TraceID, 12, 18, @on exec sp_trace_setevent @TraceID, 12, 3, @on exec sp_trace_setevent @TraceID, 12, 11, @on exec sp_trace_setevent @TraceID, 12, 12, @on exec sp_trace_setevent @TraceID, 12, 13, @on -- SQL Statement completed exec sp_trace_setevent @TraceID, 41, 15, @on exec sp_trace_setevent @TraceID, 41, 8, @on exec sp_trace_setevent @TraceID, 41, 16, @on exec sp_trace_setevent @TraceID, 41, 1, @on exec sp_trace_setevent @TraceID, 41, 9, @on exec sp_trace_setevent @TraceID, 41, 17, @on exec sp_trace_setevent @TraceID, 41, 6, @on exec sp_trace_setevent @TraceID, 41, 10, @on exec sp_trace_setevent @TraceID, 41, 14, @on exec sp_trace_setevent @TraceID, 41, 18, @on exec sp_trace_setevent @TraceID, 41, 3, @on exec sp_trace_setevent @TraceID, 41, 11, @on exec sp_trace_setevent @TraceID, 41, 12, @on exec sp_trace_setevent @TraceID, 41, 13, @on -- Set the Filters declare @intfilter int declare @bigintfilter bigint -- Here are the filters. The more specific you can be the less overhead. -- In your case you are working with a "problem" query so at least one of -- the following should be an issue: duration, reads, writes. With any -- luck you don't have a lot of high resource queries on your instance -- so these filters won't pull a tremendous amount more than what you -- are looking for. -- Obviously you will need to modify the filter values to fit your purpose. -- Filter by database id set @intfilter = 17 exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter -- Filter by duration. Greater than or equal to. set @bigintfilter = 10000000 exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter -- Filter by reads. Greater than or equal to. set @bigintfilter = 1000 exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter -- Filter by writes. Greater than or equal to. set @bigintfilter = 1000 exec sp_trace_setfilter @TraceID, 17, 0, 4, @bigintfilter -- This is generated by the GUI to filter out the profiler. -- I'm striping off the GUID on the end as that would be -- specific to my machine (I believe). -- exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 99514c1d-7404-4448-8831-1225125c45aa' exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler' -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 -- display trace id for future references select TraceID=@TraceID goto finish error: select ErrorCode=@rc finish: go 

Script to shut down the trace when you are done.

-- This query pulls a list of all traces. -- You will be able to tell which one is yours -- by the filename you selected when you -- created it. SELECT * FROM sys.fn_trace_getinfo(NULL) -- Get the trace id from the query above and use it instead of @TraceID. exec sp_trace_setstatus @TraceID, 0 exec sp_trace_setstatus @TraceID, 2