SQL Server调整以实现更快的查询

落日映苍穹つ 2022-12-07 13:53 98阅读 0赞

Your [SQL Server Compact][] is capable of lightening-fast performance and stunningly efficient service even while handling huge work loads. But without regular SQL sever performance tuning, you can’t expect it to “win the race” any more than a race car could without regular tune ups.

您的[SQL Server Compact][]即使在处理大量工作负载时也能够提供快速的性能和出众的高效服务。 但是，如果没有常规SQL服务器性能调整，就不能期望它能比没有常规调整的赛车更能“赢得比赛”。

You will have an opportunity to read in-depth information about SQL Server performance if you [visit this Stackify][] page. But for a basic overview of the most important principles for your SQL tune-ups, keep reading right here.

如果[访问此Stackify][visit this Stackify]页面，您将有机会阅读有关SQL Server性能的深入信息。 但是，要获得有关SQL调整最重要原则的基本概述，请继续阅读此处。

![race car maintenance][]

[Image Source][]

[图片来源][Image Source]

## 关于SQL Server调整 **(**About SQL Server Tuning**)** ##

It would be nice to think that you could handle SQL performance tuning in a single, end-all strike, but what it’s really going to take is a targeted, involved, and “never-ending” campaign. Slow SQL performance may be caused by only one or two slow but frequently used queries that are eating up all your active memory. It’s a “search and destroy mission.” Realize that your query processor has to compile, perfect, and generate an execute plan before it can even start an actual performance. Also realize that there may be numerous ways to do any given query, but only one of them is the most efficient. And what works in one scenario won’t work somewhere else. And what worked best last year may be only second-best (at best) this year.

认为您可以一次完成所有最终操作来处理SQL性能调整，将是一件很不错的事，但是真正要采取的是针对性，参与性和“永无止境”的活动。 缓慢SQL性能可能仅由一两个缓慢但经常使用的查询占用了所有活动内存引起。 这是“搜索和摧毁任务”。 意识到您的查询处理器必须编译，完善并生成执行计划，然后才能开始实际性能。 还应意识到，有多种方法可以执行任何给定的查询，但是只有其中一种是最有效的。 在一种情况下有效的方法在其他地方则行不通。 去年最有效的方法可能只是今年(最好)的第二好方法。

### 时段和指标 **(**Buckets and Metrics**)** ###

As you regularly test and retest, record the result and search for of the fastest, least expensive, lowest memory consumption solution, you need to learn to “think in buckets.” “Buckets” is tech-talk for units of similar data-types. It’s the way you organize things when you are looking for a problem. You ask, “Is the issue in resources, in query structure, in indexing, or somewhere else?” It’s the equivalent to a mechanic asking, “Is the problem with the engine, transmission, electrical system, or the body?”

在定期测试和重新测试，记录结果并寻找最快，最便宜，最低内存消耗的解决方案时，您需要学习“三思而后行”。 “桶”是针对类似数据类型的单元的技术讲座。 这是您寻找问题时组织事情的方式。 您会问：“问题是资源，查询结构，索引还是其他地方？” 这相当于机械师问：“发动机，变速箱，电气系统或车身出现问题了吗？”

Next, keep in mind you will be using numerous data metrics as you fine tune your query searches for optimal performance. Your boss will likely be happy with even a slight uptick, just like a track runner shaving seconds off his mile.

接下来，请记住，在微调查询搜索以获取最佳性能时，将使用大量数据指标。 您的老板可能会对一点点的增加感到满意，就像田径赛跑者减少英里数一样。

The key is to not only understand and have access to great metrics, but to know how to correlate them correctly. You have to be able to deduce where the problem is by comparing various metrics. You look at wait states, CPU, throughput, memory pressure, bandwidth, usage, and more, and “triangulate” to find the issue. Use the Database Performance Analyzer dashboard to make this process much easier!

关键是不仅要了解并获得出色的指标，而且要知道如何正确地将它们关联起来。 您必须能够通过比较各种指标来推断问题出在哪里。 您查看等待状态，CPU，吞吐量，内存压力，带宽，使用情况等，然后“三角剖分”以发现问题。 使用数据库性能分析器仪表板可以使此过程更加轻松！

### 跟踪结果 **(**Track Results Over Time**)** ###

Again, this is a long-term undertaking, a never-ending war. To win, you need to keep collecting data at regular intervals (we’re talking about days, weeks, months); and then, compare this against last year’s or the last few year’s stats. That gives you perspective on how well you are doing and if you are improving/declining.

同样，这是一项长期的工作，一场永无止境的战争。 为了获胜，您需要定期收集数据(我们所说的是几天，几周，几个月)； 然后，将其与去年或最近几年的统计数据进行比较。 这使您可以了解自己的表现如何以及是否在改善/下降。

Of course, you’ll also want to get information on the industry average and pay attention to any goals your superiors may have set for you (or you may have set for yourself).

当然，您还希望获得行业平均水平的信息，并注意上级可能为您设定的目标(或者您可能为自己设定的目标)。

### 进行定期维护 **(**Do Regular Maintenance**)** ###

Maintenance of your database is also key. As you pay attention to maintenance tasks, you both prevent SQL issues and gather the data that can help you solve certain SQL problems should they arise.

维护数据库也是关键。 当您注意维护任务时，既可以防止SQL问题，又可以收集数据来帮助您解决某些SQL问题(如果出现)。

Specifically, hour-by-hour transaction log backups plus daily and weekly backups; daily index maintenance (not necessarily rebuilding indexes every day though), statistic updating, and corruption checks should all be a part of your maintenance routine.

具体来说，按小时进行的事务日志备份以及每日和每周的备份； 日常索引维护(虽然不一定每天都重建索引)，统计信息更新和损坏检查都应作为维护例程的一部分。

> 翻译自: [https://www.thecrazyprogrammer.com/2017/10/sql-server-tuning-faster-queries.html][https_www.thecrazyprogrammer.com_2017_10_sql-server-tuning-faster-queries.html]

[SQL Server Compact]: https://en.wikipedia.org/wiki/Microsoft_SQL_Server
[visit this Stackify]: https://stackify.com/performance-tuning-in-sql-server-find-slow-queries/
[race car maintenance]: /images/20221123/1eb386d2784d44f3908d03a418ca7046.png
[Image Source]: http://www.directasia.com/blog/5-useful-car-maintenance-tips-from-formula-1/
[https_www.thecrazyprogrammer.com_2017_10_sql-server-tuning-faster-queries.html]: https://www.thecrazyprogrammer.com/2017/10/sql-server-tuning-faster-queries.html