How can I query What are 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: 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 snippet, run the code in Management Studio with the Include Actual Execution Plan option. You will see a result in graphs showing the percentage of the weight of the queries executed and the cost of each data access of each part of the query.