SQLServer性能优化宝典.doc

SQL Server性能优化宝典1).优化方法论61.分析实例的等待62.联系等待和队列73.确定方案84.细化到数据库/文件级Page8285.细化到进程级(指存储过程，查询等)96.优化索引/查询102).优化工具101. 查看sql内部执行计划生成 优化信息102. 显示查询计划101).文本102).XML113).图形113. syscachobjects114. 清空缓存(数据/查询计划)115. 动态管理对象116. STATISTICS IO117. STATISTICS TIME128. 分析执行计划128.1. 取得缓存中每个计划的XML计划138.2. 过多的重新编译148.3. 不够高效的查询计划148.4. 执行计划重用次数159. 索引优化159.1. 索引优化概要和分析159.2. 查看索引碎片信息159.3. 重新生成和重新组织索引179.4. 查看没有使用的索引189.5. 查看已经使用且如何使用索引列表199.6. 查看每个表的索引结构209.7. 查看索引列顺序209.8. 同时查看使用和未使用索引及列位置信息219.9. 查看聚集表个数229.10. 索引使用效率评估239.11. 评估索引的选择性2310. 数据库和文件空间2310.1 查看数据库空间2310.2 查看数据库中每个表的占用空间2310.3 查看数据库页面信息2510.4 获取数据文件头部信息2510.5. 查看数据库文件表信息2610.6. 查看数据库信息2610.7. 查看数据库日志文件信息2610.8. 查看表中有多少重定向的行2610.9. 查看数据库的版本和补丁信息2711. 监视命令2712. SQL2813. 跟踪293).性能故障检测方法311. CPU311.1 可运行状态下的工作进程数量311.2 工作进程在可运行状态下花费的时间311.3 每次执行过程中占用CPU最多的前10位查询311.4 每次执行过程中运行最频繁的查询与1.2同用321.5 编译和重编译321.6 获取分配给用于存储优化查询计划的过程高速缓存的内存321.7 确定服务器的活动332. 内存332.1 物理内存压力的检测332.2 虚拟内存压力的检测342.3 内存压力的隔离和排查353. I/O363.1 I/O瓶颈的检测363.2 I/O瓶颈的隔离和排查374. tempdb384.1 tempdb性能问题的检测384.2 tempdb瓶颈的隔离和排查394.3 检查tempdb空闲空间方法405. 阻塞405.1 阻塞的检测(V)405.2 隔离和排查阻塞故障416. 死锁436.1 使用sys.dm_tran_locks DMV在给定的时间点探测表锁(X)436.2 防止锁升级设置表在1小时内防止锁升级436.3 显示发生5秒以上的等待(V)436.4 显示处于WAIT状态的锁(A)(V)446.5 显示每个等待资源已授权和等待中的锁(B)(V)446.6 返回resource_associated_entity_id表示的实际对象(C)(V)456.7 提取等待查询文本(V)466.8监视全快照隔离级别事务466.9命令476.10跟踪标记1204486.11 查看锁信息存储过程1486.12 查看锁信息存储过程2497. 排除故障517.1 207错误提示517.2 查看连接数518. 信息查询518.1 查看数据库是否启用AWE518.2 查看SQL Server 通过 AWE 机制分配了多少内存519. 存储引擎529.1 查看数据库信息(实例/数据库/文件信息)529.2 查看数据库信息(表/对象)529.3 查看数据库信息(对象存储视图)549.4 查看页面结构569.5 PFS页面结构589.6 IAM页面结构589.7 DBCC IND599.8 获取表的页面信息619.9 转换页面地址为页号6210. 资源6310.1 SQLSERVER等待类型631).优化方法论 -分析实例的等待 -联系等待和队列 -确定方案 -细化到数据库/文件级 -细化到进程级 -优化索引/查询1.分析实例的等待 1.1 返回系统中的等待（按类型排序） - SQL Server 2005 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_type; - SQL Server 2000 DBCC SQLPERF(WAITSTATS); -列解释 列名 数据类型 说明 wait_type nvarchar(60) 等待类型的名称。 waiting_tasks_count bigint 该等待类型的等待数。该计数器在每开始一个等待时便会增加。 wait_time_ms bigint 该等待类型的总等待时间（毫秒）。此时间包含 signal_wait_time。 max_wait_time_ms bigint 该等待类型的最长等待时间。 signal_wait_time bigint 正在等待的线程从收到信号通知到其开始运行之间的时差。 -行解释(sql2005联机帮助) ms-help:/MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/tsqlref9/html/568d89ed-2c96-4795-8a0c-2f3e375081da.htm 注意：这些统计信息在每次重新启动 SQL Server 时都不能持续存在，并且所有的数据均为自上次重置统计信息或启动服务器以来累积的数据。 1.2 重置系统中的等待 - SQL Server 2005-该命令将所有计数器重置为 0。-或重启数据库服务器 DBCC SQLPERF (sys.dm_os_wait_stats, CLEAR); - SQL Server 2000 DBCC SQLPERF(WAITSTATS, CLEAR); 1.3 返回系统中的等待(90%重量级) WITH Waits AS ( SELECT wait_type, wait_time_ms / 1000. AS wait_time_s, 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn FROM sys.dm_os_wait_stats WHERE wait_type NOT LIKE %SLEEP% - filter out additional irrelevant waits ) SELECT W1.wait_type, CAST(W1.wait_time_s AS DECIMAL(12, 2) AS wait_time_s, CAST(W1.pct AS DECIMAL(12, 2) AS pct, CAST(SUM(W2.pct) AS DECIMAL(12, 2) AS running_pct FROM Waits AS W1 JOIN Waits AS W2 ON W2.rn = W1.rn GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct HAVING SUM(W2.pct) - W1.pct 90 - percentage threshold OR W1.rn = 10 -至少10行可选条件 ORDER BY W1.rn; GO 1.4 制作等待分布图 TSQL-查询 Page76, 可以得到一天中各种类型等待发生什么时间。2.联系等待和队列 主要根据上面1中结果，分析计数器。分析过程概述见TSQL-查询 Page80 - SQL Server 2005 SELECT object_name, counter_name, instance_name, cntr_value, cntr_type FROM sys.dm_os_performance_counters; - SQL Server 2000 SELECT object_name, counter_name, instance_name, cntr_value, cntr_type FROM master.dbo.sysperfinfo; GO3.确定方案4.细化到数据库/文件级Page82 以I/O为例，如下语句可以查询哪个数据库中的哪类文件导致大部分I/O及等待： - Analyze DB IO WITH DBIO AS ( SELECT DB_NAME(IVFS.database_id) AS db, CASE WHEN MF.type = 1 THEN log ELSE data END AS file_type, SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io, SUM(IVFS.io_stall) AS io_stall FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS JOIN sys.master_files AS MF ON IVFS.database_id = MF.database_id AND IVFS.file_id = MF.file_id GROUP BY DB_NAME(IVFS.database_id), MF.type ) SELECT db, file_type, CAST(1. * io / (1024 * 1024) AS DECIMAL(12, 2) AS io_mb, CAST(io_stall / 1000. AS DECIMAL(12, 2) AS io_stall_s, CAST(100. * io_stall / SUM(io_stall) OVER() AS DECIMAL(10, 2) AS io_stall_pct, ROW_NUMBER() OVER(ORDER BY io_stall DESC) AS rn FROM DBIO ORDER BY io_stall DESC; - SQL Server 2000 SELECT * FROM :fn_virtualfilestats(15, 1); GO5.细化到进程级(指存储过程，查询等) 5.1 生成跟踪存储过程(附件：SQL跟踪存储过程.txt) 5.2 调用跟踪存储过程 - Start the trace DECLARE dbid AS INT, traceid AS INT; SET dbid = DB_ID(Performance); EXEC dbo.sp_perfworkload_trace_start dbid = dbid, tracefile = c:tempPerfworkload, -c:tempPerfworkload.trc traceid = traceid OUTPUT; GO - Stop the trace (不要与上面在一块执行) EXEC sp_trace_setstatus 2, 0; -停止 EXEC sp_trace_setstatus 2, 2; -移除 GO -select * from sys.traces -得到当前正在运行Trace信息 5.3 从跟踪文件输出到表，便于统计分析（sort） - Load trace data to table SET NOCOUNT ON; USE Performance; GO IF OBJECT_ID(dbo.Workload) IS NOT NULL DROP TABLE dbo.Workload; GO SELECT CAST(TextData AS NVARCHAR(MAX) AS tsql_code, Duration AS duration INTO dbo.Workload FROM sys.fn_trace_gettable(c:tempPerfworkload 20060828.trc, NULL) AS T WHERE Duration IS NOT NULL; GO - Aggregate trace data by query SELECT tsql_code, SUM(duration) AS total_duration FROM dbo.Workload GROUP BY tsql_code; - Aggregate trace data by query prefix SELECT SUBSTRING(tsql_code, 1,