记SQL Server2014数据库查询优化

公司某外购业务系统，随着业务数据的逐渐增长，对服务器配置要求越来越高，部分查询视图初次加载仍缓慢异常。接到任务对其进行调优，出于客观原因只能对数据库进行查询优化。之前多用Mysql  ，赶紧对SQL server进行了一波了解。这里记录部分过程。

主要参考文档 

https://docs.microsoft.com/zh-cn/previous-versions/sql/2014/?view=sql-server-2014

主要工具  

SQL Server Profiler 、数据库引擎优化顾问

https://docs.microsoft.com/zh-cn/previous-versions/sql/2014/tools/sql-server-profiler/sql-server-profiler?view=sql-server-2014

https://docs.microsoft.com/zh-cn/previous-versions/sql/2014/relational-databases/performance/database-engine-tuning-advisor?view=sql-server-2014

 

优化目标  1、通过慢查询日志检索出来的

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) as 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%'
-- and creation_time between '2012-05-07 01:35:00.000' and '2020-05-07 01:35:00.000'
and creation_time > '2021-06-01' and (total_elapsed_time / execution_count/1000) > 3000
ORDER BY 
-- last_execution_time DESC
 total_elapsed_time / execution_count DESC;

2、通过SQL Server Profiler 跟踪系统中查询较慢的视图，有针对性的进行优化

 

备注：数据库引擎优化顾问，给出的优化建议，无需全盘执行，需要分析后有针对性的应用，否则可能会适得其反。本次优化主要通过添加索引的方式。

举例：

1、count(*)  需要非聚合索引，仅聚合索引无效；

2、多条件查询 建立联合索引

3、单表索引不宜太多

 

 

关于缓存清理 

在进行性能分析的时候有时候需要清除缓存以便进行下一次分析。

SQL Server提供了一些工具来清除缓存的性能数据。使用下面的语句来完成这些任务。

清除全局缓存使用下面的语句：
DBCC DROPCLEANBUFFERS;

从全局缓存中清除执行计划，使用下面的语句：
DBCC FREEPROCCACHE;