Tune SQL Server Express using Profiler?

Question

I have a SQL Server 2005 database... a copy of it is running in development on a full version of SQL server. Another copy is running in SQL Server 2005 Express on a web server.

I've used SQL Profiler and saved a Tuning trace log from activity on the SQL Express copy of the database.

I want to use the saved trace log in the Database Engine Tuning Advisor...

If I try when connecting the Advisor to the Express database, I am told that Express is not supported.

If I try when connecting the Advisor to the SQL Server database, I get empty results.

Is there any way to do this?

Answer 1

I think you would need a copy of the database from the Express instance restored to the full instance. The advisor looks at the schema to make it's tuning advice.

Answer 2

You should be able to tune an identical database on the local development server using trace data collected from a remote production server (Express).

But you cannot use trace tables stored in the remote server to do this. According to Considerations for Using Database Engine Tuning Advisor, "Database Engine Tuning Advisor cannot tune a workload in a trace table that resides on a remote server"

But I guess you are using trace files, so this should not be an issue.

If you don't get any recommendations, you should first check the list on the previously mentioned MSDN page:

Database Engine Tuning Advisor might not make recommendations under the following circumstances:

The table being tuned contains less than 10 data pages.

The recommended indexes would not offer enough improvement in query performance over the current physical database design.

The user who runs Database Engine Tuning Advisor is not a member of the db_owner database role or the sysadmin fixed server role. The queries in the workload are analyzed in the security context of the user who runs the Database Engine Tuning Advisor. The user must be a member of the db_owner database role.

Answer 3

I think all you need to do is trace the DB to a table and then point tuning advisor at the data in the table that contains the trace results. That way I dont think you need to move any databases around.

Answer 4

I also found that I could edit the trace file in SQL Profiler and remove any columns that specify the login or database name. After that, the Tuning Advisor worked.