--测试即时文件初始化
CREATE DATABASE [TestFileInit] ON  PRIMARY 
( NAME = N‘TestFileInit‘, FILENAME = N‘F:\DBBackup\TestFileInit.mdf‘ , SIZE = 41943040KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N‘TestFileInit_log‘, FILENAME = N‘F:\DBBackup\TestFileInit_log.ldf‘ , SIZE = 1024KB , FILEGROWTH = 10%)
GO

（Figure7：之前创建时间）

（Figure8：之后创建时间）

注意：禁用即时文件初始化功能，要想让这个禁用生效，一样需要重启SQL Server (MSSQLSERVER)服务。

五.疑问（Questions）

1. 在安装SQL Server的时候，如何设置会使得SQL Server服务是以【网络服务】登陆的？

2. 禁用掉SQL Server的协议Shared Memory，这个协议是用来干嘛的？有什么作用？

3. 如果把NT AUTHORITY\NETWORKSERVICE加入到Windows组里面有什么不安全隐患嘛？

4. 当启用 TDE 时，即时文件初始化功能不可用。什么是TDE？

六.参考文献（References）

数据库文件初始化

为SQL Server 2005配置Windows即时初始化

Local System/Network Service/Local Service权限详解

 

SQL Server 索引中include的魅力（具有包含性列的索引）

开文之前首先要讲讲几个概念

　　【覆盖查询】

　　　　当索引包含查询引用的所有列时，它通常称为“覆盖查询”。

 

 

　　【索引覆盖】

　　 　　如果返回的数据列就包含于索引的键值中，或者包含于索引的键值+聚集索引的键值中，那么就不会发生Bookup Lookup，因为找到索引项，就已经找到所需的数据了，没有必要再到数据行去找了。这种情况，叫做索引覆盖；

 

　　【复合索引】

　　　　和复合索引相对的就是单一索引了，就是索引只包含一个字段，所以复合索引就是包含两个或者多个字段的索引；

　　

　　【非键列】

　　　　键列就是在索引中所包含的列，当然非键列就是该索引之外的列了；

 

下面就开始今天的主题

　　【摘要1】

　　在 SQL Server 2005 中，可以通过将非键列添加到非聚集索引的叶级别来扩展非聚集索引的功能。通过包含非键列，可以创建覆盖更多查询的非聚集索引。这是因为非键列具有下列优点：
    * 它们可以是不允许作为索引键列的数据类型。
    * 在计算索引键列数或索引键大小时，数据库引擎不考虑它们。
　　当查询中的所有列都作为键列或非键列包含在索引中时，带有包含性非键列的索引可以显著提高查询性能。这样可以实现性能提升，因为查询优化器可以在索引中找到所有列值；不访问表或聚集索引数据，从而减少磁盘 I/O 操作。

　　说明：第一：只能是针对非聚集索引；第二：比起复合索引是有性能上的提升的，因为索引的大小变小了；

 

　　【摘要2】

　　键列存储在索引的所有级别中，而非键列仅存储在叶级别中。

　　说明：这就表现为包含与不包含的关系了。有关索引级别的详细信息，请参阅表组织和索引组织。

 

　　【摘要3】

　　使用包含性列以避免大小限制
　　可以将非键列包含在非聚集索引中，以避免超过当前索引大小的限制（最大键列数为 16，最大索引键大小为 900 字节）。数据库引擎计算索引键列数或索引键大小时，不考虑非键列。
　　例如，假设要为 AdventureWorks 示例数据库的 Document 表中的以下列建立索引：
     Title nvarchar(50)
     Revision nchar(5)
     FileName nvarchar(400)
　　因为 nchar 和 nvarchar 数据类型的每个字符需要 2 个字节，所以包含这三列的索引将超出 900 字节的大小限制 10 个字节 (455 * 2)。使用 CREATE INDEX 语句的 INCLUDE 子句，可以将索引键定义为 (Title, Revision)，将 FileName 定义为非键列。这样，索引键大小将为 110 个字节 (55 * 2)，并且索引仍将包含所需的所有列。下面的语句就创建了这样的索引。

　　说明：当你把一个nvarchar(500)的字段设置为主键的时候，你就可以看到不能超出900字节的提示了。一般来说我们是不太会做这些操作的，所以那个错误提示也是不常见的，也许你可能还见过。

　　一个数据页的大小才8k，所以我们合理的设置每个字段的大小，不要浪费太多的空间，这样对查询也是有好处的，这个include就比较好的的解决了索引和空间的问题，虽然那些include的数据也会占用空间。

　　虽然可以设置include，但是也尽量不要使用太多的字段作为索引包含的非键列。

 

　　【摘要4】

　　带有包含性列的索引准则
　　设计带有包含性列的非聚集索引时，请考虑下列准则：
    * 在 CREATE INDEX 语句的 INCLUDE 子句中定义非键列。
    * 只能对表或索引视图的非聚集索引定义非键列。
    * 除 text、ntext 和 image 之外，允许所有数据类型。
    * 精确或不精确的确定性计算列都可以是包含性列。有关详细信息，请参阅为计算列创建索引。
    * 与键列一样，只要允许将计算列数据类型作为非键索引列，从 image、ntext 和 text 数据类型派生的计算列就可以作为非键（包含性）列。
    * 不能同时在 INCLUDE 列表和键列列表中指定列名。
    * INCLUDE 列表中的列名不能重复。

　　说明：include不能使用在聚集索引中。后面的两点，这个在实际中很难想象会有这样的需求要把重复列放到一个索引中。如果有朋友遇到过这样的需求可以告知一些，不胜感激。那如果有是否可以通过不同的列名（其实保存是同样的值）来解决这个问题呢？？

 

　　【摘要5】

　　列大小准则
    * 必须至少定义一个键列。最大非键列数为 1023 列。也就是最大的表列数减 1。
    * 索引键列（不包括非键）必须遵守现有索引大小的限制（最大键列数为 16，总索引键大小为 900 字节）。
    * 所有非键列的总大小只受 INCLUDE 子句中所指定列的大小限制；例如，varchar(max) 列限制为 2 GB。

　　说明：varchar(max)这样的定义是在2005之后才有的，所以这些数值也是对2005后的版本才生效的。

　　最大的表列数为：1024

　　最大非键列数为：1023

 

　　【摘要6】

　　修改已定义为包含性列的表列时，要受下列限制：
    * 除非先删除索引，否则无法从表中删除非键列。
    * 除进行下列更改外，不能对非键列进行其他更改：
          o 将列的为空性从 NOT NULL 改为 NULL。
          o 增加 varchar、nvarchar 或 varbinary 列的长度。 
    * 这些列修改限制也适用于索引键列。

　　说明：这些细小的东西一直没有注意过。所以要记录下来，用来“防身”，呵呵。

 

　　【摘要7】

　　设计建议
　　重新设计索引键大小较大的非聚集索引，以便只有用于搜索和查找的列为键列。将覆盖查询的所有其他列设置为包含性非键列。这样，将具有覆盖查询所需的所有列，但索引键本身较小，而且效率高。

 

　　说明：也就是说把常用的where后面的条件查询的字段作为索引的键列，而需要返回的字段就作为索引包含的非键列。

　　如果where的是两个或两个以上的谓词的话，这个索引就可以创建为复合索引了。以前天真的认为要返回的字段只能通过在复合索引中入这些字段，不管它是否会用来做谓词。看到这篇文章，才有了豁然开朗的感觉。

 

　　【摘要8】

USE AdventureWorks;
GO
CREATE INDEX IX_Address_PostalCode       
ON Person.Address (PostalCode)       
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID); 

　　说明：这个是使用include的语法，在表的设计中的索引设计中是没有办法选择的；

 

 

　　【摘要9】

　　性能注意事项
　　避免添加不必要的列。添加过多的索引列（键列或非键列）会对性能产生下列影响：
    * 一页上能容纳的索引行将更少。这样会使 I/O 增加并降低缓存效率。
    * 需要更多的磁盘空间来存储索引。特别是，将 varchar(max)、nvarchar(max)、varbinary(max) 或 xml 数据类型添加为非键索引列会显著增加磁盘空间要求。这是因为列值被复制到了索引叶级别。因此，它们既驻留在索引中，也驻留在基表中。
    * 索引维护可能会增加对基础表或索引视图执行修改、插入、更新或删除操作所需的时间。
　　您应该确定修改数据时在查询性能上的提升是否超过了对性能的影响，以及是否需要额外的磁盘空间要求。有关评估查询性能的详细信息，请参阅查询优化。

　　说明：“这是因为列值被复制到了索引叶级别”这句很好的说明了物理上的存储结构和原理。

 

　　【图片解析】

 

 　　上图也说明了为什么不能在聚集索引中建立具有包含性列的索引，因为非聚集索引的叶层是由索引页而不是由数据页组成，这就得说到聚集和非聚集索引的的物理存储了，聚集索引的顺序排序和存储就是基表的顺序和存储结构。

 

 

　

　　【一个例子】

SELECT UserName,Password,RealName,Mobile,Age FROM bw_Users WHERE UserName = XXX AND Age = XX

说明：

1. 这是一个我们很常见的查询语句，我们如何提高查询效率呢？
2. 首先我们来看看谓词，这条语句是通过UserName = XXX AND Age = XX作为条件的，那么我们就应该建立一个组合索引，也称为复合索引，注意索引中的键列的位置，先UserName后Age；
3. 其实上面那个是一个非聚集索引，那我们就可以把Password,RealName,Mobile这三列作为索引包含列；
4. 所以，最终就是建立一个以UserName 和 Age做为键列、Password,RealName,Mobile作为非键列的非聚集索引；
5. 通常来说我们系统的用户表并不是很大，所以这样的优化起不了很明显的效果，如果有兴趣的可以使用大表进行性能测试；

 

 

SQL Server 表分区实战系列（文章索引）

一.本文所涉及的内容（Contents）

1. 本文所涉及的内容（Contents）
2. 前言（Introduction）
3. 实战说明（In Action）
4. 表分区逻辑结构图（Construction）
5. 表分区学习流程图（Study Step）
6. 系列文章索引（Catalog）
7. 总结&待续...（Summary & To Be Continued…）

二.前言（Introduction）

前段时间在忙数据库的表分区，经常会去上网找资料，但是在找到都是测试表分区的文章，没有实战经验的，所以在我把表分区运用到实际项目中的时候遇到了很多问题。

比如：如何确认分区字段？分区字段与聚集索引的区别与联系？如何存储分区索引？MSDN说交换分区是以秒计算，但执行40G交换分区超时？如何解决分区不断增长的问题？自动化交换分区的陷阱？

这些问题都只能自己在实战中摸索答案，后来我写了几篇关于这些问题的博文，希望对那些需要实战帮助的童鞋有一点提示和帮助。希望大家拍砖。

三.实战说明（In Action）

某生产数据库大小已经有800G了，每天进库数据量大概有150W条记录（数据空间大概为7G），而服务器现在已经没有太多的磁盘空间了，面对这样的问题，我决定对这个数据库的一个大表做表分区，每个分区的ndf文件为40G，一个分区存储1千万条记录。总的记录数保持在1.2亿的数据量。

当需要新的空间来存储新的数据的时候，我们就通过交换表分区来快速删除一个分区的数据，并使用这个分区来存放新进库的数据。

如果每次都人工来执行交换分区的话就太麻烦了，所以我对这个如何进行交换分区删除数据来清理磁盘空间做成自动化。

在执行自动化的作业却也发现了很多问题，在分区文件达到40G的情况，执行交换分区的时间会很长（在正常进数据的情况下执行作业），而对8G的数据文件进行交换分区时速度非常快， 只能先禁用掉MSSQLSERVER网络协议中的TCP/IP的协议；重启SQLServer服务；执行Job进行交换分区；

四.表分区逻辑结构图（Construction）

 

五.表分区学习流程图（Study Step）

 

六.系列文章索引（Catalog）

Step1：SQL Server 合并（删除）分区解惑

Step2：SQL Server 2005 分区模板与实例

Step3：SQL Server 动态生成分区脚本

Step4：SQL Server 2005 自动化删除表分区设计方案

Step5：SQL Server 表分区注意事项

Step6：SQL Server 自动化管理分区设计方案（图解） 

Step7：SQL Server 维护计划备份主分区

Step8：SQL Server 当表分区遇上唯一约束

Step10：SQL Server 解读【已分区索引的特殊指导原则】（1）- 索引对齐

Step11：SQL Server 解读【已分区索引的特殊指导原则】（2）- 唯一索引分区

Step12：SQL Server 解读【已分区索引的特殊指导原则】（3） - 非聚集索引分区

七.总结&待续...（Summary & To Be Continued…）

       我会继续了解和深入表分区的一些知识，并继续写一些关于表分区实际应用的文章。但是一个人的能力和视野是比较有限，所以大家如果有更好的分区实战经验的话，欢迎拿出来分享和交流。看到一些好的博文也欢迎把地址贴出来。

SQL Server 性能优化实战系列(二)

标签：没有   交流   创建   童鞋   字符   产生   tool   sql   init