SQL 硬件与性能的比率
我正在寻找一种方法来查找 SQL Server 中的瓶颈，似乎超过 32GB 的内存和 8 个核心上超过 32 个主轴是不够的。是否有任何指标、最佳实践或硬件比较（即每秒事务数）？我们每天的关闭需要几个小时，我希望在几分钟内完成，或者如果可能的话实时完成。我无法合并超过 12k 行/秒。目前，我必须将流量拆分到多个服务器，但对于约 50GB 数据库来说，这是一个合适的解决方案吗？ 合并包含在 SP 中并尽可能简单 - 删除重复输入、插入新行、更新现有行。我发现我们放入单个合并的行越多，每秒获得的行就越多。应用程序服务器在更多线程中运行，并使用其专用服务器上的所有内存和处理器。
I am seeking a way to find bottlenecks in SQL server and it seems that more than 32GB ram and more than 32 spindels on 8 cores are not enough. Are there any metrics, best practices or HW comparations (i.e. transactions per sec)? Our daily closure takes hours and I want it in minutes or realtime if possible. I was not able to merge more than 12k rows/sec. For now, I had to split the traffic to more than one server, but is it a proper solution for ~50GB database?
Merge is enclosed in SP and keeped as simple as it can be - deduplicate input, insert new rows, update existing rows. I found that the more rows we put into single merge the more rows per sec we get. Application server runs in more threads, and uses all the memory and processor on its dedicated server.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
请遵循等待和队列等方法来识别瓶颈。这正是设计的目的。一旦确定了瓶颈，您还可以判断是否是硬件配置和校准问题（如果是，哪个硬件是瓶颈），或者是否是其他问题。
Follow a methodology like Waits and Queues to identify the bottlenecks. That's exactly what is designed for. Once you identified the bottleneck, you can also judge whether is a hardware provisioning and calibration issue (and if so, which hardware is the bottleneck), or if is something else.
基本思想是避免对磁盘进行随机访问（读取和写入）。在不做任何分析的情况下，50 GB 的数据库至少需要 50 GB 的 RAM。然后，您必须确保索引与数据和事务日志位于不同的轴上，尽可能晚地写入，并且关键表被拆分到多个轴上。你在做这一切吗？
The basic idea is to avoid having to do random access to a disk, both reading and writing. Without doing any analysis, a 50 GB database needs at least 50GB of ram. Then you have to make sure indexes are on a separate spindle from the data and the transaction logs, you write as late as possible, and critical tables are split over multiple spindles. Are you doing all that?