How to find SQL Server queries that took a lot of time?

Question

I have application that is up more than 3 days. I can see in logs that there was a moment when application executed some SQL query and this took a lot of time, probably because of some db locks.

I heard that there is a query for such situations. So I need to be able to ask all queries that took, for example, more than 30 minutes. Is it possible?

Answer 1

use SQL Server Profiler.

Answer 2

You can use the SQL Server Profiler (a separate tool which you can choose to install together with SQL Server itself).

It has a wizard you can use to set up a trace for long-running queries.

Answer 3

give this a try:

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 
ORDER BY AVG_Run_Time DESC

Answer 4

You can achieve it by querying Dynamic Management Views. SQL Server 2005 Performance Dashboard Reports will also reveal (actually they are based on dmv) lots of useful performance related information.

Keep in mind that when you restart sql server the above information is lost.

In sql server 2008 you can use Data Collector

Answer 5

In SQL Server Management Studio, connect to your server. Then, right-click the server name in the Object Explorer, and click "Activity Monitor". "Recent Expensive Queries" will tell you much of what you'd like to know. Try double-clicking on one of the queries while you're there.