SQL Server查询优化方案

3/6/2024 11:23:15 AM

365

一、获取慢速查询

a)、使用sql获取

对于当前正在执行的语句，检查 sys.dm_exec_requests 中的total_elapsed_time列和cpu_time列。运行以下查询以获取数据：

SELECT 
    req.session_id
    , req.total_elapsed_time AS duration_ms
    , req.cpu_time AS cpu_time_ms
    , req.total_elapsed_time - req.cpu_time AS wait_time
    , req.logical_reads
    , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
       ((CASE statement_end_offset
           WHEN -1
           THEN DATALENGTH(ST.text)  
           ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 
      1, 512)  AS statement_text  
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;

对于过去执行的查询，检查sys.dm_exec_query_stats中的last_elapsed_time列和last_worker_time列。运行以下查询以获取数据：

SELECT t.text,
     (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
     (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
     ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time,
     qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
     qs.total_logical_writes / qs.execution_count AS avg_writes,
     (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions
FROM sys.dm_exec_query_stats qs
     CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE t.text like '<Your Query>%'
-- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped.
ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC

如果 avg_wait_time 显示负值，则为并行查询。

如果可以在 SQL Server Management Studio (SSMS) 或 Azure Data Studio 中按需执行查询，请使用 SET STATISTICS TIMEON 和 SET STATISTICS IOON 运行它。

SET STATISTICS TIME ON
SET STATISTICS IO ON
<YourQuery>
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

然后，从 “消息”中，你将看到 CPU 时间、运行时间和逻辑读取，如下所示：

Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 460 ms,  elapsed time = 470 ms.

其他

select id,`user`,`host`,DB,command,`time`,state,info from information_schema.PROCESSLIST where TIME>=10;

b)、使用 SQL Profiler

使用 SQL Profiler：SQL Server Profiler 是 SQL Server 提供的专业工具，可以用来监视数据库服务器上发生的各种事件，包括 SQL 语句的执行情况。你可以设置 Profiler 来捕获执行时间超过一定阈值的 SQL 语句，然后分析这些 SQL 语句的执行计划和性能指标。
使用 Query Store：SQL Server 2016 及更新版本提供了 Query Store 功能，可以用来跟踪执行计划的变化和查询性能。通过 Query Store，你可以找到执行缓慢的 SQL 查询，并且比较不同时间点的性能情况。

c)、使用数据库报表

在数据库服务器上右键报表->标准报表->按平局CPU时间/IO次数/总CPU时间/IO总次数排在前面的查询

d)、使用 Extended Events

SQL Server 2008 及以上版本引入了 Extended Events，这是一个更加灵活和高效的事件处理系统，可以用来捕获执行缓慢的 SQL 语句。你可以设置 Extended Events 来捕获 SQL 执行的时间、逻辑读取次数等指标，并根据这些指标来识别执行缓慢的 SQL 语句。

二、优化工具

a)、报表工具

在数据库服务器上右键选择“报表” ，或者在数据库上右键选择“报表”，可以看到又很多统计工具可以使用，进而分析CPU,IO,内存,索引等的使用情况

b)、查询计划

使用获取到的sql放入SSMS查询窗口，不要修改代码任何部分，显示分析执行计划

c)、监控性能计数器

利用 SQL Server 提供的性能计数器，可以监控数据库服务器的性能指标，如 CPU 使用率、内存利用率、磁盘 I/O 等。通过监控这些性能指标，你可以找到是否有硬件资源瓶颈导致 SQL 查询执行缓慢

三、语句优化

使用查询优化器提示
在某些情况下，SQL 查询可能由于查询优化器选择了不恰当的执行计划而导致执行缓慢。你可以尝试使用查询提示（Query Hints）来指导查询优化器选择更合适的执行计划，例如使用 OPTION (RECOMPILE)、OPTION (FORCE ORDER) 等提示
创建适当的索引。例如left join 查询，在右表关联条件的字段上创建索引
使用子查询。在关联表查询的语句中，使用子查询查出少量的数据然后在进行关联表查询
去掉非必要的select字段
避免过多的join动作
减少查询的记录数
减少函数的使用
使用参数化查询。可复用查询计划
数据库统计信息更新：定期更新数据库的统计信息，以确保查询优化器能够生成最佳的执行计划。
在合适的数据量下使用EXISTS和IN
大量数据插入时使用批处理语句
合理的字段类型
合理的索引数量
避免null值
绝对避免使用时间字符串拼接sql

四、索引失效

四、查看语句执行逻辑

1 DBCC DROPCLEANBUFFERS  --清除缓冲区
2 DBCC FREEPROCCACHE  --删除计划高速缓存中的元素

开启查询IO读取统计、查询时间统计。

SET STATISTICS TIME ON --执行时间
SET STATISTICS IO ON --IO读取

执行sql后在“消息”窗口查看

扩展阅读：
SQL Server 执行计划
 SQL执行缓慢的排查过程
 一条SQL更新语句是如何执行的?
SQL Server 部分包含数据库及从SSMS链接

本文链接:https://blog.nnwk.net/article/1534