索引碎片引起死锁问题

满嘴地瓜腔 2018-07-27 07:26:48
数据库版本2005
有一个上百万条数据的表，该表访问频繁，业务运行一段时间后就发生死锁，重启数据库无法解决，
跟踪后发现，将索引删除重建后，可解决死锁，但是再运行一段时间后，又发生死锁。
个人感觉是索引碎片的问题，但不知如何解决？
...全文
138 2 打赏 收藏 转发到动态 举报
写回复
用AI写文章
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
吉普赛的歌 2018-07-29
  • 打赏
  • 举报
 回复
虽然有一些捕获死锁的方法，但目前来说，当务之急并不是捕获死锁。
几百万的表不算小表了，如果增删改频繁，对整个表来说是很容易引起阻塞和死锁的。
建议：
1. 增加历史表，将表中不需要的历史数据转移到历史表。增加作业定期迁移历史数据。
2. 如果表的列很多，创建辅表，将不重要的字段特别是nvarchar(max)之类大数据类型字段移到辅表
3. 对于不重要的查询，一律加 with(nolock)
4. 删除不常用的索引
5. 增加作业每周做一次索引重引

做好上面的这几项，死锁的可能性会大大降低
如果还有，你再来问，此时你问的问题就更经典，更有价值
二月十六 2018-07-27
  • 打赏
  • 举报
 回复
治标的方法写个job，定期自动重建索引。
治本还得找找为啥死锁
SQLServer2008查询性能优化 2/2
书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所需要的工具。建立、维护数据库和数据库服务器可能是个困难的工作。当服务器的运行越来越慢时，这个工作就变得更加困难。来自用户的愤怒的电话以及站在你办公桌周围的管理人员都使你很不快活。在开发代码的同时，如果你花费时间和精力来开发一个性能故障排错的方法。那么你就能避免这种情况——至少可以快速而有效地做出反应。《SQL Server 2008查询性能优化》指出的性能要点之一是数据库随着用户和数据的日益增多而进行扩展的必要性。你需要理解性能低下的起因。以及识别并修复它们的方法。《SQL Server 2008查询性能优化》将帮助你： 使用性能监视器、SQL Trace以及动态管理视图和函数建立性能基线 理解一般系统中发生瓶颈的地方。以及解决瓶颈的方法 识别常见性能问题以及对其快速处理的方法 实施修复甚至预防性能问题的T-SQL最佳实践 《SQL Server 2008查询性能优化》不是理论书籍，它的目的是帮助你避免数据库出现性能低下的状况，它还能帮助你保住你的工作。 内容提要 《SQL Server 2008查询性能优化》通过大量实例，详细介绍了SQL Server数据库系统优化的各种方法和技巧。内容涵盖了数据库应用系统中各种性能瓶颈的表现形式及其发生的根源和解决方法，从硬件瓶颈到查询、索引设计以及数据库管理等，贯穿了数据库系统知识的各个方面。最后以一个实际的工作负载将所有技巧联系起来，并且提供了“宝典”式的最佳实践列表。 《SQL Server 2008查询性能优化》适合于关心数据库应用系统性能的开发人员和数据库管理人员阅读。通过阅读《SQL Server 2008查询性能优化》，不仅可以学习到数据库性能管理的许多知识和技巧，还有助于养成良好的编程习惯，为实现高性能的数据库应用系统打下基础。 目录 第1章 SQL查询性能调整 1 1.1 性能调整过程 2 1.1.1 核心过程 2 1.1.2 迭代过程 4 1.2 性能vs.价格 7 1.2.1 性能目标 7 1.2.2 “足够好”的调整 7 1.3 性能基线 8 1.4 工作的重点 9 1.5 SQL Server性能杀手 10 1.5.1 低质量的索引 10 1.5.2 不精确的统计 11 1.5.3 过多的阻塞和死锁 11 1.5.4 不基于数据集的操作 11 1.5.5 低质量的查询设计 12 1.5.6 低质量的数据库设计 12 1.5.7 过多的碎片 12 1.5.8 不可重用的执行计划 13 1.5.9 低质量的执行计划 13 1.5.10 频繁重编译计划 13 1.5.11 游标的错误使用 13 1.5.12 错误配置数据库日志 14 1.5.13 过多使用或者错误配置tempdb 14 1.6 小结 14 第2章 系统性能分析 15 2.1 性能监视器工具 15 2.2 动态管理视图 17 2.3 硬件资源瓶颈 18 2.3.1 识别瓶颈 18 2.3.2 瓶颈解决方案 19 2.4 内存瓶颈分析 19 2.4.1 SQL Server内存管理 20 2.4.2 Available Bytes 23 2.4.3 Pages/sec和Page Faults/sec计数器 23 2.4.4 Buffer Cache Hit Ratio 24 2.4.5 Page Life Expectancy 24 2.4.6 Checkpoint Pages/sec 24 2.4.7 Lazy writes/sec 24 2.4.8 Memory Grants Pending 25 2.4.9 Target Server Memory(KB)和Total Server Memory(KB) 25 2.5 内存瓶颈解决方案 25 2.5.1 优化应用程序工作负载 26 2.5.2 为SQL Server分配更多内存 27 2.5.3 增加系统内存 27 2.5.4 更换32位处理器为64位处理器 27 2.5.5 启用3GB进程空间 28 2.5.6 在32位SQL Server中使用4GB以上内存 28 2.6 磁盘瓶颈分析 29 2.6.1 磁盘计数器 30 2.6.2 % Disk Time 30 2.6.3 Current Disk Queue Length 31 2.6.4 Disk Transfers/sec 31 2.6.5 Disk Bytes/sec 32 2.6.6 Avg. Disk Sec/Read和Avg. Disk Sec/Write 32 2.7 磁盘瓶颈解决方案 32 2.7.1 优化应用程序工作负载 33 2.7.2 使用更快的磁盘驱动器 33 2.7.3 使用一个RAID阵列 33 2.7.4 使用SAN系统 35 2.7.5 恰当地对齐磁盘 35 2.7.6 使用电池后备的控制器缓存 36 2.7.7 添加系统内存 36 2.7.8 创建多个文件和文件组 36 2.7.9 将表和索引放在不同的磁盘上 39 2.7.10 将日志文件保存到独立的物理磁盘 39 2.7.11 表的分区 40 2.8 处理器瓶颈分析 40 2.8.1 % Processor Time 41 2.8.2 % Privileged Time 41 2.8.3 Processor Queue Length 42 2.8.4 Context Switches/sec 42 2.8.5 Batch Requests/sec 42 2.8.6 SQL Compilations/sec 42 2.8.7 SQL Recompilations/sec 43 2.9 处理器瓶颈解决方案 43 2.9.1 优化应用程序工作负载 43 2.9.2 消除过多的编译/重编译 43 2.9.3 使用更多或更快的处理器 44 2.9.4 使用大的二级(L2)/三级(L3)缓存 44 2.9.5 运行更高效的控制器/驱动程序 44 2.9.6 不运行不必要的软件 45 2.10 网络瓶颈分析 45 2.10.1 Bytes Total/sec 45 2.10.2 % Net Utilization 46 2.11 网络瓶颈解决方案 46 2.11.1 优化应用程序工作负载 46 2.11.2 增加网络适配器 47 2.11.3 节制和避免中断 47 2.12 SQL Server总体性能 47 2.12.1 丢失索引 48 2.12.2 数据库阻塞 49 2.12.3 不可重用的执行计划 50 2.12.4 总体表现 50 2.13 创建一个基线 51 2.13.1 创建性能计数器的一个可重用列表 51 2.13.2 使用性能计数器列表创建一个计数器日志 54 2.13.3 最小化性能监视器开销 55 2.14 以基线为标准的系统状态分析 56 2.15 小结 57 第3章 SQL查询性能分析 58 3.1 SQL Profiler工具 58 3.1.1 Profiler跟踪 59 3.1.2 事件 60 3.1.3 数据列 62 3.1.4 过滤器 64 3.1.5 跟踪模板 65 3.1.6 跟踪数据 65 3.2 跟踪的自动化 66 3.2.1 使用GUI捕捉跟踪 66 3.2.2 使用存储过程捕捉跟踪 67 3.3 结合跟踪和性能监视器输出 68 3.4 SQL Profiler建议 69 3.4.1 限制事件和数据列 69 3.4.2 丢弃性能分析所用的启动事件 70 3.4.3 限制跟踪输出大小 70 3.4.4 避免在线数据列排序 71 3.4.5 远程运行Profiler 71 3.4.6 限制使用某些事件 71 3.5 没有Profiler情况下的查询性能度量 71 3.6 开销较大的查询 72 3.6.1 识别开销较大的查询 73 3.6.2 识别运行缓慢的查询 77 3.7 执行计划 78 3.7.1 分析查询执行计划 80 3.7.2 识别执行计划中开销较大的步骤 82 3.7.3 分析索引有效性 83 3.7.4 分析连接有效性 84 3.7.5 实际执行计划vs.估算执行计划 88 3.7.6 计划缓存 89 3.8 查询开销 90 3.8.1 客户统计 90 3.8.2 执行时间 91 3.8.3 STATISTICS IO 92 3.9 小结 94 第4章 索引分析 95 4.1 什么是索引 95 4.1.1 索引的好处 97 4.1.2 索引开销 98 4.2 索引设计建议 100 4.2.1 检查WHERE子句和连接条件列 100 4.2.2 使用窄索引 102 4.2.3 检查列的唯一性 103 4.2.4 检查列数据类型 106 4.2.5 考虑列顺序 107 4.2.6 考虑索引类型 109 4.3 聚簇索引 109 4.3.1 堆表 110 4.3.2 与非聚簇索引的关系 110 4.3.3 聚簇索引建议 112 4.4 非聚簇索引 117 4.4.1 非聚簇索引维护 117 4.4.2 定义书签查找 117 4.4.3 非聚簇索引建议 118 4.5 聚簇索引vs.非聚簇索引 118 4.5.1 聚簇索引相对于非聚簇索引的好处 119 4.5.2 非聚簇索引相对于聚簇索引的好处 120 4.6 高级索引技术 121 4.6.1 覆盖索引 122 4.6.2 索引交叉 124 4.6.3 索引连接 125 4.6.4 过滤索引 126 4.6.5 索引视图 128 4.6.6 索引压缩 132 4.7 特殊索引类型 134 4.7.1 全文索引 134 4.7.2 空间索引 135 4.7.3 XML 135 4.8 索引的附加特性 135 4.8.1 不同的列排序顺序 135 4.8.2 在计算列上的索引 136 4.8.3 BIT数据类型列上的索引 136 4.8.4 作为一个查询处理的CREATE INDEX语句 136 4.8.5 并行索引创建 136 4.8.6 在线索引创建 137 4.8.7 考虑数据库引擎调整顾问 137 4.9 小结 137 第5章 数据库引擎调整顾问 139 5.1 数据库引擎调整顾问机制 139 5.2 数据库引擎调整顾问实例 143 5.2.1 调整一个查询 143 5.2.2 调整一个跟踪工作负载 146 5.3 数据库引擎调整顾问的局限性 148 5.4 小结 149 第6章 书签查找分析 150 6.1 书签查找的目的 150 6.2 书签查找的缺点 152 6.3 分析书签查找的起因 153 6.4 解决书签查找 155 6.4.1 使用一个聚簇索引 155 6.4.2 使用一个覆盖索引 155 6.4.3 使用索引连接 158 6.5 小结 160 第7章 统计分析 161 7.1 统计在查询优化中的角色 161 7.2 索引列上的统计 162 7.2.1 更新统计的好处 162 7.2.2 过时统计的缺点 164 7.3 在非索引列上的统计 165 7.3.1 在非索引列上统计的好处 166 7.3.2 丢失非索引列上的统计的缺点 169 7.4 分析统计 172 7.4.1 密度 174 7.4.2 多列索引上的统计 174 7.4.3 过滤索引上的统计 175 7.5 统计维护 176 7.5.1 自动维护 177 7.5.2 人工维护 179 7.5.3 统计维护状态 181 7.6 为查询分析统计的有效性 182 7.6.1 解决丢失统计问题 182 7.6.2 解决过时统计问题 184 7.7 建议 186 7.7.1 统计的向后兼容性 186 7.7.2 自动创建统计 186 7.7.3 自动更新统计 187 7.7.4 自动异步更新统计 189 7.7.5 收集统计的采样数量 189 7.8 小结 190 第8章 碎片分析 191 8.1 碎片的成因 191 8.1.1 UPDATE语句引起的页面分割 193 8.1.2 INSERT语句引起的页面分割 196 8.2 碎片开销 197 8.3 分析碎片数量 200 8.4 碎片解决方案 204 8.4.1 卸载并重建索引 204 8.4.2 使用DROP_EXISTING子句重建索引 205 8.4.3 执行ALTER INDEX REBUILD语句 205 8.4.4 执行ALTER INDEX REORGANIZE语句 207 8.5 填充因子的重要性 209 8.6 自动维护 212 8.7 小结 217 第9章 执行计划缓冲分析 218 9.1 执行计划生成 218 9.1.1 解析器 219 9.1.2 代数化器 220 9.1.3 优化 221 9.2 执行计划缓冲 227 9.3 执行计划组件 227 9.3.1 查询计划 227 9.3.2 执行上下文 227 9.4 执行计划的老化 228 9.5 分析执行计划缓冲 228 9.6 执行计划重用 229 9.6.1 即席工作负载 230 9.6.2 预定义工作负载 231 9.6.3 即席工作负载的计划可重用性 231 9.6.4 预定义工作负载的计划可重用性 239 9.7 查询计划Hash和查询Hash 248 9.8 执行计划缓冲建议 251 9.8.1 明确地参数化查询的可变部分 252 9.8.2 使用存储过程实现业务功能 252 9.8.3 使用sp_executesql编程以避免存储过程维护 252 9.8.4 实现准备/执行模式以避免重传查询字符串 253 9.8.5 避免即席查询 253 9.8.6 对于动态查询sp_executesql优于EXECUTE 253 9.8.7 小心地参数化查询的可变部分 254 9.8.8 不要允许查询中对象的隐含解析 254 9.9 小结 254 第10章 存储过程重编译 256 10.1 重编译的好处和缺点 256 10.2 确认导致重编译的语句 258 10.3 分析重编译起因 260 10.3.1 架构或绑定变化 261 10.3.2 统计变化 261 10.3.3 延迟对象解析 264 10.3.4 SET选项变化 266 10.3.5 执行计划老化 266 10.3.6 显式调用sp_recompile 267 10.3.7 显式使用RECOMPILE子句 268 10.4 避免重编译 269 10.4.1 不要交替使用DDL和DML语句 270 10.4.2 避免统计变化引起的重编译 271 10.4.3 使用表变量 273 10.4.4 避免在存储过程中修改SET选项 275 10.4.5 使用OPTIMIZE FOR查询提示 276 10.4.6 使用计划指南 277 10.5 小结 281 第11章 查询设计分析 282 11.1 查询设计建议 282 11.2 在小结果集上操作 283 11.2.1 限制选择列表中的列数 283 11.2.2 使用高选择性的WHERE子句 284 11.3 有效地使用索引 284 11.3.1 避免不可参数化的搜索条件 285 11.3.2 避免WHERE子句列上的算术运算符 289 11.3.3 避免WHERE子句列上的函数 290 11.4 避免优化器提示 292 11.4.1 连接提示 293 11.4.2 索引提示 295 11.5 使用域和参照完整性 296 11.5.1 非空约束 297 11.5.2 声明参照完整性 299 11.6 避免资源密集型查询 301 11.6.1 避免数据类型转换 301 11.6.2 使用EXISTS代替COUNT(*)验证数据存在 303 11.6.3 使用UNION ALL代替UNION 304 11.6.4 为聚合和排序操作使用索引 305 11.6.5 避免在批查询中的局部变量 306 11.6.6 小心地命名存储过程 309 11.7 减少网络传输数量 311 11.7.1 同时执行多个查询 311 11.7.2 使用SET NOCOUNT 311 11.8 降低事务开销 312 11.8.1 减少日志开销 312 11.8.2 减少锁开销 314 11.9 小结 315 第12章 阻塞分析 316 12.1 阻塞基础知识 316 12.2 理解阻塞 317 12.2.1 原子性 317 12.2.2 一致性 320 12.2.3 隔离性 320 12.2.4 持久性 321 12.3 数据库锁 321 12.3.1 锁粒度 322 12.3.2 锁升级 325 12.3.3 锁模式 326 12.3.4 锁兼容性 332 12.4 隔离级别 332 12.4.1 未提交读 333 12.4.2 已提交读 333 12.4.3 可重复读 335 12.4.4 可序列化(Serializable) 338 12.4.5 快照(Snapshot) 343 12.5 索引对锁的作用 343 12.5.1 非聚簇索引的作用 344 12.5.2 聚簇索引的作用 346 12.5.3 索引在可序列化隔离级别上的作用 346 12.6 捕捉阻塞信息 347 12.6.1 使用SQL捕捉阻塞信息 347 12.6.2 Profiler跟踪和被阻塞进程报告事件 349 12.7 阻塞解决方案 351 12.7.1 优化查询 352 12.7.2 降低隔离级别 352 12.7.3 分区争用的数据 353 12.7.4 争用数据上的覆盖索引 354 12.8 减少阻塞的建议 354 12.9 自动化侦测和收集阻塞信息 355 12.10 小结 359 第13章 死锁分析 360 13.1 死锁基础知识 360 13.2 使用错误处理来捕捉死锁 361 13.3 死锁分析 362 13.3.1 收集死锁信息 362 13.3.2 分析死锁 364 13.4 避免死锁 368 13.4.1 按照相同的时间顺序访问资源 368 13.4.2 减少被访问资源的数量 369 13.4.3 最小化锁的争用 369 13.5 小结 370 第14章 游标开销分析 372 14.1 游标基础知识 372 14.1.1 游标位置 373 14.1.2 游标并发性 374 14.1.3 游标类型 376 14.2 游标开销比较 378 14.2.1 游标位置的开销比较 378 14.2.2 游标并发性上的开销比较 380 14.2.3 在游标类型上的开销比较 381 14.3 默认结果集 383 14.3.1 好处 384 14.3.2 缺点 384 14.4 分析SQL Server游标开销 386 14.5 游标建议 390 14.6 小结 392 第15章 数据库工作负载优化 393 15.1 工作负载优化基础知识 393 15.2 工作负载优化步骤 394 15.3 捕捉工作负载 397 15.4 分析工作负载 399 15.5 识别开销最大的查询 400 15.6 确定开销最大的查询的基线资源使用 402 15.6.1 总体资源使用 402 15.6.2 详细资源使用 402 15.7 分析和优化外部因素 405 15.7.1 分析应用程序使用的批级别选项 405 15.7.2 分析统计有效性 406 15.7.3 分析碎片整理需求 406 15.8 分析开销最大的查询的内部行为 410 15.8.1 分析查询执行计划 410 15.8.2 识别执行计划中开销较大的步骤 412 15.8.3 分析处理策略的效率 412 15.9 优化代价最大的查询 412 15.9.1 修改现有索引 413 15.9.2 分析连接提示的应用 415 15.9.3 避免聚簇索引扫描操作 417 15.9.4 修改过程 418 15.10 分析对数据库工作负载的影响 420 15.11 迭代各个优化阶段 421 15.12 小结 424 第16章 SQL Server优化检查列表 425 16.1 数据库设计 425 16.1.1 平衡不足和过多的规范化 426 16.1.2 从实体完整性约束中得利 427 16.1.3 从域和参照完整性约束中得利 428 16.1.4 采用索引设计最佳实践 430 16.1.5 避免在存储过程名称中使用sp_前缀 431 16.1.6 最小化触发器的使用 431 16.2 查询设计 432 16.2.1 使用SET NOCOUNT ON命令 432 16.2.2 显式定义对象所有者 432 16.2.3 避免不可参数化的搜索条件 432 16.2.4 避免WHERE子句列上的算术运算符 433 16.2.5 避免优化器提示 434 16.2.6 远离嵌套视图 434 16.2.7 确保没有隐含的数据类型转换 435 16.2.8 最小化日志开销 435 16.2.9 采用重用执行计划的最佳实践 435 16.2.10 采用数据库事务最佳实践 436 16.2.11 消除或减少数据库游标开销 437 16.3 配置设置 437 16.3.1 Affinity Mask 437 16.3.2 内存配置选项 437 16.3.3 并行性开销阈值 438 16.3.4 最大并行度 438 16.3.5 优化即席工作负载 438 16.3.6 查询调控器开销限制 439 16.3.7 填充因子(%) 439 16.3.8 被阻塞过程阈值 439 16.3.9 数据库文件布局 439 16.3.10 数据库压缩 440 16.4 数据库管理 440 16.4.1 保持统计最新 440 16.4.2 保持最小数量的索引碎片数量 441 16.4.3 循环使用SQL错误日志文件 441 16.4.4 避免像AUTO_CLOSE或AUTO_SHRINK这样的自动化数据库功能 441 16.4.5 最小化SQL跟踪开销 442 16.5 数据库备份 442 16.5.1 增量和事务日志备份频率 442 16.5.2 备份分布 443 16.5.3 备份压缩 444 16.6 小结 444 作者介绍 作者：（美国）弗里奇（Grant Fritchey） （美国）达姆（Sajal Dam） 译者：姚军 弗里奇（Grant Fritchey），为FM Global(一家行业领先的工程和保险公司)工作，担任首席DBA。他使用各种语言(如VB、C#和Java等)开发了许多大规模的应用程序，从版本6．0开始使用SQL Server。他曾经为3家失败的．com公司担任财务和咨询工作，还是Dissecting SQL Server Execution Plans一书的作者。 达姆（Sajal Dam），拥有位于印度班加罗尔的印度理工学院的计算机科学技术硕士学位，并且使用微软技术超过16年。他已经在设计数据库应用和管理软件开发方面拥有了很广泛的背景。Saial还在从前端网页到后端数据库的基于微软技术的应用程序上，具备了故障定位和性能优化的大量经验。他有许多为《财富》500强公司设计可伸缩的数据库解决方案和最大化数据库环境性能的经验。
SQLServer2008查询性能优化 1/2
书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所需要的工具。建立、维护数据库和数据库服务器可能是个困难的工作。当服务器的运行越来越慢时，这个工作就变得更加困难。来自用户的愤怒的电话以及站在你办公桌周围的管理人员都使你很不快活。在开发代码的同时，如果你花费时间和精力来开发一个性能故障排错的方法。那么你就能避免这种情况——至少可以快速而有效地做出反应。《SQL Server 2008查询性能优化》指出的性能要点之一是数据库随着用户和数据的日益增多而进行扩展的必要性。你需要理解性能低下的起因。以及识别并修复它们的方法。《SQL Server 2008查询性能优化》将帮助你： 使用性能监视器、SQL Trace以及动态管理视图和函数建立性能基线 理解一般系统中发生瓶颈的地方。以及解决瓶颈的方法 识别常见性能问题以及对其快速处理的方法 实施修复甚至预防性能问题的T-SQL最佳实践 《SQL Server 2008查询性能优化》不是理论书籍，它的目的是帮助你避免数据库出现性能低下的状况，它还能帮助你保住你的工作。 内容提要 《SQL Server 2008查询性能优化》通过大量实例，详细介绍了SQL Server数据库系统优化的各种方法和技巧。内容涵盖了数据库应用系统中各种性能瓶颈的表现形式及其发生的根源和解决方法，从硬件瓶颈到查询、索引设计以及数据库管理等，贯穿了数据库系统知识的各个方面。最后以一个实际的工作负载将所有技巧联系起来，并且提供了“宝典”式的最佳实践列表。 《SQL Server 2008查询性能优化》适合于关心数据库应用系统性能的开发人员和数据库管理人员阅读。通过阅读《SQL Server 2008查询性能优化》，不仅可以学习到数据库性能管理的许多知识和技巧，还有助于养成良好的编程习惯，为实现高性能的数据库应用系统打下基础。 目录 第1章 SQL查询性能调整 1 1.1 性能调整过程 2 1.1.1 核心过程 2 1.1.2 迭代过程 4 1.2 性能vs.价格 7 1.2.1 性能目标 7 1.2.2 “足够好”的调整 7 1.3 性能基线 8 1.4 工作的重点 9 1.5 SQL Server性能杀手 10 1.5.1 低质量的索引 10 1.5.2 不精确的统计 11 1.5.3 过多的阻塞和死锁 11 1.5.4 不基于数据集的操作 11 1.5.5 低质量的查询设计 12 1.5.6 低质量的数据库设计 12 1.5.7 过多的碎片 12 1.5.8 不可重用的执行计划 13 1.5.9 低质量的执行计划 13 1.5.10 频繁重编译计划 13 1.5.11 游标的错误使用 13 1.5.12 错误配置数据库日志 14 1.5.13 过多使用或者错误配置tempdb 14 1.6 小结 14 第2章 系统性能分析 15 2.1 性能监视器工具 15 2.2 动态管理视图 17 2.3 硬件资源瓶颈 18 2.3.1 识别瓶颈 18 2.3.2 瓶颈解决方案 19 2.4 内存瓶颈分析 19 2.4.1 SQL Server内存管理 20 2.4.2 Available Bytes 23 2.4.3 Pages/sec和Page Faults/sec计数器 23 2.4.4 Buffer Cache Hit Ratio 24 2.4.5 Page Life Expectancy 24 2.4.6 Checkpoint Pages/sec 24 2.4.7 Lazy writes/sec 24 2.4.8 Memory Grants Pending 25 2.4.9 Target Server Memory(KB)和Total Server Memory(KB) 25 2.5 内存瓶颈解决方案 25 2.5.1 优化应用程序工作负载 26 2.5.2 为SQL Server分配更多内存 27 2.5.3 增加系统内存 27 2.5.4 更换32位处理器为64位处理器 27 2.5.5 启用3GB进程空间 28 2.5.6 在32位SQL Server中使用4GB以上内存 28 2.6 磁盘瓶颈分析 29 2.6.1 磁盘计数器 30 2.6.2 % Disk Time 30 2.6.3 Current Disk Queue Length 31 2.6.4 Disk Transfers/sec 31 2.6.5 Disk Bytes/sec 32 2.6.6 Avg. Disk Sec/Read和Avg. Disk Sec/Write 32 2.7 磁盘瓶颈解决方案 32 2.7.1 优化应用程序工作负载 33 2.7.2 使用更快的磁盘驱动器 33 2.7.3 使用一个RAID阵列 33 2.7.4 使用SAN系统 35 2.7.5 恰当地对齐磁盘 35 2.7.6 使用电池后备的控制器缓存 36 2.7.7 添加系统内存 36 2.7.8 创建多个文件和文件组 36 2.7.9 将表和索引放在不同的磁盘上 39 2.7.10 将日志文件保存到独立的物理磁盘 39 2.7.11 表的分区 40 2.8 处理器瓶颈分析 40 2.8.1 % Processor Time 41 2.8.2 % Privileged Time 41 2.8.3 Processor Queue Length 42 2.8.4 Context Switches/sec 42 2.8.5 Batch Requests/sec 42 2.8.6 SQL Compilations/sec 42 2.8.7 SQL Recompilations/sec 43 2.9 处理器瓶颈解决方案 43 2.9.1 优化应用程序工作负载 43 2.9.2 消除过多的编译/重编译 43 2.9.3 使用更多或更快的处理器 44 2.9.4 使用大的二级(L2)/三级(L3)缓存 44 2.9.5 运行更高效的控制器/驱动程序 44 2.9.6 不运行不必要的软件 45 2.10 网络瓶颈分析 45 2.10.1 Bytes Total/sec 45 2.10.2 % Net Utilization 46 2.11 网络瓶颈解决方案 46 2.11.1 优化应用程序工作负载 46 2.11.2 增加网络适配器 47 2.11.3 节制和避免中断 47 2.12 SQL Server总体性能 47 2.12.1 丢失索引 48 2.12.2 数据库阻塞 49 2.12.3 不可重用的执行计划 50 2.12.4 总体表现 50 2.13 创建一个基线 51 2.13.1 创建性能计数器的一个可重用列表 51 2.13.2 使用性能计数器列表创建一个计数器日志 54 2.13.3 最小化性能监视器开销 55 2.14 以基线为标准的系统状态分析 56 2.15 小结 57 第3章 SQL查询性能分析 58 3.1 SQL Profiler工具 58 3.1.1 Profiler跟踪 59 3.1.2 事件 60 3.1.3 数据列 62 3.1.4 过滤器 64 3.1.5 跟踪模板 65 3.1.6 跟踪数据 65 3.2 跟踪的自动化 66 3.2.1 使用GUI捕捉跟踪 66 3.2.2 使用存储过程捕捉跟踪 67 3.3 结合跟踪和性能监视器输出 68 3.4 SQL Profiler建议 69 3.4.1 限制事件和数据列 69 3.4.2 丢弃性能分析所用的启动事件 70 3.4.3 限制跟踪输出大小 70 3.4.4 避免在线数据列排序 71 3.4.5 远程运行Profiler 71 3.4.6 限制使用某些事件 71 3.5 没有Profiler情况下的查询性能度量 71 3.6 开销较大的查询 72 3.6.1 识别开销较大的查询 73 3.6.2 识别运行缓慢的查询 77 3.7 执行计划 78 3.7.1 分析查询执行计划 80 3.7.2 识别执行计划中开销较大的步骤 82 3.7.3 分析索引有效性 83 3.7.4 分析连接有效性 84 3.7.5 实际执行计划vs.估算执行计划 88 3.7.6 计划缓存 89 3.8 查询开销 90 3.8.1 客户统计 90 3.8.2 执行时间 91 3.8.3 STATISTICS IO 92 3.9 小结 94 第4章 索引分析 95 4.1 什么是索引 95 4.1.1 索引的好处 97 4.1.2 索引开销 98 4.2 索引设计建议 100 4.2.1 检查WHERE子句和连接条件列 100 4.2.2 使用窄索引 102 4.2.3 检查列的唯一性 103 4.2.4 检查列数据类型 106 4.2.5 考虑列顺序 107 4.2.6 考虑索引类型 109 4.3 聚簇索引 109 4.3.1 堆表 110 4.3.2 与非聚簇索引的关系 110 4.3.3 聚簇索引建议 112 4.4 非聚簇索引 117 4.4.1 非聚簇索引维护 117 4.4.2 定义书签查找 117 4.4.3 非聚簇索引建议 118 4.5 聚簇索引vs.非聚簇索引 118 4.5.1 聚簇索引相对于非聚簇索引的好处 119 4.5.2 非聚簇索引相对于聚簇索引的好处 120 4.6 高级索引技术 121 4.6.1 覆盖索引 122 4.6.2 索引交叉 124 4.6.3 索引连接 125 4.6.4 过滤索引 126 4.6.5 索引视图 128 4.6.6 索引压缩 132 4.7 特殊索引类型 134 4.7.1 全文索引 134 4.7.2 空间索引 135 4.7.3 XML 135 4.8 索引的附加特性 135 4.8.1 不同的列排序顺序 135 4.8.2 在计算列上的索引 136 4.8.3 BIT数据类型列上的索引 136 4.8.4 作为一个查询处理的CREATE INDEX语句 136 4.8.5 并行索引创建 136 4.8.6 在线索引创建 137 4.8.7 考虑数据库引擎调整顾问 137 4.9 小结 137 第5章 数据库引擎调整顾问 139 5.1 数据库引擎调整顾问机制 139 5.2 数据库引擎调整顾问实例 143 5.2.1 调整一个查询 143 5.2.2 调整一个跟踪工作负载 146 5.3 数据库引擎调整顾问的局限性 148 5.4 小结 149 第6章 书签查找分析 150 6.1 书签查找的目的 150 6.2 书签查找的缺点 152 6.3 分析书签查找的起因 153 6.4 解决书签查找 155 6.4.1 使用一个聚簇索引 155 6.4.2 使用一个覆盖索引 155 6.4.3 使用索引连接 158 6.5 小结 160 第7章 统计分析 161 7.1 统计在查询优化中的角色 161 7.2 索引列上的统计 162 7.2.1 更新统计的好处 162 7.2.2 过时统计的缺点 164 7.3 在非索引列上的统计 165 7.3.1 在非索引列上统计的好处 166 7.3.2 丢失非索引列上的统计的缺点 169 7.4 分析统计 172 7.4.1 密度 174 7.4.2 多列索引上的统计 174 7.4.3 过滤索引上的统计 175 7.5 统计维护 176 7.5.1 自动维护 177 7.5.2 人工维护 179 7.5.3 统计维护状态 181 7.6 为查询分析统计的有效性 182 7.6.1 解决丢失统计问题 182 7.6.2 解决过时统计问题 184 7.7 建议 186 7.7.1 统计的向后兼容性 186 7.7.2 自动创建统计 186 7.7.3 自动更新统计 187 7.7.4 自动异步更新统计 189 7.7.5 收集统计的采样数量 189 7.8 小结 190 第8章 碎片分析 191 8.1 碎片的成因 191 8.1.1 UPDATE语句引起的页面分割 193 8.1.2 INSERT语句引起的页面分割 196 8.2 碎片开销 197 8.3 分析碎片数量 200 8.4 碎片解决方案 204 8.4.1 卸载并重建索引 204 8.4.2 使用DROP_EXISTING子句重建索引 205 8.4.3 执行ALTER INDEX REBUILD语句 205 8.4.4 执行ALTER INDEX REORGANIZE语句 207 8.5 填充因子的重要性 209 8.6 自动维护 212 8.7 小结 217 第9章 执行计划缓冲分析 218 9.1 执行计划生成 218 9.1.1 解析器 219 9.1.2 代数化器 220 9.1.3 优化 221 9.2 执行计划缓冲 227 9.3 执行计划组件 227 9.3.1 查询计划 227 9.3.2 执行上下文 227 9.4 执行计划的老化 228 9.5 分析执行计划缓冲 228 9.6 执行计划重用 229 9.6.1 即席工作负载 230 9.6.2 预定义工作负载 231 9.6.3 即席工作负载的计划可重用性 231 9.6.4 预定义工作负载的计划可重用性 239 9.7 查询计划Hash和查询Hash 248 9.8 执行计划缓冲建议 251 9.8.1 明确地参数化查询的可变部分 252 9.8.2 使用存储过程实现业务功能 252 9.8.3 使用sp_executesql编程以避免存储过程维护 252 9.8.4 实现准备/执行模式以避免重传查询字符串 253 9.8.5 避免即席查询 253 9.8.6 对于动态查询sp_executesql优于EXECUTE 253 9.8.7 小心地参数化查询的可变部分 254 9.8.8 不要允许查询中对象的隐含解析 254 9.9 小结 254 第10章 存储过程重编译 256 10.1 重编译的好处和缺点 256 10.2 确认导致重编译的语句 258 10.3 分析重编译起因 260 10.3.1 架构或绑定变化 261 10.3.2 统计变化 261 10.3.3 延迟对象解析 264 10.3.4 SET选项变化 266 10.3.5 执行计划老化 266 10.3.6 显式调用sp_recompile 267 10.3.7 显式使用RECOMPILE子句 268 10.4 避免重编译 269 10.4.1 不要交替使用DDL和DML语句 270 10.4.2 避免统计变化引起的重编译 271 10.4.3 使用表变量 273 10.4.4 避免在存储过程中修改SET选项 275 10.4.5 使用OPTIMIZE FOR查询提示 276 10.4.6 使用计划指南 277 10.5 小结 281 第11章 查询设计分析 282 11.1 查询设计建议 282 11.2 在小结果集上操作 283 11.2.1 限制选择列表中的列数 283 11.2.2 使用高选择性的WHERE子句 284 11.3 有效地使用索引 284 11.3.1 避免不可参数化的搜索条件 285 11.3.2 避免WHERE子句列上的算术运算符 289 11.3.3 避免WHERE子句列上的函数 290 11.4 避免优化器提示 292 11.4.1 连接提示 293 11.4.2 索引提示 295 11.5 使用域和参照完整性 296 11.5.1 非空约束 297 11.5.2 声明参照完整性 299 11.6 避免资源密集型查询 301 11.6.1 避免数据类型转换 301 11.6.2 使用EXISTS代替COUNT(*)验证数据存在 303 11.6.3 使用UNION ALL代替UNION 304 11.6.4 为聚合和排序操作使用索引 305 11.6.5 避免在批查询中的局部变量 306 11.6.6 小心地命名存储过程 309 11.7 减少网络传输数量 311 11.7.1 同时执行多个查询 311 11.7.2 使用SET NOCOUNT 311 11.8 降低事务开销 312 11.8.1 减少日志开销 312 11.8.2 减少锁开销 314 11.9 小结 315 第12章 阻塞分析 316 12.1 阻塞基础知识 316 12.2 理解阻塞 317 12.2.1 原子性 317 12.2.2 一致性 320 12.2.3 隔离性 320 12.2.4 持久性 321 12.3 数据库锁 321 12.3.1 锁粒度 322 12.3.2 锁升级 325 12.3.3 锁模式 326 12.3.4 锁兼容性 332 12.4 隔离级别 332 12.4.1 未提交读 333 12.4.2 已提交读 333 12.4.3 可重复读 335 12.4.4 可序列化(Serializable) 338 12.4.5 快照(Snapshot) 343 12.5 索引对锁的作用 343 12.5.1 非聚簇索引的作用 344 12.5.2 聚簇索引的作用 346 12.5.3 索引在可序列化隔离级别上的作用 346 12.6 捕捉阻塞信息 347 12.6.1 使用SQL捕捉阻塞信息 347 12.6.2 Profiler跟踪和被阻塞进程报告事件 349 12.7 阻塞解决方案 351 12.7.1 优化查询 352 12.7.2 降低隔离级别 352 12.7.3 分区争用的数据 353 12.7.4 争用数据上的覆盖索引 354 12.8 减少阻塞的建议 354 12.9 自动化侦测和收集阻塞信息 355 12.10 小结 359 第13章 死锁分析 360 13.1 死锁基础知识 360 13.2 使用错误处理来捕捉死锁 361 13.3 死锁分析 362 13.3.1 收集死锁信息 362 13.3.2 分析死锁 364 13.4 避免死锁 368 13.4.1 按照相同的时间顺序访问资源 368 13.4.2 减少被访问资源的数量 369 13.4.3 最小化锁的争用 369 13.5 小结 370 第14章 游标开销分析 372 14.1 游标基础知识 372 14.1.1 游标位置 373 14.1.2 游标并发性 374 14.1.3 游标类型 376 14.2 游标开销比较 378 14.2.1 游标位置的开销比较 378 14.2.2 游标并发性上的开销比较 380 14.2.3 在游标类型上的开销比较 381 14.3 默认结果集 383 14.3.1 好处 384 14.3.2 缺点 384 14.4 分析SQL Server游标开销 386 14.5 游标建议 390 14.6 小结 392 第15章 数据库工作负载优化 393 15.1 工作负载优化基础知识 393 15.2 工作负载优化步骤 394 15.3 捕捉工作负载 397 15.4 分析工作负载 399 15.5 识别开销最大的查询 400 15.6 确定开销最大的查询的基线资源使用 402 15.6.1 总体资源使用 402 15.6.2 详细资源使用 402 15.7 分析和优化外部因素 405 15.7.1 分析应用程序使用的批级别选项 405 15.7.2 分析统计有效性 406 15.7.3 分析碎片整理需求 406 15.8 分析开销最大的查询的内部行为 410 15.8.1 分析查询执行计划 410 15.8.2 识别执行计划中开销较大的步骤 412 15.8.3 分析处理策略的效率 412 15.9 优化代价最大的查询 412 15.9.1 修改现有索引 413 15.9.2 分析连接提示的应用 415 15.9.3 避免聚簇索引扫描操作 417 15.9.4 修改过程 418 15.10 分析对数据库工作负载的影响 420 15.11 迭代各个优化阶段 421 15.12 小结 424 第16章 SQL Server优化检查列表 425 16.1 数据库设计 425 16.1.1 平衡不足和过多的规范化 426 16.1.2 从实体完整性约束中得利 427 16.1.3 从域和参照完整性约束中得利 428 16.1.4 采用索引设计最佳实践 430 16.1.5 避免在存储过程名称中使用sp_前缀 431 16.1.6 最小化触发器的使用 431 16.2 查询设计 432 16.2.1 使用SET NOCOUNT ON命令 432 16.2.2 显式定义对象所有者 432 16.2.3 避免不可参数化的搜索条件 432 16.2.4 避免WHERE子句列上的算术运算符 433 16.2.5 避免优化器提示 434 16.2.6 远离嵌套视图 434 16.2.7 确保没有隐含的数据类型转换 435 16.2.8 最小化日志开销 435 16.2.9 采用重用执行计划的最佳实践 435 16.2.10 采用数据库事务最佳实践 436 16.2.11 消除或减少数据库游标开销 437 16.3 配置设置 437 16.3.1 Affinity Mask 437 16.3.2 内存配置选项 437 16.3.3 并行性开销阈值 438 16.3.4 最大并行度 438 16.3.5 优化即席工作负载 438 16.3.6 查询调控器开销限制 439 16.3.7 填充因子(%) 439 16.3.8 被阻塞过程阈值 439 16.3.9 数据库文件布局 439 16.3.10 数据库压缩 440 16.4 数据库管理 440 16.4.1 保持统计最新 440 16.4.2 保持最小数量的索引碎片数量 441 16.4.3 循环使用SQL错误日志文件 441 16.4.4 避免像AUTO_CLOSE或AUTO_SHRINK这样的自动化数据库功能 441 16.4.5 最小化SQL跟踪开销 442 16.5 数据库备份 442 16.5.1 增量和事务日志备份频率 442 16.5.2 备份分布 443 16.5.3 备份压缩 444 16.6 小结 444 作者介绍 作者：（美国）弗里奇（Grant Fritchey） （美国）达姆（Sajal Dam） 译者：姚军 弗里奇（Grant Fritchey），为FM Global(一家行业领先的工程和保险公司)工作，担任首席DBA。他使用各种语言(如VB、C#和Java等)开发了许多大规模的应用程序，从版本6．0开始使用SQL Server。他曾经为3家失败的．com公司担任财务和咨询工作，还是Dissecting SQL Server Execution Plans一书的作者。 达姆（Sajal Dam），拥有位于印度班加罗尔的印度理工学院的计算机科学技术硕士学位，并且使用微软技术超过16年。他已经在设计数据库应用和管理软件开发方面拥有了很广泛的背景。Saial还在从前端网页到后端数据库的基于微软技术的应用程序上，具备了故障定位和性能优化的大量经验。他有许多为《财富》500强公司设计可伸缩的数据库解决方案和最大化数据库环境性能的经验。
深入解析DB2--高级管理,内部体系结构与诊断案例.part1.rar
第1章 性能调整概述 1.1 性能概述 1.2 性能评估 1.3 建立性能目标 1.4 什么时候需要做性能调整 1.5 性能调整准则 1.6 性能调整的方法和过程 1.7 性能调整总结 第2章 存储I/O设计 2.1 存储基本概念 2.2 存储架构 2.3 存储相关性能调整案例 2.4 存储I/O设计总结 第3章 操作系统相关性能问题 3.1 HP-UX系统性能监控综述 3.2 AIX性能监控综述 3.3 操作系统性能优化 3.4 逻辑卷和lvmo优化 3.5 总结 第4章 数据库物理设计和逻辑设计 4.1 数据库物理设计 4.2 数据库逻辑设计 4.3 使用Autoconfig设计数据库 4.4 其他高级设计技术 4.5 数据库设计总结 第5章 DB2性能监控 5.1 快照监视器案例 5.2 事件监视器及监控案例 5.3 利用表函数监控 5.4 性能管理视图及案例 5.5 db2pd 5.6 db2mtrk及监控案例 5.7 本章小结 第6章 数据库配置参数调整 6.1 数据库配置参数 6.2 监控和调优实例(DBM)配置参数 6.3 监控和调优DB配置参数 6.4 调整DB2概要注册变量 6.5 内存自动调优 6.6 总结 第7章 锁和并发 7.1 锁等待及调整案例 7.2 锁升级及调整案例 7.3 死锁及调整案例 7.4 隔离级别与锁 7.5 最大化并发性 7.6 锁相关的性能问题总结 7.7 锁与应用程序开发 7.8 本章小结 第8章 索引设计与优化 8.1 索引概念 8.2 索引结构 8.3 理解索引访问机制 8.4 索引设计 8.5 索引创建原则与示例 8.6 影响索引性能的相关配置 8.7 索引维护 8.8 DB2 Design Advisor(db2advis) 8.9 索引调整总结 第9章 DB2优化器 9.1 DB2优化器介绍 9.2 SQL语句执行过程 9.3 优化器组件和工作原理 9.4 扫描方式 9.5 连接方法 9.6 优化级别 9.7 如何影响优化器来提高性能 9.8 优化器总结 第10章 统计信息更新与碎片整理 10.1 统计信息更新 10.2 碎片整理 10.3 重新绑定程序包 10.4 本章小结 第11章 SQL语句调优 11.1 通过监控找出最消耗资源的SQL语句 11.2 通过解释工具分析SQL语句执行计划 11.3 理解SQL语句如何工作 11.4 SQL调优案例 11.5 提高应用程序性能 第12章 DB2调优案例、问题总结和技巧 12.1 调优案例一：某移动公司存储设计不当和SQL引起的I/O瓶颈 12.2 调优案例二：某银行知识库系统锁等待、锁升级引起性能瓶颈 12.3 调优案例三：某汽车制造商ERP系统通过调整统计信息提高性能 12.4 调优案例四：某农信社批量代收电费批处理慢调优案例 12.5 调优学习案例：利用压力测试程序学习DB2调优 后记
Oracle之索引和索引碎片问题解决
反正死锁问题你看到刚才那个结果就够了,就是他已经把死锁问题给你解决了,毕竟你只要出现死锁,毕竟有一方用户就弹出来了, 退出了,直接就弹出来了,这是ORACLE系统自己去解决了,咱们继续往下看 下面咱们要讲一个概念,这个概念其实也是一样的,除了死锁这个事,咱们讲索引这个事,那索引我还是以ORACLE为例,MYSQL 其实都是一样的,其实都是想通的,其实上面的这些也都是想通的,ORACL...
聚集索引引起数据库很容易死锁
数据库很容易死锁，我后来发现是聚集索引引起的，索引填充因子是90，后来把聚集索引去掉，问题就解决了，但是有些人不明白聚集索引为什么会引起死锁？？ 首先，填充因子设置得不对。 聚集索引代表了表中记录的存储顺序,所以每次数据的变化,都可能导致表中的数据按照聚集索引重新调整顺序 而填充因子设置为90,这是偏向于减少索引空间占用的做法,这样的做法导致为了减少索引的空间占用,使数据页的
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章