SQL Server：事件未引用任何表（Tuning Advisor 警告）
我有一个用 C# 编写的应用程序，它使用 Linq2SQL 与 SQL Server 进行通信。 有一些查询运行得有点（非常）慢，我认为它可能需要一些索引来加快速度。
但我真的不知道该怎么做，也不知道应该做什么、在哪里做什么、不应该做什么。 所以我想我可以在这里问，但后来我发现了一个名为Database Engine Tuning Advisor的程序，我想我可以先尝试一下。 问题是我无法让它工作。 可能是我不知道该怎么做，但我真的无法弄清楚这一点。 据我所知，我已经按照帮助文件做了我应该做的事情。
- 打开SQL Server Profiler。
- 使用调整模板开始新的跟踪。
- 启动我的应用程序并执行一些生成 SQL 查询的操作。
- 关闭我的应用程序。
- 停止追踪。
- 将跟踪保存为跟踪文件。
- 打开数据库引擎优化顾问
- 选择文件作为工作负载并选择我之前保存的跟踪文件。
- 在选择要调整的数据库和表下选择我的应用程序使用的数据库。
- 单击开始分析。
到目前为止，我认为一切进展顺利。 但是当它在一段时间后完成时，我得到这个：
还有一个完全空的推荐页面。 事件没有引用任何表？ 这是什么意思（当然除了显而易见的:p）？ 我是否误解了这里的流程？ 到底是怎么回事？
I have an application written in C# which uses Linq2SQL for communicating with the SQL Server. There are some queries that run a bit (very) slow, and I figure it probably need some indexes to speed things up.
But I don't really know how to do that or on what or where or what I should or should not do. So I was thinking I could ask here, but then I discovered the program called Database Engine Tuning Advisor which I thought I could try out first. The problem is I can't get it to work. It is probably me who just doesn't know how to, but I just can't really figure this out. As far as I can see, I have done what I am supposed to according to the help files.
- Open up SQL Server Profiler.
- Start a new Trace using the Tuning template.
- Start my application and do some things that generates SQL queries.
- Close my application.
- Stop the trace.
- Save the trace as a Trace file.
- Open Database Engine Tuning Advisor
- Choose File as Workload and select the Trace file I saved earlier.
- Select the databases that my application uses under Select databases and tables to tune.
- Click on Start Analysis.
This far I thought things were going ok. But when it finishes after a while short while, I get this:
And a completely empy Recommendations page. Event does not reference any tables? What does that mean (other than the obvious of course :p)? Have I misunderstood something about the process here? What is going on?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我认为您没有收到建议的原因是您对数据库没有“SHOWPLAN”权限。 授予您正在运行分析器的用户访问权限，然后重试。
另外，我还看到一些“无效的对象名称”错误 - 确保运行分析器的用户对涉及的所有表都具有适当的权限。
I think the reason you're not getting recommendations is because you don't have 'SHOWPLAN' permissions on your database. Grant the user you're running the analyzer that access and try again.
Also, I see some "invalid object name" errors as well -- make sure the user you are running the analyzer as has the appropriate permissions to all of the tables involved.
如果出现此错误，您还可以检查另一件事。 如果您像我一样笨，您可能忘记从“常规”选项卡上的“用于工作负载分析的数据库”下拉列表中选择适当的数据库
There is another thing you can check if you get this error. If you're a numpty like me you may have forgotten to select the appropriate database on from he "Database for workload analysis" drop down on the General tab
我以自己的身份运行分析器 (dbo)，但我的跟踪本身包含来自没有 SHOWPATH 访问权限的 IIS 应用程序池用户的查询。
因此，我向该 IIS 应用程序池用户授予了 SHOWPATH 访问权限，然后它工作正常。
I was running the analyser as myself (dbo) but my trace itself contained queries from an IIS app pool user who did not have SHOWPATH access.
So I granted SHOWPATH access to that IIS app pool user and then it worked fine.