Most expensive sql queries with user
Question
Friday, August 10, 2018 2:12 PM
i am trying to get the list of most expensive queries with the users that is coming form
kindly assist , i need to know the users names as well where the query is coming form.
k
All replies (8)
Monday, August 13, 2018 2:19 AM âś…Answered
Hi '''HuuM''',
As mentioned by Erland, we will need to use trace to know who run these queries. We can use SQL Server Profiler to trace it and save the result to table.
Then we can get a list of query text and spid, after that, we can join these tables based on the query test.
Best Regards,
Teige
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Friday, August 10, 2018 2:51 PM
This will help:
https://dba.stackexchange.com/questions/127252/users-running-expensive-queries
Friday, August 10, 2018 2:59 PM | 1 vote
At the basic level, Activity Monitor in SSMS has a tab "Recent Expensive Queries" that shows the list of queries. However, it doesn't have a "LoginName" tab to show where the queries are coming from. You have to tie the queries to the SPID to know the login name.
Also, what's your definition of "Expensive"? are you looking for the queries that have high CPU utilization? I/O? logical reads etc?
Anyway, the following links should give you some idea:
https://www.sqlservercentral.com/Forums/Topic1564298-3387-1.aspx
https://www.sqlpassion.at/archive/2015/04/20/how-to-find-your-worst-performing-sql-server-queries/
Alternatively, if you're comfortable using Extended Events, you can go with the events:
sqlserver.sql_statement_completed and sqlserver.rpc_completed and filter on reads, CPU time, writes etc.
Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.
Friday, August 10, 2018 5:28 PM
how can i tie the queries to the spid?
k
Friday, August 10, 2018 5:45 PM | 1 vote
select hostname, loginame, program_name from sys.sysprocesses
where spid=1
Replace 1 with the spid you are interested in.
Friday, August 10, 2018 7:42 PM
How can i utalize this
select hostname, loginame, program_name from sys.sysprocesses
where spid=1
with query below
SELECT TOP 10 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.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_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
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time
k
Friday, August 10, 2018 9:34 PM
From the plan cache you can tell which the expensive queries are, but you cannot say who ran them. For that end, you need to use Trace or Extended Events.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Monday, August 13, 2018 2:58 AM | 1 vote
A word of warning about Profiler - an unfiltered trace can bog down an active production server, sometimes to the point of crashing. It would be best to use Extended Events or a server-side trace that writes to a file (which can be scripted from profiler). The file can then be imported into a table from analysis.
Dan Guzman, Data Platform MVP, http://www.dbdelta.com