分析 SQL Server 和/或 ASP.NET
如何分析从 ASP.NET 应用程序运行的一些查询? 我工作的一些软件由于数据库的原因运行速度非常慢(我认为)。 这些表有索引,但它仍然很拖沓,因为它要处理大量数据。 我如何进行分析以了解可以在哪些方面进行一些小的改进,从而有望带来更大的速度提升?
编辑:我想补充一点,网络服务器喜欢在这些长查询期间超时。
How would one go about profiling a few queries that are being run from an ASP.NET application? There is some software where I work that runs extremely slow because of the database (I think). The tables have indexes but it still drags because it's working with so much data. How can I profile to see where I can make a few minor improvements that will hopefully lead to larger speed improvements?
Edit: I'd like to add that the webserver likes to timeout during these long queries.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
要分析 SQL Server,请使用 SQL 分析器。
您可以使用 Red Gate 的 ANTS Profiler 来分析您的代码。
To profile SQL Server, use the SQL Profiler.
And you can use ANTS Profiler from Red Gate to profile your code.
另一个与 ASP.NET 配合良好的 .NET 分析器是 dotTrace。 我亲自使用过它,发现我的代码中有很多瓶颈。
Another .NET profiler which plays nicely with ASP.NET is dotTrace. I have personally used it and found lots of bottlenecks in my code.
我相信您已经找到了分析问题所需的答案。 然而,这是性能调优中最简单的部分。 一旦您知道问题出在查询而不是网络或应用程序上,您如何找到并解决问题?
性能调优是一件复杂的事情。 但首先有一些地方需要注意。 你说你返回大量数据? 您返回的数据是否超出了您的需要? 您真的只返回您需要的列和记录吗? 使用 select * 返回 100 列可能比返回实际使用的 5 列慢得多。
您的索引和统计数据是最新的吗? 如果您有一段时间没有执行此操作,请查找如何更新 BOL 中的统计数据和重新索引。 您是否对所有连接字段都有索引? where 子句中的字段怎么样?
你用过光标吗? 你用过子查询吗? union怎么样——如果你用的是union的话可以改成union all吗?
您的查询是否可搜索(如果不熟悉该术语,请谷歌。)
当您可以使用分组依据时,您是否使用不同的查询?
你有锁吗?
还有很多其他事情需要考虑,这些只是一个起点。
I believe you have the answer you need to profile the queries. However, this is the easiest part of performance tuning. Once you know it is the queries and not the network or the app, how do you find and fix the problem?
Performance tuning is a complex thing. But there some places to look at first. You say you are returning lots of data? Are you returning more data than you need? Are you really returning only the columns and records you need? Returning 100 columns by using select * can be much slower than returning the 5 columns you are actually using.
Are your indexes and statistics up-to-date? Look up how to update statisistcs and re-index in BOL if you haven't done this in a awhile. Do you have indexes on all the join fields? How about the fields in the where clause.
Have you used a cursor? Have you used subqueries? How about union-if you are using it can it be changed to union all?
Are your queries sargable (google if unfamiliar with the term.)
Are you using distinct when you could use group by?
Are you getting locks?
There are many other things to look at these are just a starting place.
Sql Server 有一些优秀的工具可以帮助您解决这种情况。 这些工具内置于 Management Studio(以前称为企业管理器 + 查询分析器)中。
使用 SQL Profiler 向您显示来自 Web 应用程序的实际查询。
复制每个有问题的查询(占用大量 CPU 时间或 IO 的查询)。 使用“显示实际执行计划”运行查询。 希望您会看到一些明显缺失的索引。
您还可以运行调优向导(该按钮位于“显示实际执行计划”旁边。它将运行查询并提出建议。
通常,如果您已经有索引并且查询仍然运行缓慢,则需要重新 -以不同的方式编写查询
将所有查询保存在存储过程中使这项工作变得更加容易。
Sql Server has some excellent tools to help you with this situation. These tools are built into Management Studio (which used to be called Enterprise Manager + Query Analyzer).
Use SQL Profiler to show you the actual queries coming from the web application.
Copy each of the problem queries out (the ones that eat up lots of CPU time or IO). Run the queries with "Display Actual Execution Plan". Hopefully you will see some obvious index that is missing.
You can also run the tuning wizard (the button is right next to "display actual execution plan". It will run the query and make suggestions.
Usually, if you already have indexes and queries are still running slow, you will need to re-write the queries in a different way.
Keeping all of your queries in stored procedures makes this job much easier.
如果我想要调整某个特定的查询或存储过程,我发现在查询之前打开统计信息非常有用:
当您在查询分析器中打开统计信息时,统计信息将显示在“结果”窗格的“消息”选项卡中。
IO 统计数据对我特别有用,因为它让我知道是否需要索引。 如果我从 IO 统计信息中看到较高的读取计数,我可能会尝试向受影响的表添加不同的索引。 当我尝试索引时,我再次运行查询以查看读取计数是否下降。 经过几次迭代后,我通常可以找到所涉及表的最佳索引。
以下是这些统计命令的 MSDN 链接:
设置统计时间
< a href="http://msdn.microsoft.com/en-us/library/ms184361.aspx" rel="nofollow noreferrer">设置统计 IO
If there is a particular query or stored procedure I want to tune, I have found turning on statistics before the query to be very useful:
When you turn on statistics in Query Analyzer, the statistics are shown in the Messages tab of the Results pane.
IO statistics have been particularly useful for me, because it lets me know if I might need an index. If I see a high read count from the IO statistics, I might try adding different indexes to the affected tables. As I try an index, I run the query again to see if the read count has gone down. After a few iterations, I can usually find the best index(es) for the tables involved.
Here are links to MSDN for these statistics commands:
SET STATISTICS TIME
SET STATISTICS IO