【翻译】对 SQL Server DBA 有用的五个查询

介绍

作为 SQL Server DBA，拥有正确的查询集可以显著简化您的工作流程、增强监控并帮助排除故障。以下是解决常见管理任务的五个基本查询，每个查询都附有详细说明，以帮助您了解其目的、解释结果并采取适当的措施。

1. 确定主要等待类型

目的： 了解 SQL Server 实例正在等待什么对于诊断性能瓶颈非常重要。等待统计数据揭示了服务器大部分时间在等待什么，无论是 CPU、内存、I/O 还是锁定问题。

查询：

SELECT 
    wait_type,
    CAST(SUM(wait_time_ms) AS BIGINT) / 1000.0 AS total_wait_time_s,
    CAST(SUM(signal_wait_time_ms) AS BIGINT) / 1000.0 AS total_signal_wait_time_s,
    CAST(SUM(waiting_tasks_count) AS BIGINT) AS waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN
(
    'SLEEP_TASK', 'BROKER_EVENTHANDLER', 'XE_TIMER_EVENT', 'LAZYWRITER_SLEEP',
    'SLEEP_BPOOL_FLUSH', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH',
    'XE_DISPATCHER_WAIT'
)
GROUP BY wait_type
ORDER BY total_wait_time_s DESC;

解释：

• wait_type： SQL Server 实例正在经历的等待的具体类型（例如PAGEIOLATCH_SH，LCK_M_X）。
• total_wait_time_s：自上次重启以来，SQL Server 等待此特定等待类型的总时间（以秒为单位）。
• total_signal_wait_time_s：任务主动等待 CPU 资源的等待时间部分。
• waiting_tasks_count：经历过此等待类型的任务数。

解释结果：

• 高PAGEIOLATCH_SH等待率：表示 SQL Server 正在等待磁盘 I/O 操作，这意味着存在潜在的存储性能问题。
• 高SOS_SCHEDULER_YIELD等待时间：指向 CPU 压力，其中任务由于 CPU 耗尽而频繁让出调度程序。
• 锁等待（LCK_M_）：建议争用锁，可能是由于长时间运行的事务或效率低下的查询造成的。

下一步：

1. I/O瓶颈：
   • 检查磁盘性能：使用性能监视器或 SQL Server 的 DMV 等工具来评估磁盘延迟和吞吐量。
   • 优化查询：识别导致过多 I/O 的查询，并通过添加适当的索引或重写查询逻辑来优化它们。
   • 升级存储：如果磁盘性能一直是瓶颈，请考虑转向更快的存储解决方案，例如 SSD。
2. CPU 压力：
   • 分析 CPU 使用情况：使用 SQL Server Profiler 或扩展事件来识别消耗过多 CPU 的查询。
   • 优化查询：重构低效查询、添加索引或更新统计数据以改进执行计划。
   • 扩展资源：如果尽管进行了优化，但 CPU 使用率仍然很高，请考虑添加更多 CPU 核心或升级硬件。
3. 锁争用：
   • 识别阻塞查询：使用阻塞会话查询（如下所述）来查找和解决阻塞问题。
   • 优化事务：确保事务尽可能短，并使用适当的隔离级别以最大限度地减少锁定。
   • 实现行版本控制：考虑启用快照隔离，通过允许读者访问数据版本而无需获取锁来减少阻塞。

2. 跟踪资源消耗最高的查询

目的： 确定消耗最多资源的查询有助于确定优化工作的优先级。专注于这些“重磅任务”可以显著提高整个系统的性能。

查询：

SELECT TOP 5
    SUBSTRING(qt.TEXT, 
              (qs.statement_start_offset / 2) + 1,
              ((CASE qs.statement_end_offset
                  WHEN -1 THEN DATALENGTH(qt.TEXT)
                  ELSE qs.statement_end_offset
               END - qs.statement_start_offset) / 2) + 1
             ) AS query_text,
    qs.total_logical_reads AS total_logical_reads,
    qs.total_worker_time / 1000 AS total_cpu_ms,
    qs.total_elapsed_time / 1000 AS total_elapsed_ms,
    qs.execution_count,
    DB_NAME(qt.dbid) AS database_name,
    qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_worker_time DESC;

解释：

• query_text：提取消耗资源的 SQL 语句部分。
• total_logical_reads：查询执行的逻辑读取次数，表示从缓冲区缓存访问了多少数据。
• total_cpu_ms：查询消耗的总 CPU 时间（以毫秒为单位）。
• total_elapsed_ms：查询运行的总时间。
• execution_count：查询已执行的次数。
• database_name：运行查询的数据库的名称。
• last_execution_time：上次执行查询的时间戳。

解释结果：

• 高total_cpu_ms：表示查询占用大量 CPU。寻找优化查询的方法以使用更少的 CPU 资源。
• 高total_logical_reads：表明查询正在访问大量数据。请考虑索引策略以减少读取次数。
• 频繁执行（execution_count）：即使单次执行的成本不是特别高，但高执行次数也会导致大量累积资源使用。

下一步：

1. 分析查询计划：
   • sys.dm_exec_query_plan与此查询结合使用来查看已识别查询的执行计划。
   • 查找可能转换为搜索、缺失索引或低效连接的扫描。
2. 优化索引：
   • 确保存在适当的索引来支持查询的 WHERE 子句和 JOIN 条件。
   • 删除任何可能导致过多维护开销的冗余或未使用的索引。
3. 重构查询：
   • 将复杂查询分解为更小、更易于管理的部分，从而简化查询。
   • 避免使用SELECT *，而是仅指定必要的列以减少 I/O。
4. 实现缓存：
   • 对于经常执行且结果静态的查询，请考虑缓存结果以减少数据库的负载。

3.检查数据库增长和文件大小

目的： 监控数据库文件的增长和大小对于防止意外的存储问题以及确保自动增长设置得到最佳配置以避免性能下降至关重要。

查询：

SELECT 
    DB_NAME(database_id) AS database_name,
    name AS logical_file_name,
    physical_name,
    CAST(size AS BIGINT) * 8.0 / 1024 AS size_mb,
    CASE 
        WHEN max_size = -1 THEN 'Unlimited'
        ELSE CAST(CAST(max_size AS BIGINT) * 8.0 / 1024 AS VARCHAR(20))
    END AS max_size_mb,
    growth,
    CASE WHEN is_percent_growth = 1 THEN 'Percent' ELSE 'MB' END AS growth_type
FROM sys.master_files
ORDER BY database_name, logical_file_name;

解释：

• database_name：文件所属数据库的名称。
• logical_file_name：数据库文件的逻辑名称。
• physical_name：数据库文件在磁盘上的物理路径。
• size_mb：文件的当前大小（以兆字节为单位）。
• max_size_mb：文件可以增长到的最大大小，以兆字节为单位。“无限制”表示没有设置限制。
• 增长：文件增长的增量。
• growth_type：指定增长增量是以兆字节 (MB) 为单位还是以百分比为单位。

解释结果：

• 文件大小（size_mb）：文件大小较大可能表示数据增长过多或存储使用效率低下。
• 自动增长设置（growth和growth_type）：将自动增长设置为较小的百分比可能会导致频繁的增长事件，从而降低性能。出于可预测性的考虑，固定 MB 增长通常是首选。
• 最大大小（max_size_mb）：确保文件未设置为“无限制”，除非绝对必要，以防止失控增长。

下一步：

1. 检查自动增长设置：
   • 固定增长：最好以固定的 MB 增量而不是百分比设置自动增长，以保持一致的性能。
   • 足够的增长规模：选择足够大的增长增量来适应典型的增长而不会造成过度碎片化。
2. 容量规划：
   • 预测增长：分析历史增长模式以预测未来的存储需求并相应地分配磁盘空间。
   • 定期监控：设置文件快速增长警报，以便尽早调查潜在问题。
3. 优化文件放置：
   • 分布文件：将数据和日志文件放在单独的物理磁盘上以平衡 I/O 负载。
   • 使用多个数据文件：对于 TempDB 和其他访问量很大的数据库，使用多个数据文件来减少争用并提高性能。
4. 调查意外增长：
   • 大型交易：识别并优化可能导致文件突然增长的交易。
   • 索引重建：定期重建或重新组织索引以有效地管理空间。

4. 发现潜在的缺失索引

目的： 缺少索引会导致全表扫描或数据检索效率低下，从而严重影响查询性能。此查询利用 SQL Server 的动态管理视图 (DMV) 来建议可以提高性能的索引。

查询：

SELECT TOP 5
    DB_NAME(mid.database_id) AS database_name,
    mid.statement AS table_reference,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks + migs.user_scans AS total_accesses,
    migs.last_user_seek AS last_seek_time,
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS index_advantage_score
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig
    ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid
    ON mig.index_handle = mid.index_handle
ORDER BY index_advantage_score DESC;

解释：

• database_name：建议缺少索引的数据库。
• table_reference：可以从新索引中受益的表或视图。
• equal_columns：相等谓词中使用的列（例如WHERE column = value）。
• inequality_columns：范围谓词中使用的列（例如WHERE column > value）。
• included_columns：可以包含在索引中以覆盖查询的附加列。
• total_accesses：可从索引中受益的查找和扫描的总数。
• last_seek_time：上次发生可从索引中受益的查找操作的时间。
• index_advantage_score：一个计算指标，表示索引的潜在利益，考虑到查询成本、影响和频率。

解释结果：

• 高index_advantage_score：由于具有较高的潜在性能提升，强烈建议创建索引。
• 频繁访问（total_accesses）：表明该索引经常被使用，因此值得创建它。
• 最近使用情况（last_seek_time）：确保索引建议与当前工作负载模式相关。

下一步：

1. 评估索引建议：
   • 相关性：确保建议的索引与实际查询模式和工作负载要求一致。
   • 重叠：检查可能已经覆盖建议列的现有索引以避免冗余。
2. 设计索引：
   • 关键列：使用等式列作为关键列，使用不等式列作为包含列，以优化查找操作。
   • 包含列：添加包含的列来覆盖查询，而无需将它们添加到键中，从而减少索引大小和维护开销。
3. 实施和测试：
   • 开发环境：首先在非生产环境中创建索引以评估其影响。
   • 性能监控：部署后，监控查询性能和整体系统行为，以确保索引提供预期的好处，而不会引入过多的维护开销。
4. 维护索引：
   • 定期审查：随着工作负载和查询模式的发展，定期重新评估缺失的索引建议。
   • 索引维护：根据需要重建或重新组织索引以保持其有效性并防止碎片。

5. 检查阻塞会话

目的： 当一个会话锁定其他会话试图访问的资源时，就会发生阻塞，从而导致延迟和潜在的应用程序超时。识别阻塞会话有助于迅速解决争用问题。

查询：

SELECT 
    r.session_id AS blocked_session_id,
    r.blocking_session_id,
    s.login_name AS blocked_login,
    s.host_name AS blocked_host,
    r.wait_type,
    r.wait_time,
    r.wait_resource
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON r.session_id = s.session_id
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

解释：

• blocked_session_id：被阻止的请求的会话 ID。
• blocking_session_id：导致阻止的请求的会话 ID。
• banned_login：与阻止的会话关联的登录名。
• blocked_host：连接到阻止会话的客户端应用程序的主机名。
• wait_type：阻塞会话正在经历的等待类型（例如LCK_M_X）。
• wait_time：会话等待的时长（以毫秒为单位）。
• wait_resource：会话正在等待的特定资源（例如，表、页面、键）。

解释结果：

• 高wait_time：表示会话已经等待了相当长一段时间，这可能会影响应用程序的性能。
• 具体wait_type：有助于识别阻塞的性质，例如排他锁（LCK_M_X）或共享锁（LCK_M_S）。
• wait_resource：精确定位导致阻塞的确切资源，以便有针对性地进行故障排除。

下一步：

1. 识别阻塞会话：
   • 使用blocking_session_id来检索有关阻塞会话的详细信息，例如正在运行的查询。
   • 例子：SELECT qt.TEXT FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt WHERE r.session_id = [blocking_session_id];
2. 评估影响：
   • 确定阻塞是否会导致严重的性能问题或是否是暂时的。
   • 识别可能表明存在潜在问题的模式或频繁块。
3. 解决阻塞：
   • 终止阻塞会话：如果阻塞会话没有响应或者导致严重问题，请考虑使用 终止它KILL [session_id]。
   • 优化交易：确保交易尽可能短，以最大限度地缩短锁定持续时间。
   • 检查隔离级别：使用适当的隔离级别（例如READ COMMITTED SNAPSHOT）来减少锁定争用。
4. 防止将来阻塞：
   • 索引优化：适当的索引可以通过加快查询执行来减少锁争用。
   • 应用程序设计：设计应用程序以有效地处理事务并避免长时间持有锁。
   • 定期监控：实施监控解决方案，主动检测并警告阻塞情况。

结论

这五个查询为 SQL Server DBA 提供了全面的工具包，以有效地监控和优化其数据库环境：

1. 主要等待类型：诊断服务器大部分时间等待的位置。
2. 最耗资源的查询：识别并优化最耗资源的查询。
3. 数据库增长和文件大小：确保主动管理存储以防止出现问题。
4. 潜在的缺失索引：利用 SQL Server 的建议来增强查询性能。
5. 阻塞会话：检测并解决影响应用程序性能的争用问题。

通过将这些查询集成到您的常规维护例程中，您可以维护一个强大、高效且高性能的 SQL Server 环境。定期监控并根据这些查询的洞察及时优化将有助于防止性能下降并确保数据库运行顺畅。

有效使用技巧

• 自动执行：使用 SQL Server 代理作业安排这些查询定期运行，并记录结果以进行趋势分析。
• 自定义过滤器：调整TOP子句或添加其他过滤器，以使查询适合您的特定环境和要求。
• 与监控工具结合：将这些查询与全面的监控解决方案一起使用，以全面了解数据库的健康状况。
• 文档更改：记录根据这些查询的结果所做的任何更改，以跟踪其影响并改进您的优化策略。

通过持续应用这些实践，您将增强维持最佳性能和快速解决 SQL Server 实例中出现的任何问题的能力。

如有任何疑问，请随时联系我们或分享您对这些问题的经验。持续学习和适应是有效数据库管理的关键。