none
i need to modify this query to pull out a user ID

    Question

  • SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT TOP 100
    CAST((qs.total_elapsed_time / 1000000.0) AS DECIMAL(28,2)) as total_elapsed_time,
    CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2))
    AS [Total CPU time (s)]
    , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
    AS DECIMAL(28,2)) AS [% CPU]
    , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
    qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting], qs.execution_count
    , CAST((qs.total_worker_time) / 1000000.0
    / qs.execution_count AS DECIMAL(28, 2)) AS [CPU time average (s)]
    , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
    ((CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
    , qt.text AS [Parent Query]
    , DB_NAME(qt.dbid) AS DatabaseName
    , qp.query_plan
    --,qs.last_worker_time
    , qs.last_execution_time
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE qs.total_elapsed_time > 0
    ORDER BY [CPU time average (s)] DESC

    k

    Thursday, October 17, 2013 8:20 PM

Answers

  • As already stated you are better off finding this information through traces.  You cannot tell who is running what query through the above code as SQL Server does not keep track of that.  

    If you set up monitoring you can tell when a query is run and by whom if you use traces but only if they are running when a query is run.  They cannot tell you after the fact.  

    That is why I suggested looking into SQLSentry or looking through the articles I listed.  

    Edit:  Be careful when instituting traces against production data as they can impact performance.  

    Although no one is able to it is best to not allow ad hoc queries be run against production data anyway.  Ad hoc queries generally should be run in a development environment.  

    • Edited by zcurtin608 Thursday, October 17, 2013 9:00 PM
    • Marked as answer by '''HuuM''' Tuesday, October 22, 2013 1:52 PM
    Thursday, October 17, 2013 8:58 PM

All replies