Entity Framework and SQL Server Tuning Advisor

Question

How do I use SQL Server Tuning Advisor to produce recommendations based on Entity Framework queries? Entity Framework uses sp_executesql 'query' instead of executing the query and Tuning Advisor does not recognize these queries. I could get the query text manually, but this will only work for very simple scenarious. How do I do it in an automatic way?

UPDATE

Would it help if I save the profiling information into a table and then run some application which will strip "sp_executesql" and only keep the query text and use this new data to run Tuning Advisor?

Answer 1

I know this topic is a little cold. But since I was having the same issue and found a better solution:

Changing events captured is one way to do it, but there is already a "template", which records everything needed by the SQL server tuning advisor.

If you select the template "tuning", it will effectively remove and add event capture quoted by Doug, but another too.

You can find more about template here

To activate this template, in the "New Trace" windows, select the corresponding template( I didn't create it, it was already there):

Once you did it, you can check the event selection, you have already what is needed:

I additionaly disabled the RPCCompleted, since I've no stored procedure

I hope it will help some people ;)

Answer 2

It looks like the problem is this. If you create a trace using SQL Profiler from the "Tuning" template it does not include the correct events. The Database Engine Tuning Advisor does not understand sp_executsql statements from the batch completed events. It report in the tuning log "Event does not reference any tables" with a category ID of "S008".

When capturing your trace you need to add the "SQL:StmtCompleted" event under the TSQL section as well.