Search code examples
c#sql-serverlinq-to-sqlperformancesql-server-profiler

SQL Server: Event does not reference any tables (Tuning Advisor warning)

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.

  1. Open up SQL Server Profiler.
  2. Start a new Trace using the Tuning template.
  3. Start my application and do some things that generates SQL queries.
  4. Close my application.
  5. Stop the trace.
  6. Save the trace as a Trace file.
  7. Open Database Engine Tuning Advisor
  8. Choose File as Workload and select the Trace file I saved earlier.
  9. Select the databases that my application uses under Select databases and tables to tune.
  10. Click on Start Analysis.

This far I thought things were going ok. But when it finishes after a while short while, I get this:

Progress

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?

Solution

  • 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.