ITGuy24 Asked: 2009-07-10 10:13:25 +0800 CST2009-07-10 10:13:25 +0800 CST 2009-07-10 10:13:25 +0800 CST MS SQL 布局以获得最佳性能 772 我们购买了一台新服务器作为 MS SQL 后端。我很想知道最好的设置是什么。 服务器是戴尔 R710，它有 6 个硬盘驱动器 2x 74GB 15k 和 4x 146GB 15k 目前这是在 RAID 1/Raid10 配置中设置的。 我的问题是以下应该去哪里（哪个数组）？ TEMPDB（还有多少、大小和增长） 系统 DB（主数据库、模型等） 应用程序 MDF 应用程序 LDF 系统页面文件 操作系统已安装在 RAID1 上。 sql-server performance database 8 个回答 Voted splattne 2009-07-10T10:42:19+08:002009-07-10T10:42:19+08:00 临时数据库 不久前我做了一些关于 tempdb 优化的研究，并在 Stackoverflow 上回答了我自己的问题。这是我发现的。 为了优化 tempdb 性能，请注意物理磁盘配置、文件配置以及数据库内部的一些设置。 物理磁盘配置 tempdb 应该驻留在它自己的专用物理磁盘上。这允许它从 SQL Server 上的其余卷中拆分 I/O 事务。 要将 tempdb 移动到新磁盘驱动器，请使用ALTER DATABASE. 它是执行此操作的关键 T-SQL 命令。Microsoft 在 SQL Server 2005 联机丛书中提供了一个很好的示例。文章名称是 ALTER DATABASE (Transact-SQL)，具体部分是'G. 将 tempdb 移动到新位置。 tempdb 是一个非常高写入的数据库。因此，RAID 5 阵列不适合它。您应该将 tempdb放在 RAID 1 或 RAID 10 阵列上，因为它们已针对高写入应用程序进行了优化。如果您可以为 tempdb 的每个物理数据库文件提供额外的 RAID 1 或 RAID 10 阵列，您将获得更高的性能。 数据库文件 服务器中的每个 CPU 核心应该有一个物理文件。所以，如果你有一个双芯片、双核服务器，你应该有四个用于 tempdb 数据库的物理数据库文件。添加更多数据库文件时，将文件配置为相同的初始大小和相同的增长设置非常重要。这样，SQL Server 将尽可能均匀地跨文件写入数据。 数据库文件大小 tempdb 数据库的大小会影响系统的性能。例如，如果为 tempdb 定义的大小太小，则系统处理负载的一部分可能会被自动增长的 tempdb 占用到每次重新启动 SQL Server 实例时支持工作负载所需的大小。您可以通过增加 tempdb 数据和日志文件的大小来避免这种开销。 在生产环境中确定适当的 tempdb 大小取决于许多因素，包括现有工作负载和使用的 SQL Server 功能。Microsoft 建议您通过在 SQL Server测试环境中执行以下任务来分析现有工作负载： 为 tempdb 设置自动增长（在测试环境中！）。 执行单个查询或工作负载跟踪文件并监控 tempdb 空间使用情况。 执行索引维护操作，例如重建索引和监控 tempdb 空间。 使用前面步骤中的空间使用值来预测您的总工作负载使用情况；为预计的并发活动调整此值，然后相应地设置 tempdb 的大小。 tempdb 的最小大小建议如下： 环境。大小 DB 大小 (MB) 日志大小 (MB) ------------ ------------ ------------ 小号 1024 256 中型 5120 1024 大号 10024 2048 数据库设置 您可以通过禁用自动更新统计信息来进一步提高 tempdb 性能，这将为您的 tempdb 节省一些工作。您还可以将自动创建统计信息选项设置为 false。 免责声明：应谨慎更改设置。根据您对 tempdb 施加的负载类型，更改设置可能会对系统性能产生不利影响。 要获得最佳 tempdb 性能，请遵循优化 tempdb 性能中提供的指南和建议。 如何监控 tempdb 的使用情况？ tempdb 中的磁盘空间不足会导致 SQL Server 生产环境严重中断，并可能阻止正在运行的应用程序完成操作。 您可以使用sys.dm_db_file_space_usage动态管理视图来监控这些功能在 tempdb 文件中使用的磁盘空间。此外，要在会话或任务级别监视 tempdb 中的页面分配或解除分配活动，您可以使用sys.dm_db_session_space_usage动态sys.dm_db_task_space_usage管理视图。 这些视图可用于识别使用大量 tempdb 磁盘空间的大型查询、临时表或表变量。还有几个计数器可用于监视 tempdb 中的可用空间以及正在使用 tempdb 的资源。 链接： Microsoft Technet：在 SQL Server 2005 中使用 tempdb SearchSQLServer.com：SQL Server tempdb 最佳实践提高性能 MSDN 库：优化 tempdb 性能 SearchSQLServer.com：配置 SQL Server 2005 TempDB 以提高性能 MSDN 库：tempdb 的容量规划 MSDN 库：对 tempdb 中的磁盘空间不足进行故障排除 Best Answer SuperCoolMoss 2009-07-10T12:27:32+08:002009-07-10T12:27:32+08:00 我会将操作系统、页面文件和 LDF 放在 RAID1 阵列上。RAID10 阵列上的所有其他内容。 如果您使用的不是 Windows 2008，请确保您的分区正确对齐： http://msdn.microsoft.com/en-us/library/dd758814.aspx 如前所述，为每个 cpu 核心添加 1 个 TEMPDB 文件 - 使它们的大小相同。 适当调整日志文件的大小并一步创建它们。 如果可能，请考虑通过网络共享将您的数据库备份到不同的服务器 - 以降低您的阵列完全失败并带走您的数据库和备份的风险。 James 2009-07-10T10:40:57+08:002009-07-10T10:40:57+08:00 简单的通用答案是任何涉及高 IO 的东西都应该放在 RAID 10 磁盘组上。您是否还决定了分区策略或问题的那一部分？ 所以在你的第一个磁盘组中，我可能会创建一个单独的分区（大约 70GB 可用），我会将操作系统和 MSSQL 应用程序放在上面。 在第二个我将创建以下分区 1) 页面文件分区（取决于您拥有的内存，但大约 10-20GB 2）事务日志文件分区 100GB 3）数据文件分区 100GB 这将为您留下大约 50GB 的备用空间，我将保留未分配的空间，以便您可以根据需求的变化增加日志分区或数据。 有趣的是，我目前正在使用相同规格的机器，只是我使用的是 Linux 和 Oracle，你是我来自平行宇宙吗？ 詹姆士 mrdenny 2009-07-10T22:42:20+08:002009-07-10T22:42:20+08:00 到目前为止已经有一些很好的答案，所以我不会费心提出一个，但我会向你提出一个问题。 为什么在购买服务器之前不问这个问题？您为什么要尝试将系统塞入已经购买的服务器中，而不是购买服务器来满足系统的需求？ ITGuy24 2009-07-11T04:51:52+08:002009-07-11T04:51:52+08:00 抱歉，我以未注册用户的身份提出了这个问题，现在无法将其标记为已回答。我正在等待管理员返回这里，看看他们是否可以再次将我的帐户附加到问题中。 为了更深入地了解这个服务器不会承受巨大的负载，它实际上是一个源代码控制后端。购买服务器和 DAS 或 ISCSI SAN 或其他东西会过分杀戮，而且成本会粉碎项目的机会。我为大约 100 人的 SMB 工作，所以我们的 IT 预算很紧，尤其是现在。 @mrdenny 我们确实向推荐此配置的软件供应商提出了这个问题。当我们提出我在这里问的任务时，我们买了它，他们说将日志、数据库和 tempdbs 都放在 raid 10 分区上。我不是一个大数据库人（真的吗？？）但这听起来很可疑，因为几乎每个人都知道不要在同一个主轴上混合你的 ldfs 和 mdfs。 @splattne - 感谢您对 tempdb 的深入了解，这将在此和未来的 SQL 安装中派上用场。 @SuperCoolMoss - 我已经与我自己的 IT 朋友网络中的一些人交谈过，他们对此表示同意。RAID1 上的操作系统、页面文件和 LDF 以及 RAID10 上的 tempdb 和 MDF 感谢所有其他发表评论的人。 根据我的阅读，这些是我认为在磁盘方面部署 SQL 时应该遵循的基本规则。在我看来，它们应该按照这个顺序进行，如果您同意或不同意，请告诉我。 使用冗余磁盘（很明显） 使用快速磁盘（SCSI/SAS，如果可能的话 15k） 在不同的主轴上分离你的 ldf 和 mdf 文件 不要将 RAID5 用于 ldfs 或 tempdbs（使用 RAID10 或 RAID1） 把你的 tempdb 放在你最快的主轴上；如果可能，将 tempdbs 与 ldf 和 mdf 心轴分开。 请随时分享您对此列表的修改版本。 user2278 2009-07-10T10:43:42+08:002009-07-10T10:43:42+08:00 您需要多少性能？很难根据“这里有一些磁盘”得出答案。另外，如果您可以将所有数据放入缓存中，SQL 性能会更好，因此请确保您有足够的 RAM。 我会在你的 2x74Gb 卷上使用操作系统和页面文件。如果您遇到日志流量很大的情况，您可以将您的 RAID 10 拆分为一对 RAID 1 并将您的日志放在那里。 显然有很多关于“最佳”布局的宗教战争，但你真的没有足够的磁盘来移动。对数据驱动器进行分区是没有意义的，因为您真正关心的是主轴的数量，并且您受到服务器中可以容纳的内容的限制。 您最好购买 PowerVault 或类似的设备，然后将 146Gb 驱动器回收到其中（以及尽可能多的其他驱动器）并将其拆分为数据/日志/系统 + tempdb 卷。 Remus Rusanu 2009-07-10T21:31:36+08:002009-07-10T21:31:36+08:00 我将从从旧系统收集一些 IO 统计信息开始。例如，从性能仪表板报告开始： http: //www.microsoft.com/downloads/details.aspx? FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en这将为您提供数据库的 IO 统计信息，然后您可以做出明智的决定，而不是“一刀切”的通用建议。当然，旧服务器的每个数据库的 IO 特性可能与新服务器不同，特别是在可用 RAM 量不同的情况下。仍然比纯粹的猜测要好。 理想情况下，目标是将负载较高的 I/O 文件分离到自己的设备中，并且不希望将 MDF 访问的随机 I/O 与 LDF 访问的线性写入混为一谈。在您的情况下，实际上并没有太多的组合，您实际上只有 2 个位置（RAID1 中的 2 个磁盘和 RAID10 中的 4 个）。您可以总结从旧服务器收集的每个数据库的 IO，然后拆分文件位置，以便它以 RAID1 上的 1/5 和 RAID10 上的 4/5 的比率在 2 个目标之间分配 IO（考虑到用于 RAID1 上的操作系统和页面文件 IO）。希望您没有任何写入密集型应用程序（高顺序 LDF 写入负载）... TempDB 布局通常建议为每个调度程序 1 个 NDF（未屏蔽 CPU 核心），所有大小都相同，原因在此解释：http: //blogs.msdn.com/sqlserverstorageengine/archive/2009/01/04 /managing-tempdb-in-sql-server-tempdb-configuration.aspx。这解决了在 SQL 2K 上的高负载下可能是一个严重问题的页面分配争用，在 2k5 下不太严重，在 2k8 上甚至更少。但是考虑到配置它基本上不需要任何成本，你还是应该这样做。 John Rennie 2009-07-11T00:05:41+08:002009-07-11T00:05:41+08:00 这不是一个真正快速的磁盘系统。为 splattne 提供了一个很棒的答案，但实际上这不是一个非常快的磁盘设置，所以我不确定它有多大的麻烦。 两个磁盘 RAID1 与单个未RAID 磁盘相比，几乎没有或根本没有提高性能。四盘 RAID10 为您提供两盘 RAID0 的速度，速度很快但并不惊人。它不是一个缓慢的服务器，但它不是运行真正要求苛刻的 SQL 数据库的明显候选者。 如果它是我的服务器，我会再找两个 146GB 的磁盘并将其设置为六磁盘 RAID5。好的，我会因为将 tempdb 放在与数据文件相同的主轴上而大喊大叫，但是 Perc 控制器非常擅长 RAID5，而且六磁盘 RAID5 会非常快，你（可能）会得到比 RAID1 上的 tempdb 和 RAID5 中只有 4 个磁盘更好的性能。我说“可能”是因为一切都取决于你如何加载它。 JR
临时数据库
不久前我做了一些关于 tempdb 优化的研究，并在 Stackoverflow 上回答了我自己的问题。这是我发现的。
为了优化 tempdb 性能，请注意物理磁盘配置、文件配置以及数据库内部的一些设置。
物理磁盘配置
tempdb 应该驻留在它自己的专用物理磁盘上。这允许它从 SQL Server 上的其余卷中拆分 I/O 事务。
要将 tempdb 移动到新磁盘驱动器，请使用
ALTER DATABASE. 它是执行此操作的关键 T-SQL 命令。Microsoft 在 SQL Server 2005 联机丛书中提供了一个很好的示例。文章名称是 ALTER DATABASE (Transact-SQL)，具体部分是'G. 将 tempdb 移动到新位置。tempdb 是一个非常高写入的数据库。因此，RAID 5 阵列不适合它。您应该将 tempdb放在 RAID 1 或 RAID 10 阵列上，因为它们已针对高写入应用程序进行了优化。如果您可以为 tempdb 的每个物理数据库文件提供额外的 RAID 1 或 RAID 10 阵列，您将获得更高的性能。
数据库文件
服务器中的每个 CPU 核心应该有一个物理文件。所以，如果你有一个双芯片、双核服务器，你应该有四个用于 tempdb 数据库的物理数据库文件。添加更多数据库文件时，将文件配置为相同的初始大小和相同的增长设置非常重要。这样，SQL Server 将尽可能均匀地跨文件写入数据。
数据库文件大小
tempdb 数据库的大小会影响系统的性能。例如，如果为 tempdb 定义的大小太小，则系统处理负载的一部分可能会被自动增长的 tempdb 占用到每次重新启动 SQL Server 实例时支持工作负载所需的大小。您可以通过增加 tempdb 数据和日志文件的大小来避免这种开销。
在生产环境中确定适当的 tempdb 大小取决于许多因素，包括现有工作负载和使用的 SQL Server 功能。Microsoft 建议您通过在 SQL Server测试环境中执行以下任务来分析现有工作负载：
tempdb 的最小大小建议如下：
数据库设置
您可以通过禁用自动更新统计信息来进一步提高 tempdb 性能，这将为您的 tempdb 节省一些工作。您还可以将自动创建统计信息选项设置为 false。
免责声明：应谨慎更改设置。根据您对 tempdb 施加的负载类型，更改设置可能会对系统性能产生不利影响。
要获得最佳 tempdb 性能，请遵循优化 tempdb 性能中提供的指南和建议。
如何监控 tempdb 的使用情况？
tempdb 中的磁盘空间不足会导致 SQL Server 生产环境严重中断，并可能阻止正在运行的应用程序完成操作。
您可以使用
sys.dm_db_file_space_usage动态管理视图来监控这些功能在 tempdb 文件中使用的磁盘空间。此外，要在会话或任务级别监视 tempdb 中的页面分配或解除分配活动，您可以使用sys.dm_db_session_space_usage动态sys.dm_db_task_space_usage管理视图。这些视图可用于识别使用大量 tempdb 磁盘空间的大型查询、临时表或表变量。还有几个计数器可用于监视 tempdb 中的可用空间以及正在使用 tempdb 的资源。
链接：
我会将操作系统、页面文件和 LDF 放在 RAID1 阵列上。RAID10 阵列上的所有其他内容。
如果您使用的不是 Windows 2008，请确保您的分区正确对齐：
http://msdn.microsoft.com/en-us/library/dd758814.aspx
如前所述，为每个 cpu 核心添加 1 个 TEMPDB 文件 - 使它们的大小相同。
适当调整日志文件的大小并一步创建它们。
如果可能，请考虑通过网络共享将您的数据库备份到不同的服务器 - 以降低您的阵列完全失败并带走您的数据库和备份的风险。
简单的通用答案是任何涉及高 IO 的东西都应该放在 RAID 10 磁盘组上。您是否还决定了分区策略或问题的那一部分？
所以在你的第一个磁盘组中，我可能会创建一个单独的分区（大约 70GB 可用），我会将操作系统和 MSSQL 应用程序放在上面。
在第二个我将创建以下分区
1) 页面文件分区（取决于您拥有的内存，但大约 10-20GB 2）事务日志文件分区 100GB 3）数据文件分区 100GB
这将为您留下大约 50GB 的备用空间，我将保留未分配的空间，以便您可以根据需求的变化增加日志分区或数据。
有趣的是，我目前正在使用相同规格的机器，只是我使用的是 Linux 和 Oracle，你是我来自平行宇宙吗？
詹姆士
到目前为止已经有一些很好的答案，所以我不会费心提出一个，但我会向你提出一个问题。
为什么在购买服务器之前不问这个问题？您为什么要尝试将系统塞入已经购买的服务器中，而不是购买服务器来满足系统的需求？
抱歉，我以未注册用户的身份提出了这个问题，现在无法将其标记为已回答。我正在等待管理员返回这里，看看他们是否可以再次将我的帐户附加到问题中。
为了更深入地了解这个服务器不会承受巨大的负载，它实际上是一个源代码控制后端。购买服务器和 DAS 或 ISCSI SAN 或其他东西会过分杀戮，而且成本会粉碎项目的机会。我为大约 100 人的 SMB 工作，所以我们的 IT 预算很紧，尤其是现在。
@mrdenny
我们确实向推荐此配置的软件供应商提出了这个问题。当我们提出我在这里问的任务时，我们买了它，他们说将日志、数据库和 tempdbs 都放在 raid 10 分区上。我不是一个大数据库人（真的吗？？）但这听起来很可疑，因为几乎每个人都知道不要在同一个主轴上混合你的 ldfs 和 mdfs。
@splattne - 感谢您对 tempdb 的深入了解，这将在此和未来的 SQL 安装中派上用场。
@SuperCoolMoss - 我已经与我自己的 IT 朋友网络中的一些人交谈过，他们对此表示同意。RAID1 上的操作系统、页面文件和 LDF 以及 RAID10 上的 tempdb 和 MDF
感谢所有其他发表评论的人。
根据我的阅读，这些是我认为在磁盘方面部署 SQL 时应该遵循的基本规则。在我看来，它们应该按照这个顺序进行，如果您同意或不同意，请告诉我。
请随时分享您对此列表的修改版本。
您需要多少性能？很难根据“这里有一些磁盘”得出答案。另外，如果您可以将所有数据放入缓存中，SQL 性能会更好，因此请确保您有足够的 RAM。
我会在你的 2x74Gb 卷上使用操作系统和页面文件。如果您遇到日志流量很大的情况，您可以将您的 RAID 10 拆分为一对 RAID 1 并将您的日志放在那里。
显然有很多关于“最佳”布局的宗教战争，但你真的没有足够的磁盘来移动。对数据驱动器进行分区是没有意义的，因为您真正关心的是主轴的数量，并且您受到服务器中可以容纳的内容的限制。
您最好购买 PowerVault 或类似的设备，然后将 146Gb 驱动器回收到其中（以及尽可能多的其他驱动器）并将其拆分为数据/日志/系统 + tempdb 卷。
我将从从旧系统收集一些 IO 统计信息开始。例如，从性能仪表板报告开始： http: //www.microsoft.com/downloads/details.aspx? FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en这将为您提供数据库的 IO 统计信息，然后您可以做出明智的决定，而不是“一刀切”的通用建议。当然，旧服务器的每个数据库的 IO 特性可能与新服务器不同，特别是在可用 RAM 量不同的情况下。仍然比纯粹的猜测要好。
理想情况下，目标是将负载较高的 I/O 文件分离到自己的设备中，并且不希望将 MDF 访问的随机 I/O 与 LDF 访问的线性写入混为一谈。在您的情况下，实际上并没有太多的组合，您实际上只有 2 个位置（RAID1 中的 2 个磁盘和 RAID10 中的 4 个）。您可以总结从旧服务器收集的每个数据库的 IO，然后拆分文件位置，以便它以 RAID1 上的 1/5 和 RAID10 上的 4/5 的比率在 2 个目标之间分配 IO（考虑到用于 RAID1 上的操作系统和页面文件 IO）。希望您没有任何写入密集型应用程序（高顺序 LDF 写入负载）...
TempDB 布局通常建议为每个调度程序 1 个 NDF（未屏蔽 CPU 核心），所有大小都相同，原因在此解释：http: //blogs.msdn.com/sqlserverstorageengine/archive/2009/01/04 /managing-tempdb-in-sql-server-tempdb-configuration.aspx。这解决了在 SQL 2K 上的高负载下可能是一个严重问题的页面分配争用，在 2k5 下不太严重，在 2k8 上甚至更少。但是考虑到配置它基本上不需要任何成本，你还是应该这样做。
这不是一个真正快速的磁盘系统。为 splattne 提供了一个很棒的答案，但实际上这不是一个非常快的磁盘设置，所以我不确定它有多大的麻烦。
两个磁盘 RAID1 与单个未RAID 磁盘相比，几乎没有或根本没有提高性能。四盘 RAID10 为您提供两盘 RAID0 的速度，速度很快但并不惊人。它不是一个缓慢的服务器，但它不是运行真正要求苛刻的 SQL 数据库的明显候选者。
如果它是我的服务器，我会再找两个 146GB 的磁盘并将其设置为六磁盘 RAID5。好的，我会因为将 tempdb 放在与数据文件相同的主轴上而大喊大叫，但是 Perc 控制器非常擅长 RAID5，而且六磁盘 RAID5 会非常快，你（可能）会得到比 RAID1 上的 tempdb 和 RAID5 中只有 4 个磁盘更好的性能。我说“可能”是因为一切都取决于你如何加载它。
JR