核心思想：优化不是一蹴而就的，而是一个持续监控、分析、调整的循环过程。
硬件与基础设施优化
这是性能的基石,如果硬件是瓶颈，再好的软件配置和查询优化也收效甚微。
（图片来源网络，侵删）
-
CPU (中央处理器)
- 核心数 vs. 频率：对于 SQL Server，更多的核心数通常比更高的频率更重要，因为 SQL Server 可以充分利用并行处理能力。
- 避免 CPU 竞争：确保服务器没有运行与 SQL Server 无关的高 CPU 占用率的应用程序。
-
内存
- 黄金法则：尽可能多地分配内存给 SQL Server，SQL Server 是一个内存消耗大户，它会将最常用数据和执行计划缓存到内存中，以减少磁盘 I/O。
- 配置：在 SQL Server 服务属性中，将“最大服务器内存”设置为一个合理的值（通常是物理内存的 80%-90%），为操作系统和其他应用程序预留足够空间。
- 监控：监控
Page Life Expectation (PLE)性能计数器，PLE 值越高越好，表示数据在内存中停留的时间越长，一般建议 PLE > 300 秒（对于 OLTP 系统）。
-
磁盘 I/O (输入/输出)
- 分离数据文件和日志文件：
- 数据文件：存储数据和对象，应放置在高速磁盘上（如 SSD）。
- 事务日志文件：记录所有事务。日志文件必须放在最快的、独立的物理磁盘上（最好是 SSD），并且最好有独立的磁盘控制器，日志写入是顺序写入，对 I/O 吞吐量要求极高。
- 使用 RAID 级别：
- RAID 10 (镜像+条带化)：最佳选择，提供极高的 I/O 性能和数据冗余，适用于数据文件和日志文件。
- RAID 5 (条带化+奇偶校验)：成本较低，但写性能较差，适用于只读或读多写少的归档数据。
- RAID 0 (条带化)：性能最高，但无冗余，仅用于临时数据，绝不能用于生产环境。
- 考虑 SSD：对于 I/O 密集型应用（如 OLTP），将数据文件和日志文件迁移到 SSD 是最显著的性能提升之一。
- 分离数据文件和日志文件：
-
网络
（图片来源网络，侵删）确保连接数据库服务器的网络带宽充足,延迟低，对于分布式应用，可以考虑在应用服务器和数据库服务器之间使用更快的网络连接。
SQL Server 配置优化
安装好硬件后,需要正确配置 SQL Server 自身以充分利用硬件资源。
-
服务器内存配置
- 如前所述,设置
max server memory，默认值是动态的，但在生产环境中建议手动设置。
- 如前所述,设置
-
处理器配置
（图片来源网络，侵删）max degree of parallelism (MAXDOP)：控制一个并行查询可以使用的最大 CPU 核心数。- 一般建议：对于有多个核心的服务器，设置为逻辑核心数的一半，但不超过 8，一个 16 核的服务器，可以设置为 8。
- OLTP 系统：建议设置为 1 或 2，以减少资源争用。
- 数据仓库/报表系统：可以设置得更高（如 8 或 16）。
cost threshold for parallelism：决定一个查询的“成本”达到多少时才启用并行执行，默认值是 5，可以根据实际情况调高（如 20-50），以避免短小查询触发并行带来的额外开销。
-
高级配置
instant file initialization(IFI)：开启此选项可以大幅加快数据文件和日志文件的初始化速度（如RESTORE,DBCC SHRINKFILE），开启方法：在 SQL Server 策略中配置“使用 Windows 安全设置中的Perform volume maintenance tasks权限”，并将服务账户添加到Administrators组。Auto growth设置：- 绝对不要使用百分比增长。
- 始终使用固定 MB 增长（每次增长 1024 MB）。
- 将
autogrowth和initial size设置在同一个物理磁盘上，以避免跨盘写入的性能问题。
数据库设计与对象优化
这是应用层面的优化,从根源上决定了查询效率。
-
规范化与反规范化
- 规范化：减少数据冗余，提高数据一致性，但可能导致多表连接查询，增加 I/O 和 CPU 开销。
- 反规范化：通过增加冗余数据或合并表来减少连接查询，适用于读多写少的场景，可以提高查询性能，但会增加写入的复杂性和维护成本。
- 策略：在 OLTP 系统中，通常保持高度规范化；在报表和分析系统中，可以进行适度的反规范化。
-
索引策略
- 索引是“双刃剑”：能极大加速查询，但会降低
INSERT/UPDATE/DELETE速度，并占用存储空间。 - 创建原则：
- 为
WHERE、JOIN、ORDER BY子句中频繁使用的列创建索引。 - 覆盖索引：如果一个索引包含了查询所需的所有列（
SELECT列 +WHERE列），数据库引擎可以直接从索引中获取数据，而无需访问数据行，性能提升巨大。
- 为
- 避免过度索引：定期审查和删除不必要的索引。
- 索引碎片：频繁的增删改会导致索引碎片化，降低 I/O 性能，定期使用
ALTER INDEX ... REORGANIZE（轻度整理）或ALTER INDEX ... REBUILD（完全重建）来维护索引。
- 索引是“双刃剑”：能极大加速查询，但会降低
-
数据类型选择
- 使用最合适的数据类型,用
INT代替BIGINT（如果数值范围足够），用VARCHAR(N)代替NVARCHAR(N)（如果不需要存储多语言字符），可以节省存储空间并提高 I/O 和内存效率。
- 使用最合适的数据类型,用
-
文件组与分区
- 文件组：将表和索引分布到不同的文件组上，可以分散 I/O 负载。
- 表分区：对于非常大的表（如数亿行），可以按时间、ID 等范围进行分区，这样可以实现分区切换（快速归档历史数据）、只扫描相关分区等操作，极大提高管理和查询效率。
查询与代码优化
这是最直接、最频繁的优化点。
-
使用执行计划
- 最重要的工具！学会阅读和解读执行计划是优化查询的关键。
- 关注点：
- 预估 vs. 实际行数：如果差异巨大，统计信息可能过时或查询逻辑有问题。
- 高成本的运算符：如
Key Lookup（书签查找）、Sort、Hash Match、Nested Loops。Key Lookup通常意味着缺少有效的索引。 - 警告图标：黄色感叹号表示查询有潜在问题。
-
避免常见的 SQL 反模式
- *`SELECT `**：只查询需要的列，减少 I/O 和网络传输。
WHERE子句中对列进行函数操作：如WHERE YEAR(OrderDate) = 2025，这会导致索引失效，应改为WHERE OrderDate >= '2025-01-01' AND OrderDate < '2025-01-01'。- 隐式类型转换：如
WHERE ColumnName = 123，ColumnName是varchar类型，会导致全表扫描，确保比较两边的类型一致。 - 不使用
JOIN而使用子查询或IN：有时子查询性能更好，需要通过执行计划验证。 OR条件：WHERE A = 1 OR B = 2可能导致索引失效，可以尝试使用UNION ALL代替。
-
使用参数化查询
- 使用参数化查询可以：
- 重用执行计划：避免 SQL Server 为每次传入的微小变化的字符串重新编译查询。
- 防止 SQL 注入。
- 在应用程序（如 ADO.NET, Entity Framework）中，默认会使用参数化查询，要避免使用字符串拼接的方式构建 SQL 语句。
- 使用参数化查询可以：
-
事务管理
- 保持事务尽可能短：在事务中只执行必要的操作，然后立即提交或回滚。
