SQL Filegroup降低性能

Question

在我们的开发框中，我们的数据库完全驻留在主文件组中，一切正常。

在我们的一台生产服务器上，最近从2005年升级到2008年，我们注意到它的表现比它应该做的要慢。在这台机器上，有两个文件组-主文件组和索引文件组。两个文件组每个逻辑卷包含一个文件，每个CPU包含一个逻辑卷。文件组共享逻辑卷(它们都分布在所有磁盘上)。

我们分离出了一些查询，这些查询在开发框上执行得很快，在生产机器上执行得很慢(慢到40倍)。结果显示，这些查询使用的是驻留在索引文件组中的非聚集索引。将一些查询调整为只使用主文件组中的聚集索引，将它们的时间降回正常。

作为最后的确认，我们在同一台机器上重新部署了相同的数据库，使所有的内容都处于初级状态，一切都恢复了正常！

下面是其中一个查询的统计数据输出，运行在所讨论的机器上(为了保护无辜者而更改了表名)：

FAST (主文件组中的所有内容)：

(3 row(s) affected)
Table '0'. Scan count 2, logical reads 14, ...
Table '1'. Scan count 0, logical reads 0, ...
Table '1'. Scan count 0, logical reads 0, ...
Table '2'. Scan count 2, logical reads 7, ...
Table '3'. Scan count 2, logical reads 1012, ...
Table '4'. Scan count 1, logical reads 3, ...

 SQL Server Execution Times:
   CPU time = 437 ms,  elapsed time = 445 ms.

缓慢(索引分裂为自己的文件组)：

(3 row(s) affected)
Table '0'. Scan count 209, logical reads 428, ...
Table '1'. Scan count 0, logical reads 0,...
Table '2'. Scan count 1021, logical reads 9043,....
Table '3'. Scan count 209, logical reads 105754, ....
Table '4'. Scan count 0, logical reads 0, ....
Table '5'. Scan count 1, logical reads 695, ...
**Table '#46DA8CA9'. Scan count 205, logical reads 205, ...**
Table '6'. Scan count 6, logical reads 436, ...
Table '7'. Scan count 1, logical reads 12,....

 SQL Server Execution Times:
   CPU time = 17581 ms,  elapsed time = 17595 ms.

很明显，拥有第二个文件组将使Server在选择执行计划时变得异常重要。查看每个配置中查询的相应执行计划可以验证这一点，它们是完全不同的。

到底怎么回事？任何见解都将不胜感激。

Answer 1

随机思想

• 统计数据等在重新生成为新文件组时没有被禁用？
• 维护任务不查看文件组吗？
• 严重碎裂的磁盘？
• 试试MAXDOP 1
• 完全相同的填充因子等等？

IO看起来离我们很远，所以我想知道统计数据或碎片是否导致了问题。

Answer 2

我的最佳猜测是，由于索引与数据分离，并显示在相同的物理磁盘上，因此数据库引擎将尝试同时访问索引和数据，以使同一物理磁盘减慢速度。

通过将索引和数据放在同一个文件组中，引擎不会在同一磁盘上同时请求索引和数据，因为他知道这是在相同的物理支持下进行的。

对于索引和数据通常不鼓励使用单独的文件组，因为它需要对您的请求和您的材料进行大量检查，以找到一个以这种方式改进请求的解决方案(如果存在的话)。但是，基本上，在相同的磁盘和不同的文件组中拥有这些数据的数据和索引会导致性能问题。