How can I query for the heaviest queries in SQL Server?

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: link

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

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

There are tools that can help you, such as NaviCat that have a monitoring feature. Or Monyog . If you want to do it in the hand there are these commands:

EXECUTE sp_who2

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

DBCC INPUTBUFFER(spid)