I have a mid-sized SQL Server 2008 database that has actuarial data in it. All of the use cases for it are read-only queries. Are there any special optimizations I should consider given this scenario? Or should I just stick with the normal rules for optimizing a database?
我有一个中型的SQL Server 2008数据库，其中包含精算数据。它的所有用例都是只读查询。在这种情况下，我是否应该考虑一些特殊的优化?还是应该只遵循优化数据库的常规规则?
7 个解决方案
#1
7
One strategy is to add a readonly filegroup to your DB, and put your readonly tables there. A readonly filegroup allows SQL Server to make a number of optimizations, including things like eliminating all locks.
一种策略是将readonly文件组添加到DB中，并将readonly表放在那里。readonly filegroup允许SQL Server进行许多优化，包括消除所有锁等。
In addition to standard DB optimization:
除了标准的DB优化之外:
- Make sure all tables and indexes have zero fragmentation
- 确保所有表和索引具有零碎片
- Consider adding indexes that you may have otherwise avoided due to excessive update costs
- 考虑添加由于更新成本过高而可能避免的索引
#2
6
In database:
在数据库:
- Denormalize it.
- Denormalize它。
- Use more indexes where needed.
- 在需要的地方使用更多的索引。
- Aggregate some data if you need it in your reports.
- 如果需要，请在报告中聚合一些数据。
In program:
在程序:
- Use READ UNCOMMITTED isolation level.
- 使用读未提交的隔离级别。
- Use autocommits to escape long-run transactions.
- 使用自动提交来逃避长期事务。
#3
5
If it is read only, one thing that you can do is put indexes on just about anything that might help (space permitting). Normally adding an index is a trade-off between a performance hit to writes and a performance gain for reads. If you get rid of the writes it's no longer a trade-off.
如果它是只读的，那么您可以做的一件事就是将索引放在任何可能有用的东西上(空间允许)。通常，添加索引是对写入的性能影响与读取的性能收益之间的权衡。如果你摆脱了写操作，这就不再是一种交换了。
When you load the database you would want to drop all/most of the indexes, perform the load, then put the indexes back on the tables.
加载数据库时，您希望删除所有/大部分索引，执行加载，然后将索引放回表中。
#4
5
I'm not sure what you consider "normal rules", but here's some suggestions.
我不确定你认为“正常规则”是什么，但这里有一些建议。
-
If you're 100% certain it's read-only, you can set the transaction isolation level to READ_UNCOMMITTED. This is the fastest possible read setting, but it will lead to phantom reads and dirty reads if you are writing to the tables.
如果100%确定为只读，可以将事务隔离级别设置为READ_UNCOMMITTED。这是最快可能的读取设置，但是如果您要写入到表中，它将导致幻象读取和脏读。
-
If you have Views, use Indexed Views (create clustered indexes for them). Since they will never have to be updated, the performance penalty is negated.
如果您有视图，请使用索引视图(为它们创建集群索引)。由于它们永远不需要更新，因此性能损失将被抵消。
-
看看这篇文章。
#5
4
- Denormalize the data.
- Denormalize数据。
- Apply the appropriate indexes.
- 应用适当的索引。
- Precalculate aggregations.
- Precalculate聚合。
- Implement the database atop a striped disk.
- 在条状磁盘上实现数据库。
- I've never seen this done but if you could somehow load the entire thing into memory (RAM disk???) that would be super fast, right?
- 我从来没见过这么做，但如果你能以某种方式将整个东西载入内存(RAM磁盘???)那将会非常快，对吗?
#6
4
For a read-only table, consider altering the indexes to use a fill factor of 100%.
对于只读表，考虑修改索引以使用100%的填充因子。
This will increase the amount of data on each data page. More data per page, fewer pages to read, less I/O, thus better performance.
这将增加每个数据页上的数据量。每个页面有更多的数据，可读的页面更少，I/O更少，因此性能更好。
I like this option because it improves performance without code changes or table changes.
我喜欢这个选项，因为它在没有代码更改或表更改的情况下提高了性能。
#7
2
For performance tuning there are several things you can do. Denormailzation works. Proper clustered indexes dependent on how the data will be queried. I don't recommend using a nolock hint. I'd use snapshot isolation level.
对于性能调优，您可以做以下几件事。Denormailzation作品。正确的聚集索引取决于如何查询数据。我不建议使用nolock提示。我将使用快照隔离级别。
It's also important on how your database is laid out on the disks. For read only performance, I'd recommend Raid 10, with separate mdf's and ldf's to isolated spindles. Normally, for a production database it would be Raid 5 for data and Raid 1 for logs. Make sure you have a tempdb file for each cpu, used for sorting, a good starting size is 5gb data and 1 gb log for each cpu. Also make sure you run your queries or procs through showplan to help optimize them as well as possible. Ensure that parallelism is on in the server settings.
关于数据库在磁盘上的布局也很重要。对于只读性能，我建议使用Raid 10，使用独立的mdf和ldf来实现独立的主轴。通常，对于生产数据库，数据是Raid 5，日志是Raid 1。确保每个cpu都有一个tempdb文件，用于排序，一个好的初始大小是5gb数据，每个cpu有1gb的日志。还要确保您通过showplan运行查询或procs，以尽可能帮助优化它们。确保服务器设置中出现了并行。
Also if you have the time and space for optimal performance, I'd map out exactly where the data lives on the disks, creating file groups and putting them on completely separate volumes that are isolated disks in each volume.
另外，如果您有时间和空间来获得最佳性能，我将精确地绘制出数据在磁盘上的位置，创建文件组，并将它们放在完全独立的卷上，这些卷是每个卷中的独立磁盘。
#1
7
One strategy is to add a readonly filegroup to your DB, and put your readonly tables there. A readonly filegroup allows SQL Server to make a number of optimizations, including things like eliminating all locks.
一种策略是将readonly文件组添加到DB中，并将readonly表放在那里。readonly filegroup允许SQL Server进行许多优化，包括消除所有锁等。
In addition to standard DB optimization:
除了标准的DB优化之外:
- Make sure all tables and indexes have zero fragmentation
- 确保所有表和索引具有零碎片
- Consider adding indexes that you may have otherwise avoided due to excessive update costs
- 考虑添加由于更新成本过高而可能避免的索引
#2
6
In database:
在数据库:
- Denormalize it.
- Denormalize它。
- Use more indexes where needed.
- 在需要的地方使用更多的索引。
- Aggregate some data if you need it in your reports.
- 如果需要，请在报告中聚合一些数据。
In program:
在程序:
- Use READ UNCOMMITTED isolation level.
- 使用读未提交的隔离级别。
- Use autocommits to escape long-run transactions.
- 使用自动提交来逃避长期事务。
#3
5
If it is read only, one thing that you can do is put indexes on just about anything that might help (space permitting). Normally adding an index is a trade-off between a performance hit to writes and a performance gain for reads. If you get rid of the writes it's no longer a trade-off.
如果它是只读的，那么您可以做的一件事就是将索引放在任何可能有用的东西上(空间允许)。通常，添加索引是对写入的性能影响与读取的性能收益之间的权衡。如果你摆脱了写操作，这就不再是一种交换了。
When you load the database you would want to drop all/most of the indexes, perform the load, then put the indexes back on the tables.
加载数据库时，您希望删除所有/大部分索引，执行加载，然后将索引放回表中。
#4
5
I'm not sure what you consider "normal rules", but here's some suggestions.
我不确定你认为“正常规则”是什么，但这里有一些建议。
-
If you're 100% certain it's read-only, you can set the transaction isolation level to READ_UNCOMMITTED. This is the fastest possible read setting, but it will lead to phantom reads and dirty reads if you are writing to the tables.
如果100%确定为只读，可以将事务隔离级别设置为READ_UNCOMMITTED。这是最快可能的读取设置，但是如果您要写入到表中，它将导致幻象读取和脏读。
-
If you have Views, use Indexed Views (create clustered indexes for them). Since they will never have to be updated, the performance penalty is negated.
如果您有视图，请使用索引视图(为它们创建集群索引)。由于它们永远不需要更新，因此性能损失将被抵消。
-
看看这篇文章。
#5
4
- Denormalize the data.
- Denormalize数据。
- Apply the appropriate indexes.
- 应用适当的索引。
- Precalculate aggregations.
- Precalculate聚合。
- Implement the database atop a striped disk.
- 在条状磁盘上实现数据库。
- I've never seen this done but if you could somehow load the entire thing into memory (RAM disk???) that would be super fast, right?
- 我从来没见过这么做，但如果你能以某种方式将整个东西载入内存(RAM磁盘???)那将会非常快，对吗?
#6
4
For a read-only table, consider altering the indexes to use a fill factor of 100%.
对于只读表，考虑修改索引以使用100%的填充因子。
This will increase the amount of data on each data page. More data per page, fewer pages to read, less I/O, thus better performance.
这将增加每个数据页上的数据量。每个页面有更多的数据，可读的页面更少，I/O更少，因此性能更好。
I like this option because it improves performance without code changes or table changes.
我喜欢这个选项，因为它在没有代码更改或表更改的情况下提高了性能。
#7
2
For performance tuning there are several things you can do. Denormailzation works. Proper clustered indexes dependent on how the data will be queried. I don't recommend using a nolock hint. I'd use snapshot isolation level.
对于性能调优，您可以做以下几件事。Denormailzation作品。正确的聚集索引取决于如何查询数据。我不建议使用nolock提示。我将使用快照隔离级别。
It's also important on how your database is laid out on the disks. For read only performance, I'd recommend Raid 10, with separate mdf's and ldf's to isolated spindles. Normally, for a production database it would be Raid 5 for data and Raid 1 for logs. Make sure you have a tempdb file for each cpu, used for sorting, a good starting size is 5gb data and 1 gb log for each cpu. Also make sure you run your queries or procs through showplan to help optimize them as well as possible. Ensure that parallelism is on in the server settings.
关于数据库在磁盘上的布局也很重要。对于只读性能，我建议使用Raid 10，使用独立的mdf和ldf来实现独立的主轴。通常，对于生产数据库，数据是Raid 5，日志是Raid 1。确保每个cpu都有一个tempdb文件，用于排序，一个好的初始大小是5gb数据，每个cpu有1gb的日志。还要确保您通过showplan运行查询或procs，以尽可能帮助优化它们。确保服务器设置中出现了并行。
Also if you have the time and space for optimal performance, I'd map out exactly where the data lives on the disks, creating file groups and putting them on completely separate volumes that are isolated disks in each volume.
另外，如果您有时间和空间来获得最佳性能，我将精确地绘制出数据在磁盘上的位置，创建文件组，并将它们放在完全独立的卷上，这些卷是每个卷中的独立磁盘。