提高大于 40 GB (Sql Server 2005) 和每月增长约 3GB 的数据库性能的技巧 [英] Tips for improving performance of DB that is above size 40 GB (Sql Server 2005) and growing monthly by around 3GB

问题描述

当前的数据库或我们的项目本月已超过 40 GB，平均每月增长约 3 GB.现在所有的表都被最好地规范化并且使用了适当的索引.但是随着规模的增长，即使是像select count(1) from table"这样的基本查询也需要更多的时间来触发.那么你能否分享更多有助于这方面的观点.数据库是 Sql Server 2005.此外，如果我们实施分区，它会不会产生开销?

The current DB or our project has crossed over 40 GB this month and on an average it is growing monthly by around 3 GB. Now all the tables are best normalized and proper indexing has been used. But still as the size is growing it is taking more time to fire even basic queries like 'select count(1) from table'. So can u share some more points that will help in this front. Database is Sql Server 2005. Further if we implement Partitioning wouldn't it create a overhead ?

提前致谢.

推荐答案

1. 确保您有合适/适当的索引
2. 确保您有良好的索引维护策略(例如，重建/碎片整理/保持最新统计数据以确保索引保持良好运行)
3. 识别性能不佳的查询并对其进行优化(可能已针对小数据量编写/测试，而不会出现性能问题)
4. 考虑对您的数据进行分区(例如，如果您拥有企业版，SQL 2005 及更高版本已内置对分区的支持).要详细说明 SQL Server 分区，我完全建议通读 这篇 MSDN 文章，介绍原因和方法.总的来说，Randy Shoup(eBay 架构师)在 QCon 2008 上也有一个关于可扩展性的精彩演讲，其中一个扩展系统的关键点是分区.在此处进行了总结.
5. 您的数据库服务器硬件是否足够?它可以从更多的内存中受益吗?根据您的硬件信息查看您的评论，我认为您可以(至少)在其中投入更多内存
6. 您可能会受益于一些非规范化.在不知道确切的数据库结构的情况下很难具体说明，但非规范化可能会以牺牲数据重复/磁盘空间为代价来改善某些查询

1. make sure you have suitable/appropriate indexes
2. make sure you have a good index maintenance strategy (e.g. rebuild/defrag/keep statistics up to date to ensure indexes stay performing well)
3. identify poorly performing queries and optimise them (may have been written/tested against small data volumes when performance issues would not have shown up)
4. consider partitioning your data (e.g. SQL 2005 and onwards has built in support for partitioning if you have Enterprise Edition). to elaborate on SQL Server partitioning, I full recommend a read through this MSDN article on the whys and the hows. On a general note, there was also a good talk at QCon 2008 by Randy Shoup (eBay architect) on scalability, of which one of the key points on scaling a system in general is to partition. It's summarised here.
5. is your db server hardware sufficient? could it benefit from more memory? looking at your comment with your hardware info, I think you could do with (at least) throwing more RAM in it
6. you may benefit from some denormalisation. Difficult to be specific without knowing exact db structure, but denormalising may improve certain queries at the expense of data duplication/disk space

这篇关于提高大于 40 GB (Sql Server 2005) 和每月增长约 3GB 的数据库性能的技巧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！