sqlserver优化_物理设计
- 格式：doc
- 大小：55.00 KB
- 文档页数：11
一个数据库系统的性能依赖于组成这些系统的数据库中物理设计结构的有效配置。
这些物理设计结构包括索引、聚集索引、索引视图和分区等，其目的在于提高数据库的性能和可管理性。
SQL Server 2005提供了一套综合的工具，用于优化物理数据库的设计，其中数据库引擎优化顾问，是分析一个或多个数据库上工作负荷（对要做出化的数据库招待的一组T-SQL语名句）的性能效果的工具。
Ø文件和文件组SQL Server 2005 将数据库映射为一组操作系统文件。
数据和日志信息绝不混合在同一个文件中，而且一个文件只由一个数据库使用。
文件组是文件的命名集合，用于简化数据存放和管理任务（例如，备份和还原操作）。
有关详细信息，请参阅物理数据库文件和文件组。
文件和文件组填充策略文件组对组内的所有文件都使用按比例填充策略。
当数据写入文件组时，SQL Server 数据库引擎按文件中的可用空间比例将数据写入文件组中的每个文件，而不是将所有数据都写入第一个文件直至其变满为止。
然后再写入下一个文件。
例如，如果文件 f1 有 100 MB 可用空间，文件 f2 有 200 MB 可用空间，则从文件 f1 中分配一个区，从文件 f2 中分配两个区，依此类推。
这样，两个文件几乎同时填满，并且可获得简单的条带化。
假定将数据库设置为自动增长，则当文件组中的所有文件填满后，数据库引擎便会采用循环方式一次自动扩展一个文件以容纳更多数据。
例如，某个文件组由三个文件组成，它们都设置为自动增长。
当文件组中所有文件的空间都已用完时，只扩展第一个文件。
当第一个文件已满，无法再向文件组中写入更多数据时，将扩展第二个文件。
当第二个文件已满，无法再向文件组中写入更多数据时，将扩展第三个文件。
当第三个文件已满，无法再向文件组中写入更多数据时，将再次扩展第一个文件，依此类推。
改善数据库性能使用文件和文件组可以改善数据库的性能，因为这样允许跨多个磁盘、多个磁盘控制器或 RAID （独立磁盘冗余阵列）系统创建数据库。
例如，如果计算机上有四个磁盘，那么可以创建一个由三个数据文件和一个日志文件组成的数据库，每个磁盘上放置一个文件。
在对数据进行访问时，四个读/写磁头可以同时并行地访问数据。
这样可以加快数据库操作的速度。
有关硬件解决方案的详细信息，请参阅数据库性能。
另外，文件和文件组还允许数据布局，因为可以在特定的文件组中创建表。
这样可以改善性能，因为可以将特定表的所有 I/O 都定向到一个特定的磁盘。
例如，可以将最常用的表放在一个文件组的一个文件中，该文件组位于一个磁盘上；而将数据库中其他不常访问的表放在另一个文件组的其他文件中，该文件组位于第二个磁盘上。
实现备份和还原策略在 SQL Server 2005 中，可以通过称为段落还原的进程分阶段还原由多个文件组组成的数据库。
段落还原适用于所有恢复模式，但在完整恢复模式和大容量日志恢复模式下比在简单恢复模式下更灵活。
段落还原方案包括还原的全部三个阶段：数据复制、重做或前滚以及撤消或后滚。
有关详细信息，请参阅执行段落还原。
当使用多个文件组时，可以分别备份和还原数据库中的文件。
在简单恢复模式下，只能对只读文件进行文件备份。
使用文件备份使您能够只还原损坏的文件，而不用还原数据库的其余部分，从而加快了恢复速度。
例如，如果一个数据库由几个分别位于不同的物理磁盘上的文件组成，当其中一个磁盘发生故障时，只需还原发生故障的磁盘上的文件。
有关详细信息，请参阅BACKUP (Transact-SQL)。
文件和文件组的设计规则下列规则适用于文件和文件组：ü一个文件或文件组不能由多个数据库使用。
例如，任何其他数据库都不能使用包含 sales 数据库中的数据和对象的文件和。
ü一个文件只能是一个文件组的成员。
ü事务日志文件不能属于任何文件组。
使用文件和文件组时的一些一般建议：ü大多数数据库在只有单个数据文件和单个事务日志文件的情况下性能良好。
ü如果使用多个文件，请为附加文件创建第二个文件组，并将其设置为默认文件组。
这样，主文件将只包含系统表和对象。
ü若要使性能最大化，请在尽可能多的不同的可用本地物理磁盘上创建文件或文件组。
将争夺空间最激烈的对象置于不同的文件组中。
ü使用文件组将对象放置在特定的物理磁盘上。
ü将在同一联接查询中使用的不同表置于不同的文件组中。
由于采用并行磁盘I/O 对联接数据进行搜索，所以性能将得以改善。
ü将最常访问的表和属于这些表的非聚集索引置于不同的文件组中。
如果文件位于不同的物理磁盘上，由于采用并行 I/O，所以性能将得以改善。
ü请勿将事务日志文件置于其中已有其他文件和文件组的物理磁盘上。
Ø tempdb 数据库tempdb 系统数据库是一个全局资源，可供连接到 SQL Server 实例的所有用户使用，并可用于保存下列各项：ü显式创建的临时用户对象，例如全局或局部临时表、临时存储过程、表变量或游标。
ü SQL Server 2005 数据库引擎创建的内部对象，例如，用于存储假脱机或排序的中间结果的工作表。
ü由使用已提交读（使用行版本控制隔离或快照隔离事务）的数据库中数据修改事务生成的行版本。
ü由数据修改事务为实现联机索引操作、多个活动的结果集 (MARS) 以及AFTER 触发器等功能而生成的行版本。
tempdb 中的操作是最小日志记录操作。
这将使事务产生回滚。
每次启动 SQL Server 时都会重新创建 tempdb，从而在系统启动时总是保持一个干净的数据库副本。
在断开联接时会自动删除临时表和存储过程，并且在系统关闭后没有活动连接。
因此 tempdb 中不会有什么内容从一个SQL Server 会话保存到另一个会话。
不允许对 tempdb 进行备份和还原操作。
tempdb 性能² tempdb 大小和位置建议tempdb 数据库的大小和物理位置可能会影响系统的性能。
例如，如果为 tempdb 定义的大小过小，则每次重新启动 SQL Server 实例时，都可能会占用部分系统处理负荷，以使 tempdb 自动增长到支持工作负荷所需的大小。
您可以通过增加 tempdb 数据和日志文件的大小来避免此开销。
有关确定 tempdb 所需的适当磁盘空间量的信息，请参阅 tempdb 容量规划。
若要获得最佳的 tempdb 性能，我们建议在生产环境中对 tempdb 进行如下配置：²将 tempdb 的恢复模式设置为 SIMPLE。
此模式自动回收日志空间以保持较小的空间要求。
²有关详细信息，请参阅 ALTER DATABASE (Transact-SQL) 或如何查看或更改数据库恢复模式 (SQL Server Management Studio)。
²使 tempdb 文件的大小可以根据需要自动增大。
这可以使文件的大小增大到磁盘变满为止。
注意：如果生产环境不允许自动增长操作过程中可能出现的应用程序超时，则应为预期的工作负荷预分配空间。
²将文件增量设置为合理的大小以避免 tempdb 数据库文件的增量过小。
如果文件的增量与写入 tempdb 的数据量相比过小，则 tempdb 可能需要不断扩大。
这将影响性能。
建议为 tempdb 文件设置 FILEGROWTH 增量时遵循以下通用原则*您可能必须基于 tempdb 文件所在的 I/O 子系统的速度调整此百分比。
为了避免潜在的闩锁超时，我们建议将自动增长操作限制在大约两分钟之内。
例如，如果 I/O 子系统以每秒 50 MB 的速度初始化文件，则无论 tempdb 文件的大小如何，FILEGROWTH 增量都应设置为最大值 6 GB。
如果可能，请使用实例数据库文件初始化来提高自动增长操作的性能。
²通过将文件大小设置为足够容纳环境中典型工作负荷的值来预分配所有 tempdb 文件的空间。
这可以避免 tempdb 因扩展得过于频繁而影响性能。
tempdb 数据库应设置为自动增长，但是在出现意外情况时此设置将用于增加磁盘空间。
²根据需要创建足够多的文件以使磁盘宽度最大化。
使用多个文件可以减少 tempdb 存储争用并获得更大的可伸缩性。
但是，请勿创建过多的文件，因为此操作可能降低性能并增加管理开销。
作为通用原则，为服务器中的每一个 CPU 创建一个数据文件（用于解释任何关联掩码设置），然后根据需要上下调整文件的数量。
请注意，双核心 CPU 将被视为两个 CPU。
²使每个数据文件的大小相同，这样可以优化比例填充的性能。
²将 tempdb 数据库放置在快速 I/O 子系统中。
如果有许多直接连接的磁盘，则请使用磁盘条带化。
²将 tempdb 数据库放置在用户数据库使用的磁盘以外的磁盘中。
修改 tempdb 大小和增长参数您可以使用下列方法之一修改 tempdb 数据或日志文件的大小和文件增长参数。
ü ALTER DATABASE 语句ü SQL Server Management Studio每次创建 tempdb 时都要使用文件大小和文件增长参数的值。
例如，如果您将 tempdb 数据文件的大小增加到 20 MB 并将文件增量增加到 15%，则新的值将立即生效。
如果后续事务活动使tempdb 的大小增大，则每次重新启动 SQL Server 实例时，数据文件的大小都将返回到 20 MB。
Ø索引索引类型下表列出了 SQL Server 2005 中可用的索引类型，并提供了指向其他信息的链接。
优化²重新组织和重新生成索引无论何时对基础数据执行插入、更新或删除操作，SQL Server 2005 数据库引擎都会自动维护索引。
随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（含有碎片）。
当索引包含的页中的逻辑排序（基于键值）与数据文件中的物理排序不匹配时，就存在碎片。
碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。
²填充因子提供填充因子选项是为了优化索引数据存储和性能。
当创建或重新生成索引时，填充因子值可确定每个叶级页上要填充数据的空间百分比，以便保留一定百分比的可用空间供以后扩展索引。
例如，指定填充因子的值为 80 表示每个叶级页上将有 20% 的空间保留为空，以便随着在基础表中添加数据而为扩展索引提供空间。
填充因子值是 1 到 100 之间的一个百分比。
在大多数情况下，服务器范围的默认值 0 是最佳选项。
如果将填充因子设置为 0，则将完全填充叶级别。
