开发者

SQL most executed query?

开发者 https://www.devze.com 2022-12-30 20:28 出处:网络
I have a database in SQL Server 2008, and there are a lot of machines making queries against it. I know there is a SQL S开发者_如何学Cerver profiler, but I don\'t know very well how to use it.

I have a database in SQL Server 2008, and there are a lot of machines making queries against it. I know there is a SQL S开发者_如何学Cerver profiler, but I don't know very well how to use it.

Is there any way to know what are the most common queries executed in the database? Through the profiler or not, it doesn't matter.

Thank you very much in advance!


     SELECT *
       FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b 
   ORDER BY execution_count DESC

This will tell you the number of times a query has been executed and the longest query to execute.

Not sure whether this will be handy or not, but this SQL will give you the slowest 100 queries:

    SELECT TOP 100
            [Object_Name] = object_name(st.objectid),
            creation_time, 
            last_execution_time, 
            total_cpu_time = total_worker_time / 1000, 
            avg_cpu_time = (total_worker_time / execution_count) / 1000,
            min_cpu_time = min_worker_time / 1000,
            max_cpu_time = max_worker_time / 1000,
            last_cpu_time = last_worker_time / 1000,
            total_time_elapsed = total_elapsed_time / 1000 , 
            avg_time_elapsed = (total_elapsed_time / execution_count) / 1000, 
            min_time_elapsed = min_elapsed_time / 1000, 
            max_time_elapsed = max_elapsed_time / 1000, 
            avg_physical_reads = total_physical_reads / execution_count,
            avg_logical_reads = total_logical_reads / execution_count,
            execution_count, 
            SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                  (
                        (
                              CASE statement_end_offset
                                    WHEN -1 THEN DATALENGTH(st.text)
                                    ELSE qs.statement_end_offset
                              END 
                              - qs.statement_start_offset
                        ) /2
                  ) + 1
            ) as statement_text
       FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st 
   ORDER BY total_worker_time / execution_count DESC
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号