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

Question

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:

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?

Capps

Do the commands in the 'event' column match what you were expecting to see? Do you recognise stuff in there as coming from your application? – Scholl

not all of them, but many of them. – Capps

Answer 1

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.

Astri

Yeah, I discovered that as well. Which I fixed now. But I still get the 'Event does not reference any tables' followed by 'Replaced event blah blah'. – Capps

Hm... Tried it on some other heavier operations in my application and seems like it is working now, although I still get a bunch of those messages... confused. Oh well... I will accept your answer since you catched that SHOWPLAN stuff. Think that plus the heavier operation might have made the difference =) – Capps

I am analyzing as 'sa' but still getting this error. This is weird. – Phonography

Answer 2

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

Bryce

This field does not need to be set to the database which contain the table on which you want to create index. It is the db that the tuning advisor will start under. It will then switch to the correct db as specified in your workload file. – Highlands

Answer 3

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]

Grassplot