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?
Best Answer
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: