什么是最佳SQL Server性能优化技术? [英] What are the best SQL Server performance optimization techniques?

问题描述

我一直采用以下方法:首先以最少的索引集部署数据库，然后根据性能要求添加/更改索引.

I've always taken the approach of first deploying the database with a minimal set of indexes and then adding/changing indexes as performance dictates.

此方法相当有效.但是，它仍然没有告诉我在哪里可以提高性能.它只告诉我性能太差了，以至于用户抱怨它.

This approach works reasonably well. However, it still doesn't tell me where I could improve performance. It only tells me where performance is so bad that users complain about it.

当前，我正在重构许多应用程序上的数据库对象.

Currently, I'm in the process of refactoring database objects on a lot of our applications.

因此，由于过早的优化是万恶之源"，我是否应该不去寻求性能改进?

So should I not bother to look for performance improvements since "premature optimization is the root of all evil"?

重构应用程序代码时，开发人员一直在寻找提高代码质量的方法.是否有一种方法可以不断寻求数据库性能的改进?如果是这样，您发现哪些工具和技术最有帮助?

When refactoring application code, the developer is constantly looking for ways to improve the code quality. Is there a way to constantly be looking for improvements in database performance as well? If so, what tools and techniques have you found to be most helpful?

我曾短暂地与数据库引擎调优顾问"一起玩过，但没有发现它对您有帮助.也许我只需要更多的解释结果的经验即可.

I've briefly played around with the "Database engine tuning advisor" but didn't find it to be helpful at all. Maybe I just need more experience interpreting the results.

推荐答案

我的方法是使用SQL Server Profiler将针对服务器或数据库的命令收集到表中.有了这些信息后，您就可以根据最大和平均执行时间，最大和平均cpu时间，以及(非常重要的)查询运行的次数进行查询.

My approach is to gather commands against the server or database into a table using SQL Server Profiler. Once you have that, you can query based on the max and avg execution times, max and avg cpu times, and (also very important) the number of times that the query was run.

由于我尝试将所有数据库访问代码放入存储过程中，因此我很容易发出查询.如果使用内联SQL，可能会更困难，因为对查询中的值进行更改会使它看起来像是另一个查询.您可以尝试使用LIKE运算符解决此问题，将相同类型的查询放入相同的存储桶中以计算聚合(最大值，平均值，计数).

Since I try to put all database access code in stored procedures it's easy for me to break out queries. If you use inline SQL it might be harder, since a change to a value in the query would make it look like a different query. You can try to work around this using the LIKE operator to put the same types of queries into the same buckets for calculating the aggregates (max, avg, count).

一旦您有了潜在问题的前10名"列表，您就可以开始逐一查看它们，以查看是否可以重做该查询，索引可能会有所帮助，或者需要进行较小的体系结构更改.要得出前10名，请尝试以不同的方式查看数据:平均*计算期内的总费用，最严重的违法者的最高费用，仅是平均数，等等.

Once you have a "top 10" list of potential problems you can start looking at them individually to see if either the query can be reworked, an index might help, or making a minor architecture change is in order. To come up with the top 10, try looking at the data in different ways: avg * count for total cost during the period, max for worst offender, just plain avg, etc.

最后，如有必要，请确保在不同的时间段内进行监视.在每个人都进入并运行其每日报告的早晨，与用户输入新数据的中午相比，数据库的使用情况可能有所不同.您可能还决定，即使某个夜间过程比任何其他查询花费的时间更长，也没关系，因为它是在下班时间运行的.

Finally, be sure to monitor over different time periods if necessary. The database usage might be different in the morning when everyone is getting in and running their daily reports than it is at midday when users are entering new data. You may also decide that even though some nightly process takes longer than any other query it doesn't matter since it's run during off hours.

祝你好运！

这篇关于什么是最佳SQL Server性能优化技术?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！