在 SQL Server 中对大表进行分区的最佳方法是什么? [英] What is the best way to partition large tables in SQL Server?

问题描述

在最近的一个项目中，首席"开发人员设计了一个数据库模式，其中较大"的表将被拆分到两个单独的数据库中，并在主数据库上显示一个视图，该视图会将两个单独的数据库表联合在一起.主数据库是应用程序被驱动的，所以这些表看起来和感觉就像普通表(除了一些关于更新的古怪事情).这似乎是一个巨大的性能问题.我们确实看到了这些桌子周围的性能问题，但没有什么能让他改变对他的设计的看法.只是想知道这样做的最佳方法是什么，或者是否值得这样做?

In a recent project the "lead" developer designed a database schema where "larger" tables would be split across two separate databases with a view on the main database which would union the two separate database-tables together. The main database is what the application was driven off of so these tables looked and felt like ordinary tables (except some quirky things around updating). This seemed like a HUGE performance problem. We do see problems with performance around these tables but nothing to make him change his mind about his design. Just wondering what is the best way to do this, or if it is even worth doing?

推荐答案

我认为通过在单个服务器中跨多个数据库对表进行分区，您不会真正获得任何好处.通过在单个 SQL Server 实例下拥有多个实例(即在两个不同的数据库中打开)，您基本上所做的一切首先增加了使用表"的开销.

I don't think that you are really going to gain anything by partitioning the table across multiple databases in a single server. All you have essentially done there is increased the overhead in working with the "table" in the first place by having several instances (i.e. open in two different DBs) of it under a single SQL Server instance.

你有多大的数据集?我有一个客户端在 SQL Server 中有一个 600 万行的表，其中包含 2 年的销售数据.他们在事务中使用它并进行报告，而没有任何明显的速度问题.

How large of a dataset do you have? I have a client with a 6 million row table in SQL Server that contains 2 years worth of sales data. They use it transactionally and for reporting without any noticiable speed problems.

调整索引并选择正确的聚集索引当然对性能至关重要.

Tuning the indexes and choosing the correct clustered index is crucial to performance of course.

如果您的数据集非常大，并且您希望进行分区，那么跨物理服务器对表进行分区会更划算.

If your dataset is really large and you are looking to partition, you will get more bang for your buck partitioning the table across physical servers.

这篇关于在 SQL Server 中对大表进行分区的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！