Sql-server – Tune SQL Server Express using Profiler

sql serversql-server-2005tuning

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:

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.

Related Topic