Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I use the following query to find performance improvements in queries:

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1),
  qs.execution_count,
  qs.total_logical_reads, qs.last_logical_reads, qs.min_logical_reads,
  qs.max_logical_reads, qs.total_physical_reads, qs.last_physical_reads,
  qs.min_physical_reads, qs.max_physical_reads,
  qs.total_elapsed_time / 1000000 As total_elapsed_time, 
  qs.last_elapsed_time / 1000000 As last_elapsed_time,
  qs.min_elapsed_time / 1000000 As min_elapsed_time, 
  qs.max_elapsed_time / 1000000 As max_elapsed_time,
  qs.last_execution_time, qs.creation_time, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
AND last_execution_time >= DATEADD (MINUTE , -5 , CURRENT_TIMESTAMP )
ORDER BY qs.total_logical_reads DESC
--ORDER BY qs.total_physical_reads DESC

The issue is that I've found a query that appears to be thrown repeatedly from time to time and requires enhancement, but I'm not able to determine where it comes from (could be a user or a program, but in any case should have a login or username). How can I get this user or track this query so I can apply improvements to it?

share|improve this question
    
If you have the query text, you can use SQL Trace (or XE depending on what version of SQL Server you're using). You can get the application name and the user that issued the T-SQL. As for sys.dm_exec_query_stats, that is aggregated information and doesn't track what sessions or any other connection information generated the data. What you can do is watch sys.dm_exec_requests and correlate that with sys.dm_exec_connections to get the data you're looking for. –  Thomas Stringer Jul 9 '13 at 15:11

2 Answers 2

up vote 3 down vote accepted

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
share|improve this answer
    
Hello Kenneth. When you say a trace you mean running the SQL Profiler, don't you? If that's the case I would need more information about how to capture this concrete query. I've played with it sometimes just for fun, but really didn't need it on a real situation. Thanks!! –  Hauri Jul 10 '13 at 7:38
    
SQL Profiler is the GUI for traces yes. From what you said the query you are trying to capture is a "problem" query. If so it has a high duration, high reads, high writes etc. Your query above will give you the minimum reads & duration for your query, and you can add in writes fairly easily. Create your trace using that information (along with the specific database you are using) as your filters. That will capture more than just the query you are interested in but hopefully not a great deal more. I'll try to script an example out and add it to my answer. –  Kenneth Fisher Jul 10 '13 at 12:44
    
Hello Kenneth, your script really works great. I'm still playing around with it but the results seen reveal interesting queries to improve. Thanks !! –  Hauri Jul 11 '13 at 7:54

You can use the below query to pull currently executing requests and the corresponding session/connection information:

select
    r.session_id,
    s.login_name,
    c.client_net_address,
    s.host_name,
    s.program_name,
    st.text
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s
on r.session_id = s.session_id
left join sys.dm_exec_connections c
on r.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
--where st.text like '%your query string to search for%';

What you are querying in your question, sys.dm_exec_query_stats, is cumulative and aggregated statistics that aren't by session/connection. In other words, that information isn't stored there, nor could it easily be retrieved.

You could put in place a SQL Trace or XE session to grab this data that you're looking for, basically monitoring for the occurrence of your offending query(ies).

There are a couple of ways to get the data you're looking for, but unfortunately with your given query there isn't an easy way to mold that into what you're looking for. Simply different types of data (you provide aggregated data, but you're looking for snapshot and granular data).

share|improve this answer
    
Is it possible to get more information when running this query? For instance, the sql text of the query for each request... ¿? –  Hauri Jul 10 '13 at 7:39
    
Sorry, didn't see the last line, the one with comments. I have to say that this is quite useful. I've seen some interesting things with this. I will try the SQL Profiler anyway, it may give even more information. Thanks! –  Hauri Jul 10 '13 at 7:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.