SQL Server性能优化 #

SQL Server性能优化方向：
├── 查询优化
│   ├── 执行计划分析
│   ├── 索引优化
│   └── SQL重写
├── 数据库设计优化
│   ├── 表设计
│   ├── 索引设计
│   └── 分区
├── 服务器优化
│   ├── 内存配置
│   ├── CPU配置
│   └── I/O配置
└── 应用优化
    ├── 连接池
    ├── 批处理
    └── 缓存

SQL Server性能监控工具：
├── SQL Server Profiler
├── Extended Events
├── DMVs（动态管理视图）
├── Performance Monitor
├── Query Store
└── Activity Monitor

-- 显示估计执行计划
-- SSMS: Ctrl+L

-- 显示实际执行计划
-- SSMS: Ctrl+M，然后执行查询

-- 使用命令查看
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM users WHERE id = 1;
GO
SET SHOWPLAN_TEXT OFF;
GO

-- XML格式执行计划
SET SHOWPLAN_XML ON;
GO
SELECT * FROM users WHERE id = 1;
GO
SET SHOWPLAN_XML OFF;
GO

执行计划关键指标：
├── 扫描类型
│   ├── Table Scan（表扫描）→ 慢
│   ├── Index Scan（索引扫描）→ 中
│   └── Index Seek（索引查找）→ 快
├── 连接类型
│   ├── Nested Loops → 小数据量
│   ├── Hash Match → 大数据量
│   └── Merge Join → 已排序数据
├── 操作成本
│   ├── Estimated I/O Cost
│   ├── Estimated CPU Cost
│   └── Estimated Operator Cost
└── 行数估计
    ├── Estimated Number of Rows
    └── Actual Number of Rows

-- 表扫描（缺少索引）
SELECT * FROM users WHERE email = 'test@example.com';
-- 解决：创建索引

-- 索引扫描（索引不够优化）
SELECT * FROM users WHERE name LIKE '%test%';
-- 解决：使用前缀匹配或全文索引

-- 键查找（非聚集索引需要回表）
SELECT name, email FROM users WHERE email = 'test@example.com';
-- 解决：使用包含列索引

-- 查看缺失索引建议
SELECT 
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_user_impact DESC;

-- 创建建议的索引
CREATE INDEX ix_users_email ON users(email);

-- 查看索引使用情况
SELECT 
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc,
    user_seeks,
    user_scans,
    user_lookups,
    user_updates
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECT_NAME(i.object_id) = 'users';

-- 未使用的索引
SELECT 
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.index_id IS NULL AND i.type_desc = 'NONCLUSTERED';

-- 查看索引碎片
SELECT 
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10;

-- 重组索引（碎片5%-30%）
ALTER INDEX ix_users_email ON users REORGANIZE;

-- 重建索引（碎片>30%）
ALTER INDEX ix_users_email ON users REBUILD;

-- 避免 SELECT *
-- 差
SELECT * FROM users;
-- 好
SELECT id, name, email FROM users;

-- 使用覆盖索引
CREATE INDEX ix_users_email_name ON users(email) INCLUDE (name);

SELECT email, name FROM users WHERE email = 'test@example.com';

-- 避免 OR（使用 UNION ALL）
-- 差
SELECT * FROM users WHERE id = 1 OR id = 2;
-- 好
SELECT * FROM users WHERE id = 1
UNION ALL
SELECT * FROM users WHERE id = 2;

-- 避免 LIKE '%xxx%'
-- 差
SELECT * FROM users WHERE name LIKE '%test%';
-- 好
SELECT * FROM users WHERE name LIKE 'test%';

-- 使用 EXISTS 代替 IN
-- 差
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- 好
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 参数嗅探问题
CREATE PROCEDURE usp_get_users @status BIT
AS
SELECT * FROM users WHERE status = @status;
GO

-- 解决方案1：使用OPTION (RECOMPILE)
CREATE PROCEDURE usp_get_users @status BIT
AS
SELECT * FROM users WHERE status = @status
OPTION (RECOMPILE);
GO

-- 解决方案2：使用局部变量
CREATE PROCEDURE usp_get_users @status BIT
AS
DECLARE @local_status BIT = @status;
SELECT * FROM users WHERE status = @local_status;
GO

-- 解决方案3：使用OPTIMIZE FOR
CREATE PROCEDURE usp_get_users @status BIT
AS
SELECT * FROM users WHERE status = @status
OPTION (OPTIMIZE FOR (@status = 1));
GO

-- 强制使用索引
SELECT * FROM users WITH (INDEX(ix_users_email))
WHERE email = 'test@example.com';

-- 强制使用连接类型
SELECT * FROM users u
INNER LOOP JOIN orders o ON u.id = o.user_id;

-- 使用NOLOCK
SELECT * FROM users WITH (NOLOCK);

-- 使用OPTION提示
SELECT * FROM users
OPTION (MAXDOP 1);  -- 限制并行度

-- 查看统计信息
EXEC sp_helpstats 'users';

-- 查看统计信息详情
DBCC SHOW_STATISTICS ('users', 'ix_users_email');

-- 查看统计信息更新时间
SELECT 
    OBJECT_NAME(object_id) AS table_name,
    name AS stats_name,
    stats_date(object_id, stats_id) AS last_updated
FROM sys.stats;

-- 更新单个表的统计信息
UPDATE STATISTICS users;

-- 更新单个统计信息
UPDATE STATISTICS users ix_users_email;

-- 更新整个数据库的统计信息
EXEC sp_updatestats;

-- 自动更新统计信息
ALTER DATABASE mydb SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE mydb SET AUTO_CREATE_STATISTICS ON;

-- 查看等待统计
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

-- 常见等待类型：
-- PAGEIOLATCH_xx - I/O问题
-- CXPACKET - 并行问题
-- SOS_SCHEDULER_YIELD - CPU问题
-- LCK_xx - 锁问题
-- ASYNC_NETWORK_IO - 网络问题

-- 查看性能计数器
SELECT 
    object_name,
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
   OR object_name LIKE '%Memory Manager%';

-- 启用Query Store
ALTER DATABASE mydb SET QUERY_STORE = ON;

-- 配置Query Store
ALTER DATABASE mydb SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 100
);

-- 查看查询性能
SELECT 
    q.query_id,
    qt.query_sql_text,
    rs.avg_duration,
    rs.avg_logical_io_reads,
    rs.count_executions
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

-- 强制使用特定执行计划
EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 1;

-- 查看内存配置
SELECT 
    name,
    value,
    value_in_use
FROM sys.configurations
WHERE name LIKE '%memory%';

-- 设置最小/最大内存
EXEC sp_configure 'min server memory', 1024;
EXEC sp_configure 'max server memory', 4096;
RECONFIGURE;

-- 查看内存使用
SELECT 
    type,
    SUM(pages_kb) / 1024 AS size_mb
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY size_mb DESC;

-- Buffer Pool使用
SELECT 
    COUNT(*) * 8 / 1024 AS cached_pages_mb,
    COUNT(*) AS cached_pages
FROM sys.dm_os_buffer_descriptors;

查询优化建议：
├── 只查询需要的列
├── 使用适当的索引
├── 避免函数操作列
├── 使用参数化查询
├── 避免游标
├── 使用SET NOCOUNT ON
└── 合理使用事务

索引优化建议：
├── 为WHERE、JOIN、ORDER BY列创建索引
├── 使用覆盖索引
├── 定期维护索引碎片
├── 更新统计信息
├── 删除未使用的索引
└── 避免过度索引