July 24, 2012 at 7:45 am
Hi All,
By using the below query i can able to find out which queries are run during that particular time .. but i want know who runs that query [ mean from which application or host name that particular query was run ] . can any one suggest me how to join this script with sysprocesses DMV.
SELECT top 100
SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset ) / 2 ) + 1)
AS statement_text ,
execution_count ,
total_worker_time / 1000 AS total_worker_time_ms ,
( total_worker_time / 1000 ) / execution_count
AS avg_worker_time_ms ,
total_logical_reads ,
total_logical_reads / execution_count AS avg_logical_reads ,
total_elapsed_time / 1000 AS total_elapsed_time_ms ,
( total_elapsed_time / 1000 ) / execution_count
AS avg_elapsed_time_ms ,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
where creation_time between ('2012-06-01 00:00:00.000') and ('2012-07-24 23:59:00.000') order by creation_time
Thanks
Lavanya
July 24, 2012 at 7:50 am
join sys.dm_exec_query_stats onto sys.dm_exec_requests on either the query_hash or query_plan_hash columns
then join sys.dm_exec_requests onto sys.dm_exec_sessions on the session_id columns and username hostname application are in that one DMV
July 24, 2012 at 8:02 am
If the query is still running you can join to sys.dm_exec_requests and sys.dm_exec_sessions as Anthony suggests (thought you won't get accurate results). If the query has finished then there is no way to tell.
Sys.dm_exec_query_stats is about aggregated statistics of a lot of query executions and does not record who ran the query from where. If you want that you need to either poll sys.dm_exec_requests or use SQLTrace.
btw, that DMV is not an accurate way to tell what queries ran during a particular time period. If you are OK with missing some queries and getting incorrect stats on others then use it. Otherwise you need SQLTrace
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2012 at 8:06 am
Thanks for the update. I am trying to do that but i am getting errors . I don't have in depth knowledge on coding style . If it possible can u provide a updated query . its great helpful to me.I will try from my side ..
Thanks
Lavanya
July 24, 2012 at 8:16 am
What are you trying to find out? Who ran a query 2 weeks ago? All the people who have run a particular query in the last month and a half?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2012 at 8:28 am
Gila @: we had problem last week . Some one updated the table with out where class. So the particular column is updated with same data in all the rows.By using the query[ i provided earlier ] i can able to find out that at at that time the query was ran, but i am unable to find from where it happens ?i want know from which host name the query was ran ?
July 24, 2012 at 8:31 am
Without some custom auditing or a trace that was running at the time you wont be able to find that information.
There is no historical table which logs who ran what query, where from, from what application.
Even expensive log readers will not be able to tell you the user who performed the update.
The best you will be able to do is to restore the database to a time prior to the mass update as a different database and update the values back.
July 24, 2012 at 8:41 am
Could you please confirm , the query which one i updated earlier is the correct one or not ??
By using the above query i found the query like
update table name where columname='text' and logcreate time and execution time also.
July 24, 2012 at 8:47 am
You are trying to find out who ran that query a number of days ago. You cannot do this, there is no history table.
Sys.dm_exec_query_stats is just what it says, its an aggregated collection of statistics gathered over a period of time for specific queries.
July 24, 2012 at 8:48 am
There are no DMVs, no logs that will show you who ran a query or where it as run from.
The query that you posted earlier is correct if what you are trying to do is get historical performance data for queries. It cannot however tell you anything about who ran the query or where it was run from.
Unless you had some auditing in place at the time that the update was run, or had a trace running, you will not be able to find out who ran that query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply