CODEWITHSUNDEEP
c#sql-serverlinq-to-sqlperformancesql-server-profiler
Svish
Svish

Reputation: 158261

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?

Upvotes: 10

Views: 5681

Answers (3)

Matt Frear
Matt Frear

Reputation: 54881

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.

GRANT SHOWPLAN TO [COMPANYDOMIAN\IIS_APPUSER]

Upvotes: 3

Phil Hale
Phil Hale

Reputation: 3491

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

Upvotes: 5

Jonathan Rupp
Jonathan Rupp

Reputation: 15772

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.

Upvotes: 6

Related Questions