分析 sqlserver 2005 数据库性能的最佳方法是什么？
你使用什么技术？ 如何找出哪些作业运行时间最长？ 有没有办法找出有问题的应用程序？
What techinques do you use? How do you find out which jobs take the longest to run? Is there a way to find out the offending applications?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
步骤1：
安装 SQL Server 性能仪表板。
第2步：
利润。
说真的，您确实想从查看该仪表板开始。 有关安装和使用它的更多信息，请访问此处和/或此处
Step 1:
Install the SQL Server Performance Dashboard.
Step2:
Profit.
Seriously, you do want to start with a look at that dashboard. More about installing and using it can be found here and/or here
要识别有问题的查询，请启动探查器，选择以下事件：
过滤器输出，例如按
取决于您想要优化的内容。
请务必对输出进行足够的过滤，以免数千个数据行在您的窗口中滚动，因为这会影响您的服务器性能！
将输出记录到数据库表以便事后分析是很有帮助的。
并行运行 Windows 系统监视器以查看 cpu 负载、磁盘 io 和一些 sql server 性能计数器也很有帮助。 配置 sysmon 将数据保存到文件中。
您必须在数据库上获取生产典型查询负载和数据量，才能使用探查器查看有意义的值。
从分析器获得一些输出后，您可以停止分析。
然后再次将分析表中存储的数据加载到分析器中，并使用 importmenu 从 systemmonitor 导入输出，分析器会将 sysmon 输出与您的 sql 分析器数据相关联。 这是一个非常好的功能。
在该视图中，您可以立即识别与内存、磁盘或 CPU 系统相关的引导程序。
当您确定了一些要优化的查询时，请转到查询分析器并观察执行计划，并尝试优化索引使用和查询设计。
To identify problematic queries start the Profiler, select following Events:
filter output for example by
Depending on what you want to optimize.
Be sure to filter the output enough to not having thousands of datarows scrolling through your window, because that will impact your server performance!
Its helpful to log output to a database table to analyse it afterwards.
Its also helpful to run Windows system monitor in parallel to view cpu load, disk io and some sql server performance counters. Configure sysmon to save the data to a file.
Than you have to get production typical query load and data volumne on your database to see meaningfull values with profiler.
After getting some output from profiler, you can stop profiling.
Then load the stored data from the profiling table again into profiler, and use importmenu to import the output from systemmonitor and the profiler will correlate the sysmon output to your sql profiler data. Thats a very nice feature.
In that view you can immediately identifiy bootlenecks regarding to your memory, disk or cpu sytem.
When you have identified some queries you want to omtimize, go to query analyzer and watch the execution plan and try to omtimize index usage and query design.
在使用 SQL Server 2000 时，我使用 SSMS 或 SQL Profiler 中提供的数据库调优工具取得了很好的成功。
关键是使用良好的示例集，跟踪 analsys 的一部分真实的生产工作负载，这将获得最佳的整体效果物有所值。
I have had good sucess with the Database Tuning tools provided inside SSMS or SQL Profiler when working on SQL Server 2000.
The key is to work with a GOOD sample set, track a portion of TRUE production workload for analsys, that will get the best overall bang for the buck.
我使用 SQL Server 附带的 SQL Profiler。 我发现的大多数性能不佳的查询并未使用大量 CPU，而是生成大量磁盘 IO。
我倾向于在磁盘读取上放置过滤器，并查找往往执行超过 20,000 次左右读取的查询。 然后我查看这些查询的执行计划，它通常为您提供优化查询或所涉及表上的索引所需的信息。
I use the SQL Profiler that comes with SQL Server. Most of the poorly performing queries I've found are not using a lot of CPU but are generating a ton of disk IO.
I tend to put in filters on disk reads and look for queries that tend to do more than 20,000 or so reads. Then I look at the execution plan for those queries which usually gives you the information you need to optimize either the query or the indexes on the tables involved.
我使用了几种不同的技术。
如果您尝试优化特定查询，请使用查询分析器。 使用其中的工具，例如显示执行计划等。
对于您不确定哪个查询运行缓慢的情况，您可以使用的最强大的工具之一是 SQL Profiler。
只需选择您想要分析的数据库，然后让它完成它的工作。
您需要让它运行相当长的时间（这取决于应用程序的流量），然后您可以将结果转储到表中并开始分析它们。
您将需要查看具有大量读取或占用大量 CPU 时间等的查询。
优化是一件麻烦事，但要继续努力，最重要的是，不要假设您知道优化在哪里瓶颈是，找到它在哪里的证据并修复它。
I use a few different techniques.
If you're trying to optimize a specific query, use Query Analyzer. Use the tools in there like displaying the execution plan, etc.
For your situation where you're not sure WHICH query is running slowly, one of the most powerful tools you can use is SQL Profiler.
Just pick the database you want to profile, and let it do its thing.
You need to let it run for a decent amount of time (this varies on traffic to your application) and then you can dump the results in a table and start analyzing them.
You are going to want to look at queries that have a lot of reads, or take up a lot of CPU time, etc.
Optimization is a bear, but keep going at it, and most importantly, don't assume you know where the bottleneck is, find proof of where it is and fix it.