SQL Server性能调优指南：关键技巧与设置优化
需积分: 10 20 浏览量
更新于2024-07-30
收藏 176KB PDF 举报
《SQL Server性能调优指南》是一份由Epicor Software Corporation发布的专业文档，适用于SQL Server Vista/Vantage 8.03.405版本。该指南主要针对SQL Server的性能优化问题，旨在帮助用户提升数据库系统的运行效率，确保应用程序的稳定性和响应速度。
文档首先提出了几个重要的建议，包括：
1. **停机与重启应用服务器**：在进行性能调优前，建议临时停止应用服务器，确保在操作期间不会影响正常业务流程。这样可以减少不必要的干扰，并提供一个干净的工作环境。
2. **验证Mfgsys.pf文件参数**：Mfgsys.pf文件是关键配置的一部分，检查其参数设置是否正确，这对于数据库性能有直接影响。可能需要调整内存分配、缓存策略等参数。
3. **更新Schema Holder设置**：Schema Holder是存储元数据的地方，将其设置为只读模式有助于提高数据一致性并减少写操作对性能的影响。
4. **监控数据库缓冲区和锁定表条目**：数据库缓冲区管理是性能瓶颈的一个常见来源，关注#BlocksinDBBuffer的数量，同时监控锁表操作以避免死锁或锁定过度。
5. **处理Spin Locks**：Spin Lock是一种低级别的锁定机制，过多的 Spin Locks可能导致CPU浪费。需要识别并优化这些锁定，以减少资源争用。
6. **背景写入器**：后台写入器可能对I/O性能产生影响，合理配置它们的工作模式，以平衡写入效率和并发性能。
7. **将Schema Holders设为只读**：如前所述，将Schema Holders设为只读模式有助于减少写操作，提高查询性能。
8. **数据库准备**：对数据库进行适当的预处理，例如定义文件为只读，可以防止不必要的写操作，并提高读取速度。
9. **编辑Mfgsys.pf文件**：直接操作此文件可能涉及调整更深层次的配置，需要谨慎进行，以确保不破坏系统稳定性。
10. **服务器配置文件回顾**：总结并回顾服务器配置文件，确保所有设置都符合最佳实践，可能需要根据具体情况进行微调。
11. **Open4（可选步骤）**：这部分可能是针对特定场景的高级技巧或特定版本的特性，读者可以根据实际情况决定是否执行。
12. **故障排查 - 应用服务错误**：文档还提供了故障排除的指导，帮助用户处理可能遇到的App Server错误，确保问题得到及时解决。
在整个指南中，作者强调了细致的分析、配置调整以及对SQL Server内部机制的理解。通过遵循这些建议，用户可以有效地提升SQL Server的整体性能，优化业务运行环境。
Performance Tuning Guide (SQL) Vista/Vantage 8.03.405 3
Introduction
This document contains a series of instructions that can help you improve the performance of your SQL
database. Each set of instructions documents a potential performance tuning option you can use.
Implement the options which best match the configuration of your system. Each operating system and
specific configuration is different, so the results of these options will vary.
Epicor recommends that you measure the performance benefits of these changes. To do this, time how
long it takes to run a process before you use a performance option. Then after you implement the option,
time the same process again. You should see a demonstratable savings in performance time.
Be sure to record each change and why you made it. You then can review what you did later on. Write
comments in your scripts and .pf files to document the changes. Remember that you can gain a lot of
performance by just doing a few things. Usually adding memory and spreading the disk workload across
as many disks as possible gives you the best performance gains. Always stop after you have
accomplished enough; the more tuning you do, the smaller the return on your investment.
Stop and Restart AppServers
Throughout these instructions, you are frequently asked to stop and restart your AppServers. You must do
this to activate your performance tuning changes.
Follow these instructions each time you are asked to stop and restart your AppServers:
1. From the desktop, click Start and navigate to the following program: Programs->Open Edge
(10.0B or 10.1B)->Progress Explorer Tool
2. The Progress Explorer Tool is displayed. Review the left pane. A yellow gear labeled Progress
Explorer appears; below this icon either localhost or the server physical name are displayed.
3. From the Actions menu, select the Connect… command.
Note: The Progress Explorer Tool can be set up to connect automatically. If this is the case with your
application, skip steps 3 and 4.
4. Within the localhost Login window, enter your User Name and Password.
5. Click OK.
6. Several folders are displayed. Locate and expand the AppServer folder.
7. Each database contains three AppServers; locate the three AppServers for your database. For
example: Mfgsys803, Mfgsys803ProcessServer, and Mfgsys803TaskAgent.
8. Right click on each AppServer. From the context menu, select Stop.
9. Wait until each AppServer displays the Not Running status.
Note: To view the status, click on the AppServer folder in the left pane. The current status of the
AppServer displays in the right pane.
10. When they each stop, you must start them again. To do this, right click on each AppServer. From
the context menu, select Start.
Each AppServer is now refreshed with your changes.
剩余14页未读，继续阅读
289 浏览量
2009-04-09 上传
120 浏览量
2013-05-28 上传
2013-10-17 上传
283 浏览量
129 浏览量
