Geek Answers Handbook

How to find SQL Server queries that took a long time?

I have an application for more than 3 days. In the logs, I see that there was a moment when the application made some SQL query, and it took a long time, probably due to some db locks.

I heard that there is a request for such situations. Therefore, I need to ask all the requests that took, for example, more than 30 minutes. Is it possible?

+5
source share
5 answers

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
+7
source

use SQL Server Profiler .

+1
source

SQL Server ( , SQL Server).

, .

0

, . Dashboard Performance SQL Server 2005 ( dmv) , .

, sql .

SQL Server 2008

0

SQL Server Management Studio . " ". " " , . , .

0
source

More articles:


All Articles