SQL Server 性能诊断（0） - 准备

常见的 SQL Server 性能问题背景

1. 云迁移后的性能下降
   随着企业将 IT 基础设施迁移到云平台，SQL Server 在云环境下的性能表现往往不如在物理机上。尽管云提供了灵活的资源扩展，但云环境的存储延迟、网络瓶颈、虚拟化开销等因素，可能导致性能下降，甚至造成查询响应时间的增加。云平台资源的动态分配和共享，往往也导致了性能不稳定的问题。
2. 存储过程和查询性能下降
   存储过程在执行时出现性能下降，通常与数据库的查询计划、索引选择、统计信息更新等密切相关。长期运行的存储过程和查询在没有优化的情况下，可能因为数据量增加或查询模式改变，导致查询执行效率低下。随着业务规模的增长，存储过程的性能瓶颈逐渐显现，影响整体系统的吞吐量和响应速度。
3. 硬件资源的竞争和瓶颈
   SQL Server 性能受限于硬件资源的配置，尤其是 CPU、内存和磁盘 I/O 的限制。在高并发、大数据量和复杂查询的负载下，单一资源的瓶颈可能导致整体系统性能下降。内存不足、磁盘 I/O 过载或 CPU 利用率过高，都会严重影响 SQL Server 的响应能力。
4. SQL Server 配置问题
   SQL Server 的配置参数，如内存分配、并行度设置、缓存大小、磁盘阵列配置等，都对性能有着直接的影响。错误或不优化的配置可能会导致性能下降，甚至引发系统崩溃或锁死等问题。例如，内存设置过低会导致频繁的磁盘 I/O，查询优化不当会造成 CPU 资源的浪费。

为了全面介入性能排查，将从多个方面入手，包括：

• 内存：通过分析 SQL Server 内存的使用情况，确保内存资源合理分配，避免出现内存瓶颈。
• I/O：评估磁盘 I/O 的负载，判断是否存在磁盘读写延迟或资源争用的问题。
• CPU：监控 CPU 使用率，发现是否存在资源过度消耗或不均衡的情况。
• 阻塞：检查是否有锁竞争或进程阻塞，影响查询响应速度。
• 等待事件：通过分析等待事件，找出系统瓶颈及可能的性能障碍。
• 语句调优：对慢查询、执行计划等进行优化，确保查询执行效率最优。

1. 基础信息收集

一般会先做一次信息收集，快速熟悉陌生的环境。

使用SSMS登录数据库，并将输出调整到以文本形式输出（快捷键：Ctrl+T）

执行如下SQL

-- 查看 SQL Server 实例信息
/*
MachineName: 主机名称
Edition: SQL Server 版本（如 Standard、Enterprise）
ProductVersion: 产品版本号
ProductLevel: 补丁级别（RTM/SP1/SP2 等）
EngineEdition: 引擎版本（1=个人版，2=标准版，3=企业版）
*/
SELECT 
	@@version,
    SERVERPROPERTY('MachineName') AS [MachineName], 
    SERVERPROPERTY('Edition') AS [Edition],
    SERVERPROPERTY('ProductVersion') AS [ProductVersion],
    SERVERPROPERTY('ProductLevel') AS [ProductLevel],
    SERVERPROPERTY('EngineEdition') AS [EngineEdition];

-- 查看 CPU、内存 信息
/*
CPU Count: 逻辑 CPU 数量
Hyperthread Ratio: 每个物理处理器的逻辑核心数
Physical Memory (MB): 物理内存（以 MB 为单位）
SQL Server Start Time: SQL Server 服务启动时间
Scheduler Count: 调度程序数量
*/
SELECT 
    cpu_count AS [CPU Count], 
    hyperthread_ratio AS [Hyperthread Ratio],
    physical_memory_kb / 1024 AS [Physical Memory (MB)],
    sqlserver_start_time AS [SQL Server Start Time],
    scheduler_count AS [Scheduler Count]
FROM sys.dm_os_sys_info;

-- 查看CPU型号
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

EXEC xp_cmdshell 'wmic cpu get name, MaxClockSpeed';

EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

-- 查看内存使用情况
/*
Total Physical Memory (MB): 总物理内存
Available Physical Memory (MB): 可用物理内存
Memory State: 内存状态描述
	Available physical memory is high	系统有足够的物理内存；没有明显的内存压力。
	Available physical memory is low	系统可用物理内存较少；可能存在内存压力。建议检查是否有内存泄漏或非必要的资源消耗。
	Physical memory usage is steady	系统内存使用较为平稳，没有显著变化或压力。
	Physical memory is under pressure	系统物理内存处于压力状态；可能需要优化应用程序内存使用或增加物理内存。
*/
SELECT 
    total_physical_memory_kb / 1024 AS [Total Physical Memory (MB)], 
    available_physical_memory_kb / 1024 AS [Available Physical Memory (MB)], 
    system_memory_state_desc AS [Memory State]
FROM sys.dm_os_sys_memory;

-- 查看磁盘信息
EXEC master.dbo.xp_fixeddrives;
SELECT DISTINCT 
		volume_mount_point [Disk Mount Point], 
		file_system_type [File System Type], 
		logical_volume_name as [Logical Drive Name], 
		CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in GB], ---1GB = 1073741824 bytes
		CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available Size in GB],  
		CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %] 
FROM sys.master_files 
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)

-- 查看SQL Server配置的内存限制
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'max server memory (MB)';
EXEC sp_configure 'min server memory (MB)';

-- 查看硬件信息更多细节
SELECT 
    cpu_count AS [CPU Count],
    hyperthread_ratio AS [Hyperthread Ratio],
    physical_memory_kb / 1024 AS [Physical Memory (MB)],
    virtual_memory_kb / 1024 AS [Virtual Memory (MB)],
    committed_kb / 1024 AS [SQL Server Memory Usage (MB)],
    max_workers_count AS [Max Workers Count]
FROM sys.dm_os_sys_info;

-- 查看数据库文件的统计信息
SELECT 
    name AS [Logical Name],
    physical_name AS [Physical Path],
    size * 8 / 1024 AS [Size (MB)],
    max_size / 128 AS [Max Size (MB)],
    growth * 8 / 1024 AS [Growth (MB)]
FROM sys.master_files;

当然这里CPU只是基准频率，有些服务器支持睿频，这一点需要注意。