除了 MS Profiler 之外，还有哪些工具可用于分析 SQL Server 中的存储过程? [英] What tools are out there for profiling stored procedures in SQL server other than the MS profiler?

问题描述

似乎应该有比 SQL Server Profiler 更好的工具来分析 sql.设置配置文件会话需要很长时间.我正在寻找一种更像 VS 分析器的工具.只需选择一个存储过程，并使用一组参数运行它，我应该得到的是一个分析图，显示每个查询、SP 调用和函数调用所花费的时间，并允许我深入到带有参数的单个语句被传入.

It seems like there should be better tools out there for profiling sql than SQL Server Profiler. It takes forever to set up a profile session. I'm looking for a tool which is more like VS profiler. Just choose a stored procedure, and run it with a set of parameters, and what I should get is a profiling graph showing how long each query, SP call and function call takes, and allow me to drill down to individual statements with the parameters which were passed in.

我见过一些适用于 Oracle 的工具，但我还没有见过任何适用于 SQL Server 的好工具.

I've seen some for Oracle, but I haven't seen any good tools for SQL server.

推荐答案

SQL Profiler 没有任何问题.诚然，它有其特点，但仍然是一个很好的工具.关键是，调优单个存储过程的价值通常低于调优查询工作负载的价值.

There is nothing wrong with SQL Profiler. Admittedly, it has its idiosyncrasies, but is nonetheless a good tool. The point being, there is often less value in tuning a single stored procedure than there is tuning a query workload.

我不会分享您设置个人资料会话需要很长时间"的经验.

I do not share your experience that "It takes forever to set up a profile session".

在 SQL Server 2008 之后，还有扩展事件，但是，虽然功能强大，但它们还没有简单的 GUI.

In SQL Server 2008 onwards, there are also extended events, BUT, though powerful, they do not have a simple GUI as yet.

更新:如果您已经确定了有问题的存储过程，请在打开实际执行计划"的情况下在 SSMS 中运行:这将表明运行需要时间的原因.您还可以包括SET STATISTICS IO ON"，以便对执行的读/写进行细分.

UPDATE: If you have already identified the Stored Proc in question, run in SSMS with the 'Actual Execution Plan' turned on: that will indicate why it takes time to run. You can also include "SET STATISTICS IO ON" to give you a breakdown of the read/writes performed.

要检查执行计划，请尝试使用免费的 SQL Sentry Plan探索者

For examining execution plans, try using the free SQL Sentry Plan Explorer

这篇关于除了 MS Profiler 之外，还有哪些工具可用于分析 SQL Server 中的存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！