☰

实现最佳性能的 MS SQL 布局

tag-icon • tag-icon sql-server performance database
实现最佳性能的 MS SQL 布局

我们购买了一台新服务器作为 MS SQL 后端。我很想知道它的最佳设置是什么。

该服务器是戴尔 R710，有 6 个硬盘，2x 74GB 15k 和 4x 146GB 15k

当前设置为 RAID 1/Raid10 配置。

我的问题是以下内容应该放在哪里（哪个数组）？

TEMPDB（包括数量、大小和增长情况）系统数据库（主数据库、模型数据库等）应用程序 MDF 应用程序 LDF 系统页面文件

操作系统已安装在 RAID1 上。

答案1

我将操作系统、页面文件和 LDF 放在 RAID1 阵列上。其他所有内容都放在 RAID10 阵列上。

如果您没有使用 Windows 2008，请确保您的分区正确对齐：

http://msdn.microsoft.com/en-us/library/dd758814.aspx

正如已经解释的那样，每个 CPU 核心添加 1 个 TEMPDB 文件 - 使它们大小相同。

适当调整日志文件的大小并一步创建它们。

如果可能的话，请考虑通过网络共享将数据库备份到不同的服务器 - 以减轻阵列完全故障并带走数据库和备份的风险。

答案2

临时数据库

不久前，我做了一些关于 tempdb 优化的研究，并回答了我自己的问题Stackoverflow 上的问题。以下是我发现的情况。

为了优化tempdb性能，请注意物理磁盘配置、文件配置以及数据库内的某些设置。

物理磁盘配置

tempdb 应该驻留在其拥有专用的物理磁盘. 这使得它可以将 I/O 事务与 SQL Server 上剩余的卷分离。

要将 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 定义的大小太小，部分系统处理负载可能会每次重新启动 SQL Server 实例时，都会自动将 tempdb 增大到支持工作负载所需的大小。您可以通过增加 tempdb 数据和日志文件的大小来避免这种开销。

确定生产环境中 tempdb 的适当大小取决于许多因素，包括现有工作负载和所使用的 SQL Server 功能。Microsoft 建议您通过在 SQL Server 中执行以下任务来分析现有工作负载测试环境：

  1. 为 tempdb 设置自动增长（在测试环境中！）。
  2. 执行单个查询或工作负载跟踪文件并监控 tempdb 空间的使用情况。
  3. 执行索引维护操作，例如重建索引和监视 tempdb 空间。
  4. 使用前面步骤中的空间使用值来预测您的总工作负载使用情况；根据预计的并发活动调整此值，然后相应地设置 tempdb 的大小。

tempdb 的最小大小建议如下：

  环境大小 数据库大小 (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 的资源。

链接：

答案3

简单的通用答案是，任何涉及高 IO 的东西都应该放到 RAID 10 磁盘组上。另外，您是否已经决定了分区策略或问题的这一部分？

因此，在您的第一个磁盘组中，我可能会创建一个单独的分区（大约 70GB 可用），我会将操作系统和 MSSQL 应用程序放在这个分区上。

第二个我将创建以下分区

1）页面文件分区（取决于您拥有的内存量，但大约为 10-20GB 2）事务日志文件分区 100GB 3）数据文件分区 100GB

这会给您留下大约 50GB 的备用空间，我不会将这些空间分配给您，这样您就可以根据您的需求变化来扩大日志分区或数据分区。

有趣的是，我目前正在使用相同规格的机器，只是我使用 Linux 和 Oracle，你是来自平行宇宙的我吗？

詹姆士

答案4

抱歉，我以未注册用户的身份提出了这个问题，现在无法将其标记为已回答。我正在等待管理员的回复，看看他们是否可以再次将我的帐户附加到这个问题上。

更深入地讲，这台服务器不会承受巨大的负载，它实际上是一个源代码控制后端。购买一台服务器和一台 DAS 或 ISCSI SAN 或其他东西会过度，而且成本会破坏项目的成功机会。我为一家约有 100 名员工的中小企业工作，所以我们的 IT 预算非常紧张，尤其是现在。

@mrdenny

我们确实向推荐此配置的软件供应商询问了这个问题。我们买了它，然后当我们提出我在这里问的问题时，他们说要将日志、数据库和临时数据库都放在 raid 10 分区上。我不是一个数据库专家（真的吗？）但这听起来很可疑，因为几乎每个人都知道不要将 ldfs 和 mdfs 混合在同一个主轴上。

@splattne — 感谢您对 tempdb 的见解，这对于本次以及未来的 SQL 安装都很有用。

@SuperCoolMoss - 我和我自己的 IT 朋友网络中的一些人聊过，他们同意你的观点。RAID1 上的 OS、Pagefile 和 LDF 以及 RAID10 上的 tempdb 和 MDF

感谢所有发表评论的人。

从我的阅读来看，这些是我认为在部署 SQL 磁盘时应该遵循的基本规则。我认为应该按此顺序遵循这些规则，如果您同意或不同意，请告诉我。

  1. 使用冗余磁盘（非常明显）
  2. 使用快速磁盘（SCSI/SAS，如果可能的话，15k）
  3. 将 ldf 和 mdf 文件放在不同的磁盘上
  4. 不要对 ldfs 或 tempdbs 使用 RAID5（使用 RAID10 或 RAID1）
  5. 将您的 tempdb 放在最快的主轴上；如果可能的话，将 tempdbs 与 ldf 和 mdf 主轴分开。

请随意分享您对此列表的修改版本。

相关内容