管理索引

管理索引

索引新特性: ALTER INDEX statement  Online index operations  Parallel index operations  Locking options  Indexes with included columns  Partitioned indexes  XML indexes

索引新特性-MAXDOP-->MAXDOP(degree of parallelism): 设置查询处理器执行单个索引语句可以使用的最大处理器数。根据当前系统的工作负荷,可以使用较少的处理器。 并行索引操作只适用于SQL Server 2005 Enterprise Edition  MAXDOP索引选项只为指定此选项的查询覆盖max degree of parallelism配置选项

                    并行索引执行和MAXDOP索引选项适用于下列Transact-SQL语句: CREATE INDEX  ALTER INDEX REBUILD  DROP INDEX(只适用于聚集索引。)  ALTER TABLE ADD(索引)CONSTRAINT  ALTER TABLE DROP(聚集索引)CONSTRAINT

                    设置max degree of parallelism选项-->见下列语句:
                    sp_configure 'show advanced options',1;
                    GO
                    RECONFIGURE WITH OVERRIDE;
                    GO
                    sp_configure 'max degree of parallelism',8;
                    GO
                    RECONFIGURE WITH OVERRIDE;
                    GO

                   设置MAXDOP-->见下列语句:

                    CREATE INDEX IX_ProductVendor_VendorID
                    ON Purchasing.ProductVendor(VendorID)
                    WITH(MAXDOP=4);
                    GO

索引新特性-联机执行索引-->在SQL Server 2005中,可以联机创建、重新生成或删除索引  联机索引操作只适用于SQL Server 2005 Enterprise Edition  建议对于全天候运行的业务环境执行联机索引操作

                          USE AdventureWorks;
                          GO
                          ALTER INDEX ALL ON Production.Product
                          REBUILD WITH(ONLINE=ON)  -->见下图:

                             

索引新特性-联机执行索引最佳实践-->当基础表包含大型对象(LOB)数据类型(image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max)和xml)时,必须在脱机状态下创建、重新生成或删除聚集索引  当表包含LOB数据类型时,可以联机创建非聚集索引,但是LOB数据类型列均不能在索引定义中用作键列或非键列。使用LOB数据类型列定义的非聚集索引必须脱机创建或重新生成  无法为本地临时表联机创建、重新生成或删除索引。全局临时表的索引则没有此限制

索引新特性-联机执行索引性能-->无论并发更新活动的级别如何,联机索引操作都将比相应的脱机索引操作更慢  在索引操作过程中性能降低和使用较多的资源  权衡利弊  使用MAXDOP索引选项

索引新特性-索引的锁定-->如果ALLOW_ROW_LOCKS=ON并且ALLOW_PAGE_LOCK=ON,则当访问索引时将允许行、页和表级别的锁。数据库引擎将选择相应的锁,并且可以将锁从行锁或页锁升级到表锁。如果ALLOW_ROW_LOCKS=OFF并且ALLOW_PAGE_LOCK=OFF,则当访问索引时只允许表级锁。ALLOW_PAGE_LOCKS设置为OFF时,无法重新组织索引。ALLOW_ROW_LOCKS={ON|OFF}: 指定是否允许行锁。默认值为ON。ON在访问索引时允许行锁。数据库引擎确定何时使用行锁。OFF未使用行锁。 ALLOW_PAGE_LOCKS={ON|OFF}: 指定是否允许页锁。默认值为ON。ON访问索引时允许使用页锁。数据库引擎确定何时使用页锁。OFF未使用页锁。

索引新特性-分区索引-->什么是分区? 为什么要使用分区? 为了改善大型表的可伸缩性和可管理性。 分区可以带来什么帮助? 当表和索引变得非常大时,分区可以将数据分为更小、更容易管理的部分,从而提高性能。如果具有多个CPU的系统中存在一个大型表,则对该表进行分区可以通过并行操作获得更好的性能。

分区的发展历史-->对SQL Server 7.0之前的版本中的对象进行分区: 在SQL Server 6.5及以前的版本中,分区只能通过设计来完成,还必须内置到所有数据访问编码和查询方法中,性能并没有太大的改善。 SQL Server 7.0中的分区视图: 在SQL Server 7.0版本中,视图结合了约束,允许查询优化程序从查询计划中删除不相关的表(即分区消除),大大降低了联合视图访问多个表时的总计划成本。

SQL Server7.0中的分区视图-->见下图:

                            

使用以下查询访问YearlySales视图的用户只会被引导至SalesJanuary2003表: SELECT ys.* FROM dbo.YearlySales AS ys
                                                                                             WHERE ys.SalesDate = '20030113'
SQL Server 2000中的分区视图: 尽管SQL Server 7.0大大简化了设计并改善了SELECT语句的性能,但是并没有为数据修改语句带来任何好处,还得访问基础表。 SQL Server 2000中的分区视图: 在SQL Server 2000中,数据修改语句还可以受益于SQL Server 7.0中引入的分区视图功能。由于数据修改语句可以使用相同的分区视图结构,因此,SQL Server可以通过视图将修改定向到相应的基础表。允许在多个服务器之间分布这些视图,并直接通过视图更新数据库表

分区的发展历史--SQL Server 2000中的分区视图: SQL Server 2000区分本地分区视图和分布式分区视图。在本地分区视图中,所有的参与表和视图驻留在同一个SQL Server实例上。在分布式分区视图中,至少有一个参与表驻留在不同的(远程)服务器上。创建的基本步骤: 1.在实现分区视图之前,必须先水平分区表。原始表被分成若干个较小的成员表。每个成员表包含与原始表相同数量的列。2.成员表设计好后,每个表基于键值的范围存储原始表的一块水平区域。键值范围基于分区列中的数据值。每一成员表中的值范围通过分区列上的CHECK约束强制,并且范围之间不能重叠。3.在创建成员表后,在服务器上定义一个分区视图。

SQL Server 2005的分区功能增强: SQL Server 2000中并没有简化分区的管理、设计。而且分区数目增加,会使得查询优化时间上升  SQL Server 2005中的分区表: SQL 2005提供了在数据库的文件组之间表分区的功能,水平分区允许把表按分区scheme分为多个小的组。表分区用于非常大的,从几百GB到TB、甚至更大数据库。 通过SQL 2005中的分区表,可以对表进行设计(使用函数和架构),从而将具有相同分区键的所有行都直接放置到(且总是转到)特定的位置。函数用于定义分区边界以及放置第一个值的分区。在使用LEFT分区函数时,第一个值将作为第一个分区中的上边界。在使用RIGHT分区函数时,第一个值将作为第二个分区的下边界。定义函数后即可创建分区架构,分区架构可以将对象映射到一个或多个文件组。为了确定数据的相应物理位置,分区架构将使用了分区函数。根据分区架构创建表。

如何分区一张表? Create the partition function-->Create partition function emailPF(nvarchar(50))as range right for values('G','N')  Create the partition scheme-->Create partition scheme emailPS as partition emailPF to (fg1,fg2,fg3)  Create the partitioned table-->Create table customermail(custid int,email nvarchar(30)on emailPS(email)

索引的增强: 高可用性: Online Index Operations-->联机创建、重建或删除索引的功能是SQL Server 2005的一个增强功能。 易管理性: Index Enhancements-->Rebuild of a Clustered index no longer causes related Noclustered Indexes to be rebuilt  Index with Included Columns一索引覆盖查询  Disabled Indexes

How to Create an Index-->CREATE INDEX statement options: Permitting online operations  Specifying the degree of parallelism  Specifying locking options  Adding included columns  Partitioning indexes  Creating XML indexes