Top 10 most costly queries by user?

Last post 01-08-2008 11:10 PM by PhatboySlim. 0 replies.

Sort Posts:

  • Top 10 most costly queries by user?

    01-08-2008, 11:10 PM
    • Loading...
    • PhatboySlim
    • Joined on 05-19-2006, 10:07 AM
    • Chicago
    • Posts 20

    The websites I write use ad-hoc queries instead of stored procedures.  So when I run the following query:

    SELECT TOP 10
            [Average CPU used] = total_worker_time / qs.execution_count
            ,[Total CPU used] = total_worker_time
            ,[Execution count] = qs.execution_count
            ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
             (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)
            ,[Parent Query] = qt.text
            ,DatabaseName = DB_NAME(qt.dbid)
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    ORDER BY [Average CPU used] DESC;

    I am getting null for dbid, thus not allowing me to truly see which location my high cpu usage queries are running on.  Is it possible to output the database user that executed the query? If I can see which user was connected when running the ad-hoc query I can tell which database it was run on.

    Thanks guys.
     

    There is no such thing as an unimportant day.
    Filed under: , ,
Page 1 of 1 (1 items)
Microsoft Communities
Page view counter