How can I refer to the heaviest queries in SQL Server?

Question

In the SQL Server database, how is it possible to know which queries have consumed the most resources of the database in its execution?

Answer 1

Use this query:

SELECT TOP 10
total_worker_time/execution_count AS Avg_CPU_Time
    ,execution_count
    ,total_elapsed_time/execution_count as AVG_Run_Time
    ,(SELECT
          SUBSTRING(text,statement_start_offset/2,(CASE
                                                       WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 
                                                       ELSE statement_end_offset 
                                                   END -statement_start_offset)/2
                   ) FROM sys.dm_exec_sql_text(sql_handle)
     ) AS query_text 
FROM sys.dm_exec_query_stats 

--pick your criteria

ORDER BY Avg_CPU_Time DESC
--ORDER BY AVG_Run_Time DESC
--ORDER BY execution_count DESC

Source: https://stackoverflow.com/questions/2499910/how-to-find-the-worst-performing-queries-in-sql-server-2008

In the same article there is a link to this article, which also gives several tips for finding performance problems.

One more detail: if you want to analyze the performance of a T-SQL or Procedure chunk, run the code in Management Studio with the option Include Actual Execution Plan. You will see a result in graphs showing the percentage of the weight of the queries executed and the cost of each access to data for each part of the query.

Answer 2

There are tools that can help you like Navicat which has a monitoring feature. Or the Monyog. If you want to do in hand there are these commands:

EXECUTE sp_who2

And with this command you can exactly monitor a specific function/query:

DBCC INPUTBUFFER(spid)