性能优化查询语句
Sys.dm_exec_sql_text 来获得特定查询的文本 他可以 与
Sys.dm_exec_query_stats 动态管理视图结合使用，已获得所有数据库中性能最差的查询
获得查询执行次数，平均的CPU 时间和持续时间以及查询的文本
SELECT
(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,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) N'执行语句'
,creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
FROM
sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
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) not like '%fetch%'
ORDER BY
total_elapsed_time / execution_count DESC;
SQLServer
SELECT top 50 creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,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) N'执行语句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where 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) not like '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;
oracle
select *
from
(select
LAST_ACTIVE_TIME AS "上次执行时间",
DISK_READS AS "读磁盘次数",
SQL_TEXT AS "执行语句",
PARSE_CALLS AS "编译次数",
EXECUTIONS AS "执行次数",
CPU_TIME/1000 AS "所用的CPU总时间ms",
ELAPSED_TIME/1000 AS "总花费时间ms",
ROUND(ELAPSED_TIME/EXECUTIONS/1000, 2) AS "平均时间ms",
USER_IO_WAIT_TIME
from sys.v_$sqlarea
where EXECUTIONS > 0
and MODULE = 'SFCMN03003S.exe'
order by ELAPSED_TIME/EXECUTIONS desc)
where rownum < 51;
sys.dm_exec_query_stats (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
返回缓存查询计划的聚合性能统计信息。缓存计划中的每个查询语句在该视图中对应一行，并且行的生存期与计划本身相关联。在从缓存删除计划时，也将从该视图中删除对应行。
|
注意： | ||
|
如果服务器中当前正在执行工作负荷，则 sys.dm_exec_query_stats 的初始查询可能会生成不准确的结果。可以通过重新运行查询来确定更准确的结果。 | ||
|
列名 |
数据类型 |
说明 |
|
sql_handle |
varbinary(64) |
表示包含查询的批查询或存储过程的标记。 通过调用 sys.dm_exec_sql_text 动态管理函数，sql_handle 可以和 statement_start_offset 及 statement_end_offset 一起用于检索查询的 SQL 文本。 |
|
statement_start_offset |
int |
指示行所说明的查询在其批查询或持久化对象文本中的开始位置（字节，以 0 开头）。 |
|
statement_end_offset |
int |
指示行所说明的查询在其批查询或持久化对象文本中的结束位置（字节，以 0 开头）。值 -1 指示批查询的结尾处。 |
|
plan_generation_num |
bigint |
可用于在重新编译后区分不同计划实例的序列号。 |
|
plan_handle |
varbinary(64) |
表示包含查询的编译计划的标记。可以将此值传递给 sys.dm_exec_query_plan 动态管理函数来获取查询计划。 |
|
creation_time |
datetime |
编译计划的时间。 |
|
last_execution_time |
datetime |
上次开始执行计划的时间。 |
|
execution_count |
bigint |
计划自上次编译以来所执行的次数。 |
|
total_worker_time |
bigint |
此计划自编译以来执行所用的 CPU 时间总量（以微秒为单位报告，但仅精确到毫秒）。 |
|
last_worker_time |
bigint |
上次执行计划所用的 CPU 时间（以微秒为单位报告，但仅精确到毫秒）。 |
|
min_worker_time |
bigint |
此计划在单次执行期间所用的最小 CPU 时间（以微秒为单位报告，但仅精确到毫秒）。 |
|
max_worker_time |
bigint |
此计划在单次执行期间所用的最大 CPU 时间（以微秒为单位报告，但仅精确到毫秒）。 |
|
total_physical_reads |
bigint |
此计划自编译后在执行期间所执行的物理读取总次数。 |
|
last_physical_reads |
bigint |
上次执行计划时所执行的物理读取次数。 |
|
min_physical_reads |
bigint |
该计划在单个执行期间所执行的最少物理读取次数。 |
|
max_physical_reads |
bigint |
该计划在单个执行期间所执行的最多物理读取次数。 |
|
total_logical_writes |
bigint |
此计划自编译后在执行期间所执行的逻辑写入总次数。 |
|
last_logical_writes |
bigint |
上次执行计划时所执行的逻辑写入次数。 |
|
min_logical_writes |
bigint |
该计划在单个执行期间所执行的最少逻辑写入次数。 |
|
max_logical_writes |
bigint |
该计划在单个执行期间所执行的最多逻辑写入次数。 |
|
total_logical_reads |
bigint |
此计划自编译后在执行期间所执行的逻辑读取总次数。 |
|
last_logical_reads |
bigint |
上次执行计划时所执行的逻辑读取次数。 |
|
min_logical_reads |
bigint |
该计划在单个执行期间所执行的最少逻辑读取次数。 |
|
max_logical_reads |
bigint |
该计划在单个执行期间所执行的最多逻辑读取次数。 |
|
total_clr_time |
bigint |
编译计划后，因执行此计划而在 Microsoft .NET Framework 公共语言运行时 (CLR) 对象内使用的时间（以微秒为单位报告，但仅精确到毫秒）。CLR 对象可以是存储过程、函数、触发器、类型和聚合。 |
|
last_clr_time |
bigint |
在上一次执行此计划期间，在 .NET Framework 公共语言运行时 (CLR) 对象内执行所用的时间（以微秒为单位报告，但仅精确到毫秒）。CLR 对象可以是存储过程、函数、触发器、类型和聚合。 |
|
min_clr_time |
bigint |
此计划在单次执行期间在 .NET Framework CLR 对象内所用的最小时间（以微秒为单位报告，但仅精确到毫秒）。CLR 对象可以是存储过程、函数、触发器、类型和聚合。 |
|
max_clr_time |
bigint |
此计划在单次执行期间在 .NET Framework CLR 内所用的最大时间（以微秒为单位报告，但仅精确到毫秒）。CLR 对象可以是存储过程、函数、触发器、类型和聚合。 |
|
total_elapsed_time |
bigint |
上次完成执行此计划所用的总时间（以微秒为单位报告，但仅精确到毫秒）。 |
|
last_elapsed_time |
bigint |
最近一次完成执行此计划所用的时间（以微秒为单位报告，但仅精确到毫秒）。 |
|
min_elapsed_time |
bigint |
任何一次完成执行此计划所用的最小时间（以微秒为单位报告，但仅精确到毫秒）。 |
|
max_elapsed_time |
bigint |
任何一次完成执行此计划所用的最大时间（以微秒为单位报告，但仅精确到毫秒）。 |
|
query_hash |
Binary(8) |
对查询计算的二进制哈希值，用于标识具有类似逻辑的查询。可以使用查询哈希确定仅仅是文字值不同的查询的聚合资源使用情况。有关详细信息，请参阅 使用查询和查询计划哈希值查找和优化类似查询。 |
|
query_plan_hash |
binary(8) |
对查询执行计划计算的二进制哈希值，用于标识类似的查询执行计划。可以使用查询计划哈希查找具有类似执行计划的查询的累积成本。有关详细信息，请参阅 使用查询和查询计划哈希值查找和优化类似查询。 |
示例
A. 查找 TOP N 查询
以下示例按平均 CPU 时间返回排名前五个的查询的相关信息。此示例将根据查询的查询哈希对查询进行聚合，以便按照查询的累积资源消耗来分组在逻辑上等效的查询。
USE AdventureWorks2008R2;
GO
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM
(SELECT QS.*,
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 AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
GO
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23490154/viewspace-668522/ ，如需转载，请注明出处，否则将追究法律责任。
