SQL Server 中的超大表 [英] Very large tables in SQL Server

问题描述

我们有一个在 SQL Server 2005 64 位标准版上运行的非常大的表(> 77M 记录并且还在增长)，我们发现了一些性能问题.每天添加多达十万条记录.

We have a very large table (> 77M records and growing) runing on SQL Server 2005 64bit Standard edition and we are seeing some performance issues. There are up to a hundred thousand records added daily.

有谁知道SQL server标准版可以处理的记录数有限制吗?应该考虑迁移到企业版还是有一些我们可以使用的技巧?

Does anyone know if there is a limit to the number of records SQL server Standard edition can handle? Should be be considering moving to Enterprise edition or are there some tricks we can use?

附加信息:

有问题的表非常扁平(14 列)，有一个包含 6 个字段的聚集索引，以及单个字段上的另外两个索引.

The table in question is pretty flat (14 columns), there is a clustered index with 6 fields, and two other indexes on single fields.

我们使用 3 个字段添加了第四个索引，这些字段位于一个问题查询中的选择中，但未发现估计性能有任何差异(查询是必须在非工作时间运行的进程的一部分，因此我们不这样做)还没有指标).这些字段是聚集索引的一部分.

We added a fourth index using 3 fields that were in a select in one problem query and did not see any difference in the estimated performance (the query is part of a process that has to run in the off hours so we don't have metrics yet). These fields are part of the clustered index.

推荐答案

[有一个包含 6 个字段的聚集索引，以及单个字段上的另外两个索引.]

[there is a clustered index with 6 fields, and two other indexes on single fields.]

在不知道有关字段的任何详细信息的情况下，我会尝试找到一种方法来缩小聚集索引.

Without knowing any details about the fields, I would try to find a way to make the clustered index smaller.

使用 SQL Server，所有聚集键字段也将包含在所有非聚集索引中(作为从非聚集索引到实际数据页进行最终查找的一种方式).

With SQL Server, all the clustered-key fields will also be included in all the non-clustered indices (as a way to do the final lookup from non-clustered index to actual data page).

如果您有 6 个字段，每个 8 字节 = 48 字节，再乘以两个索引乘以 7700 万行 - 并且您会看到大量浪费的空间，这转化为很多I/O 操作(从而降低性能).

If you have six fields at 8 bytes each = 48 bytes, multiply that by two more indices times 77 million rows - and you're looking at a lot of wasted space which translates into a lot of I/O operations (and thus degrades performance).

对于聚集索引来说，它的唯一性、稳定性和尽可能小(最好是单个 INT 之类的)绝对是至关重要的.

For the clustered index, it's absolutely CRUCIAL for it to be unique, stable, and as small as possible (preferably a single INT or such).

马克

这篇关于SQL Server 中的超大表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！