前言：

在日常工作中，SQL Server的管理和维护是一个不可或缺的部分，但随之而来的各种问题也不可避免。面对这些问题时，有效地利用SQL Server自带的功能以及借助第三方工具来进行深入的问题诊断分析变得尤为重要。这样做不仅能够帮助我们快速定位问题所在，还能为制定有效的解决方案提供坚实的基础。

诊断思路：

首先，SQL Server本身提供了多种强大的内置功能来支持故障排查和性能优化工作。例如，使用动态管理视图（DMVs）可以让我们深入了解当前数据库引擎的状态；通过查询存储功能则能够对历史执行计划进行回顾分析，进而识别出可能导致性能瓶颈的查询语句；此外，还有如扩展事件、SQL Profiler等工具，它们对于追踪特定操作或事件非常有用，在某些情况下甚至可以帮助预防潜在问题的发生。
除了充分利用SQL Server提供的这些资源外，市场上也存在着众多优秀的第三方工具，它们通常具有更加直观易用的界面，并且往往集成了更为丰富的特性和更高级别的自动化程度。比如Redgate、SolarWinds DPA (Database Performance Analyzer) 等软件，在监控系统健康状况、执行深度分析报告以及实现持续集成/持续部署等方面都有着出色的表现。这些工具不仅可以简化复杂的管理任务，还能够在一定程度上提高团队协作效率，让IT专业人员能够更加专注于解决核心业务需求而非繁琐的技术细节。

常见问题场景

1、CPU问题排查：

通常情况下，为了检查数据库当前的活动会话状态，可以使用SQL Server Management Studio (SSMS) 中的“活动监视器”功能。

在某些情况下，为了追踪当前SQL语句的发起源，包括主机和应用程序的相关信息，可以参考以下几个系统视图：

select p.hotname,p.program_name,p.loginame,p.login_time,p.lastwaittype,p.status,s.text from sys.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) as s where p.spid>50 and p.lastwaittype='CXPACKET';1、p.spid>50  远程连接会话
2、p.lastwaittype='CXPACKET'  代表正在使用cpu

历史CPU使用情况的SQL查询分析（主要基于sys.dm_exec_query_stats动态管理视图）
 

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN-1THENDATALENGTH(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 CROSSAPPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDERBY total_worker_time/execution_count DESC; 

2、查看SQL执行计划

• 在 SQL Server 中将执行计划显示为树状结构

方法1：使用SET SHOWPLAN_TEXT

SET SHOWPLAN_TEXT ON;
GO
-- 你的SQL查询语句
GO
SET SHOWPLAN_TEXT OFF;

方法2：使用SET SHOWPLAN_ALL（更详细）

SET SHOWPLAN_ALL ON;
GO
-- 你的SQL查询语句
GO
SET SHOWPLAN_ALL OFF;

方法3：使用SET STATISTICS PROFILE（带实际执行结果）

SET STATISTICS PROFILE ON;
GO
-- 你的SQL查询语句
GO
SET STATISTICS PROFILE OFF;

传统方法：（显示流程图形式）

显示真实执行计划并且附带客户端的统计信息

3、内存占用分析：

查看当前SQLserver服务实例最大内存限制：

查看当前SQLserver服务实例已经占用的操作系统内存：

select physical_memory_in_use_kb/1024 from sys.dm_os_process_memory;

可能会出现当前使用略大于最大内存限制的情况，不必担心。进一步查看sqlserver服务具体的内存模块占用：

主要查看这个值（缓存预期过期时间，内存压力越小，缓存过期时间越大），不要太小

在某些情况下，可能会遇到内存模块占用不匹配的问题。SQL Server的内存使用主要由缓存占据。为了检查当前各个数据库中缓存的具体数据表或索引信息，您可以执行以下查询语句：

select database_id,page_type,count(*)*8/1024 from sys.dm_os_buffer_descriptors group by database_id,page_type;