SQL Server 2008之通过非聚集索引提高性能
马啸西风
·
2012-12-22
·
via 博客园 - 马啸西风
- 非聚集索引也是基于二叉树，叶子节点存储的是指向表结构的指针，而不是数据，数据和索引是分开存储的，其Index ID=2或者更高；提高查询性能，降低数据修改性能；一张表最多可以建999个非聚集索引
- 当表是作为堆构建时，叶子节点存储的是Row ID指针；当表是使用聚集键构建时，叶子节点存储的是聚集键，如果聚集键不唯一的话，还需要为每个数据行保存一份uniqueifier值
- 获取索引信息的方法：
- SSMS：可以查看每个表的索引，还包括预先建好的报表，这些报表展示数据库的状态，其中就包括索引信息
- sp_helpindex系统存储过程
- Catalog Views：
- sys.indexes：索引类型、文件组或者partition schema ID和当前索引项设置
- sys.index_columns：Column ID，index ID，类型（聚集还是非聚集）、排序顺序（升序还是降序）
- sys.stats：关于表的统计信息，包括统计项名称，是由用户创建还是自动创建
- sys.stats_columns：关于Column ID的统计信息
- DMVs：
- sys.dm_db_index_physical_stats：索引size和碎片统计信息
- sys.dm_db_index_operational_stats：当前索引和表的I/O统计信息
- sys.dm_db_index_usage_stats：查询类型使用索引的统计信息
- System Functions：
- INDEXKEY_PROPERTY：索引中索引列的位置和列的排序顺序（升序还是降序）
- INDEXPROPERTY：索引类型、Level数、当前设置的索引选项
- INDEX_COL：指定索引的key column的名称
- CREATE INDEX默认创建的都是非聚集索引，创建聚集索引必须显式指定索引类型（Clustered or Nonclustered）；可能的话先创建聚集索引，在创建非聚集索引，否则在创建聚集索引的时候，SQL Server会重建非聚集索引；索引都应该采用统一的命名方式，一种方式是采用创建日期+文档说明方式（这个文档说明索引创建的原因），利于维护；组合非聚集索引就是指定多列作为索引键，组合索引键可以显著提高性能，尤其是针对采用多种方式查询的情况，很少创建单一列的非聚集索引；创建非聚集索引时，列的顺序非常重要，经常查询的列应该放到最前面；每列可以指定排序顺序，默认升序
- 在Nested Loops中Lookups的性能影响
- covering index：在SQL Server2005之前，创建的索引通常都包含了大量的列，避免执行lookup操作提高性能；SQL Server 2005开始引入INCLUDE语句，用来创建covering index；向索引键添加过多的列增加了索引结构的成本（索引页存储的内容变多），另外索引还有16列和900字节的限制；covering index只将INCLUDE语句中指定的列存储在叶子节点中，他们只是用来帮助覆盖查询，INCLUDE语句指定的列的顺序无关紧要；covering index提高查询性能，同时降低数据修改性能
- 只有通过CREATE INDEX语句创建的索引才可以通过DROP INDEX删除，通过PRIMARY KEY或者UNIQUE约束创建的索引只能通过删除约束来删除这些索引
- 使用ALTER INDEX更新索引时不允许修改组成索引键的列、修改列的排序顺序、修改诸如FILLFACTOR和PADINDEX的设置，如果要修改这些信息，只能删除重建；rebuild的ONLINE命令只在Enterprise及更高版本中可用
- 索引的叶子节点默认是包含表中的所有行的，Filtered Indexes叶子节点则只包含了满足WHERE条件的行，WHERE条件只能使用简单的逻辑，比如不能在WHERE语句中比较两列，不能引用计算列，即使计算列持久化也不可以
- SQL Server Profiler是用来优化SQL Server查询的非常重要的工具；Trace只包含选择的事件和选择的列，选择的事件和列是有模板的，也可以创建自己的模板；在使用Trace分析问题时，还可以加载Windows Performance Monitor工具的日志数据，这样允许查看系统资源和SQL Sever查询之间的关系；Trace可以被重放，重放对于加载测试系统或者为已有系统升级SQL Server非常有用，允许你在分析问题的时候对查询进行步进
- SQL Server Profiler是图形化工具，对于被Trace的服务器有很明显的性能影响，SQL Trace是一个用来跟踪的系统存储过程库，它最小化跟踪对服务器产生的负面影响；SQL Server 2008引入的Extended Events系统，也提供了跟踪SQL Server的能力
- Workload就是一组你想要优化的T-SQL语句，来源包括：包含T-SQL语句的文件、SQL Profiler生成的trace文件，SQL Profiler生成的trace信息表
- SQL Server 2000及之前，称为Index Tuning Wizard，SQL Server 2005修改为Database Engine Tuning Advisor，提供了更为广泛的建议，在SQL Server 2008得到增强，提高了workload解析、优化集成、同时优化多个数据库的能力
- Database Engine Tuning Advisor生成的建议包括了对数据库做出的修改，比如创建新的索引，删除旧索引等；生成的建议包括一组实施修改的T-SQL语句，执行这些语句时必须非常小心，必须仔细考虑这些语句
发表于
2012-12-22 23:23
马啸西风
阅读(493)
评论()
收藏
举报
Powered By 博客园
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。
